Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.python > #36532 > unrolled thread

Psycopg2 SyntaxError: invalid syntax on "INSERT INTO" database

Back to article view | Back to comp.lang.python

Contents

  Psycopg2 SyntaxError: invalid syntax on "INSERT INTO" database andydtaylor@gmail.com - 2013-01-09 15:52 -0800
    Re: Psycopg2 SyntaxError: invalid syntax on "INSERT INTO" database John Gordon <gordon@panix.com> - 2013-01-10 00:08 +0000
      Re: Psycopg2 SyntaxError: invalid syntax on "INSERT INTO" database andydtaylor@gmail.com - 2013-01-09 16:19 -0800
        Re: Psycopg2 SyntaxError: invalid syntax on "INSERT INTO" database Mitya Sirenef <msirenef@lightbird.net> - 2013-01-09 20:07 -0500
        Re: Psycopg2 SyntaxError: invalid syntax on "INSERT INTO" database MRAB <python@mrabarnett.plus.com> - 2013-01-10 01:11 +0000
          Re: Psycopg2 SyntaxError: invalid syntax on "INSERT INTO" database andydtaylor@gmail.com - 2013-01-09 18:20 -0800
            Re: Psycopg2 SyntaxError: invalid syntax on "INSERT INTO" database Mitya Sirenef <msirenef@lightbird.net> - 2013-01-09 21:56 -0500
          Re: Psycopg2 SyntaxError: invalid syntax on "INSERT INTO" database andydtaylor@gmail.com - 2013-01-09 18:20 -0800
    How to run multiline shell command within python Karim <kliateni@gmail.com> - 2013-01-10 07:01 +0100
    Re: [Tutor] How to run multiline shell command within python Hugo Arts <hugo.yoshi@gmail.com> - 2013-01-10 09:31 +0100
    Re: [Tutor] How to run multiline shell command within python Karim <kliateni@gmail.com> - 2013-01-10 15:25 +0100
    Re: [Tutor] How to run multiline shell command within python Matty Sarro <msarro@gmail.com> - 2013-01-10 10:21 -0500
    Re: [Tutor] How to run multiline shell command within python Karim <kliateni@gmail.com> - 2013-01-10 20:07 +0100

#36532 — Psycopg2 SyntaxError: invalid syntax on "INSERT INTO" database

Hi,

I'm a bit stuck on this "INSERT INTO" syntax error. I have no idea why it's not working actually... I've tried changing column types to char but that didn't work. I've gone a bit blind looking at it, but hopefully you can set me right. With the '#'d out lines instead the file does work.

What am I missing?

Thanks


Andy


#!/usr/bin/python
import psycopg2
import sys

def main():
   db = psycopg2.connect(
      host = 'localhost',
      database = 'gisdb',
      user = 'postgres',
      password = '######'
   )
   cursor = db.cursor()
   cursor.execute("DROP TABLE IF EXISTS tubecross")
   cursor_to.execute("CREATE TABLE tubecross (id serial PRIMARY KEY, station_code char, SAJ interval, SPB interval, SOQ interval);")
   #cursor.execute("CREATE TABLE tubecross (id serial PRIMARY KEY, num integer, data varchar);") 
   #cursor.execute("INSERT INTO tubecross (num, data) VALUES (%s, %s)",(900, "9abc'def"))
   cursor_to.execute("INSERT INTO tubecross (station_code, SAJ, SPB, SOQ) VALUES (%s, %s, %s, %s)",(SAJ, 00:00, 00:22, 00:27))
   db.commit()

if __name__ == "__main__":
	main()

[toc] | [next] | [standalone]

#36534

In <b1c7ea47-47b4-4784-8037-e0856fdaa7cd@googlegroups.com> andydtaylor@gmail.com writes:

> I'm a bit stuck on this "INSERT INTO" syntax error. I have no idea why it's

What syntax error?  It's always helpful if you can post the actual error
message.

> not working actually... I've tried changing column types to char but that
> didn't work. I've gone a bit blind looking at it, but hopefully you can set
> me right. With the '#'d out lines instead the file does work.

> #!/usr/bin/python
> import psycopg2
> import sys

> def main():
>    db = psycopg2.connect(
>       host = 'localhost',
>       database = 'gisdb',
>       user = 'postgres',
>       password = '######'
>    )
>    cursor = db.cursor()
>    cursor.execute("DROP TABLE IF EXISTS tubecross")
>    cursor_to.execute("CREATE TABLE tubecross (id serial PRIMARY KEY, station_code char, SAJ interval, SPB interval, SOQ interval);")
>    #cursor.execute("CREATE TABLE tubecross (id serial PRIMARY KEY, num integer, data varchar);")
>    #cursor.execute("INSERT INTO tubecross (num, data) VALUES (%s, %s)",(900, "9abc'def"))
>    cursor_to.execute("INSERT INTO tubecross (station_code, SAJ, SPB, SOQ) VALUES (%s, %s, %s, %s)",(SAJ, 00:00, 00:22, 00:27))
>    db.commit()

> if __name__ == "__main__":
> 	main()

You appear to have two very different versions of the tubecross table.
One version has three fields (id, num, data) and the other version has at
least four (station_code, SAJ, SPB, SOQ).  Which one is correct?

Also, what is the 'cursor_to' variable?  It doesn't appear to be defined
anywhere.

-- 
John Gordon                   A is for Amy, who fell down the stairs
gordon@panix.com              B is for Basil, assaulted by bears
                                -- Edward Gorey, "The Gashlycrumb Tinies"

[toc] | [prev] | [next] | [standalone]

#36536

Hi John,

He're the code I would like to see work. The cursor_to is an oversight. I extracted this element from some other code in an attempt to isolate/resolve the problem myself, hence having a simplified table version. Which works actually, but unfortunately that's not educating me suffieciently. Actual error message I see follows.

- - - - - - - - - - - - - - - - - - - - - - - - - 
Code:

#!/usr/bin/python
import psycopg2
import sys

def main():
   db = psycopg2.connect(
      host = 'localhost',
      database = 'gisdb',
      user = 'postgres',
      password = '######' 
   )
   cursor = db.cursor()
   cursor.execute("DROP TABLE IF EXISTS tubecross")
   cursor.execute("CREATE TABLE tubecross (id serial PRIMARY KEY, station_code char, SAJ interval, SPB interval, SOQ interval);")
   cursor.execute("INSERT INTO tubecross (station_code, SAJ, SPB, SOQ) VALUES (%s, %s, %s, %s)",(SAJ, 00:00, 00:22, 00:27))
   db.commit()

if __name__ == "__main__":
	main()

- - - - - - - - - - - - - - - - - - - - - - - - - 
Error Message:

andyt@andyt-ThinkPad-X61:~/projects/django-stringer/Other/TFLJPAPI$ python creat_db_exp.py
  File "creat_db_exp.py", line 15
    cursor.execute("INSERT INTO tubecross (station_code, SAJ, SPB, SOQ) VALUES (%s, %s, %s, %s)",(SAJ, 00:00, 00:22, 00:27))
                                                                                                         ^
SyntaxError: invalid syntax


Thanks for your help

[toc] | [prev] | [next] | [standalone]

#36539

On Wed 09 Jan 2013 07:19:10 PM EST, andydtaylor@gmail.com wrote:
> Hi John,
>
> He're the code I would like to see work. The cursor_to is an oversight. I extracted this element from some other code in an attempt to isolate/resolve the problem myself, hence having a simplified table version. Which works actually, but unfortunately that's not educating me suffieciently. Actual error message I see follows.
>
> - - - - - - - - - - - - - - - - - - - - - - - - -
> Code:
>
> #!/usr/bin/python
> import psycopg2
> import sys
>
> def main():
>     db = psycopg2.connect(
>        host = 'localhost',
>        database = 'gisdb',
>        user = 'postgres',
>        password = '######'
>     )
>     cursor = db.cursor()
>     cursor.execute("DROP TABLE IF EXISTS tubecross")
>     cursor.execute("CREATE TABLE tubecross (id serial PRIMARY KEY, station_code char, SAJ interval, SPB interval, SOQ interval);")
>     cursor.execute("INSERT INTO tubecross (station_code, SAJ, SPB, SOQ) VALUES (%s, %s, %s, %s)",(SAJ, 00:00, 00:22, 00:27))
>     db.commit()
>
> if __name__ == "__main__":
> 	main()
>
> - - - - - - - - - - - - - - - - - - - - - - - - -
> Error Message:
>
> andyt@andyt-ThinkPad-X61:~/projects/django-stringer/Other/TFLJPAPI$ python creat_db_exp.py
>    File "creat_db_exp.py", line 15
>      cursor.execute("INSERT INTO tubecross (station_code, SAJ, SPB, SOQ) VALUES (%s, %s, %s, %s)",(SAJ, 00:00, 00:22, 00:27))
>                                                                                                           ^
> SyntaxError: invalid syntax
>
>
> Thanks for your help


00:00 etc are not quoted?

 - mitya



--
Lark's Tongue Guide to Python: http://lightbird.net/larks/

[toc] | [prev] | [next] | [standalone]

#36540

On 2013-01-10 00:19, andydtaylor@gmail.com wrote:
> Hi John,
>
> He're the code I would like to see work. The cursor_to is an oversight. I extracted this element from some other code in an attempt to isolate/resolve the problem myself, hence having a simplified table version. Which works actually, but unfortunately that's not educating me suffieciently. Actual error message I see follows.
>
[snip]

> - - - - - - - - - - - - - - - - - - - - - - - - -
> Error Message:
>
> andyt@andyt-ThinkPad-X61:~/projects/django-stringer/Other/TFLJPAPI$ python creat_db_exp.py
>    File "creat_db_exp.py", line 15
>      cursor.execute("INSERT INTO tubecross (station_code, SAJ, SPB, SOQ) VALUES (%s, %s, %s, %s)",(SAJ, 00:00, 00:22, 00:27))
>                                                                                                           ^
> SyntaxError: invalid syntax
>
"00:00", etc, aren't valid Python, they're two ints with a colon
between them.

You need to determine what Python class to use to represent those.

[toc] | [prev] | [next] | [standalone]

#36546

Thanks for your help guys. 

I was actually doing a few things wrong, but I have got this script to work by declaring fields as varchar and all values as strings. But I would like to log journey time values in hours/minutes, so I will have to look into the following:

1. Retrieving this data from postgres as text, converting it and using it. I will need to add/subtract on this time value; or
2. Recognising it as a time class in the first instance by using the string parsing function.

Regards,

Andy

[toc] | [prev] | [next] | [standalone]

#36551

On Wed 09 Jan 2013 09:20:10 PM EST, andydtaylor@gmail.com wrote:
> Thanks for your help guys.
>
> I was actually doing a few things wrong, but I have got this script to work by declaring fields as varchar and all values as strings. But I would like to log journey time values in hours/minutes, so I will have to look into the following:
>
> 1. Retrieving this data from postgres as text, converting it and using it. I will need to add/subtract on this time value; or
> 2. Recognising it as a time class in the first instance by using the string parsing function.
>
> Regards,
>
> Andy


Why not store as an int, in minutes, and then parse into h:m
when displaying?

 - m



--
Lark's Tongue Guide to Python: http://lightbird.net/larks/

[toc] | [prev] | [next] | [standalone]

#36547

Thanks for your help guys. 

I was actually doing a few things wrong, but I have got this script to work by declaring fields as varchar and all values as strings. But I would like to log journey time values in hours/minutes, so I will have to look into the following:

1. Retrieving this data from postgres as text, converting it and using it. I will need to add/subtract on this time value; or
2. Recognising it as a time class in the first instance by using the string parsing function.

Regards,

Andy

[toc] | [prev] | [next] | [standalone]

#36564 — How to run multiline shell command within python

Hello all,

I want to run multiline shell command within python without using a 
command file but directly execute several lines of shell.
I already use *subprocess.checkoutput("csh -f my_file.csh".split())* but 
I want to know if it is posssible to avoid making file and execute
shell lines of code directly.

Example:

cat<<EOF > myfile
echo "foo"
echo "bar"
...
EOF

Regards

Karim

[toc] | [prev] | [next] | [standalone]

#36566 — Re: [Tutor] How to run multiline shell command within python

[Multipart message — attachments visible in raw view] — view raw

On Thu, Jan 10, 2013 at 7:01 AM, Karim <kliateni@gmail.com> wrote:

>
>
> Hello all,
>
> I want to run multiline shell command within python without using a
> command file but directly execute several lines of shell.
> I already use *subprocess.checkoutput("csh -f my_file.csh".split())* but I
> want to know if it is posssible to avoid making file and execute
> shell lines of code directly.
>
>
Yes, this is very possible. Specify shell=True as an argument and you can
do anything you can do in a shell:

 >>> commands = """echo hello
... echo hello | wc -l
... ps aux | grep python"""
>>> b = subprocess.check_output(commands, shell=True)
>>> print(b.decode('ascii'))
hello
1
hugo      1255  1.0  0.6 777316 49924 ?        Sl   09:14   0:08
/usr/bin/python2 /usr/bi
hugo      6529  0.0  0.0  42408  7196 pts/0    S+   09:23   0:00 python
hugo      6559  0.0  0.0  10656  1128 pts/0    S+   09:28   0:00 grep python

>>>

watch out though, accepting user input into the commands variable will lead
to shell injection, which can be a dangerous security vulnerability.

HTH,
Hugo

[toc] | [prev] | [next] | [standalone]

#36570 — Re: [Tutor] How to run multiline shell command within python

[Multipart message — attachments visible in raw view] — view raw

On 10/01/2013 09:31, Hugo Arts wrote:
> On Thu, Jan 10, 2013 at 7:01 AM, Karim <kliateni@gmail.com 
> <mailto:kliateni@gmail.com>> wrote:
>
>
>
>     Hello all,
>
>     I want to run multiline shell command within python without using
>     a command file but directly execute several lines of shell.
>     I already use *subprocess.checkoutput("csh -f
>     my_file.csh".split())* but I want to know if it is posssible to
>     avoid making file and execute
>     shell lines of code directly.
>
>
> Yes, this is very possible. Specify shell=True as an argument and you 
> can do anything you can do in a shell:
>
>  >>> commands = """echo hello
> ... echo hello | wc -l
> ... ps aux | grep python"""
> >>> b = subprocess.check_output(commands, shell=True)
> >>> print(b.decode('ascii'))
> hello
> 1
> hugo      1255  1.0  0.6 777316 49924 ?        Sl 09:14   0:08 
> /usr/bin/python2 /usr/bi
> hugo      6529  0.0  0.0  42408  7196 pts/0    S+ 09:23   0:00 python
> hugo      6559  0.0  0.0  10656  1128 pts/0    S+ 09:28   0:00 grep python
>
> >>>
>
> watch out though, accepting user input into the commands variable will 
> lead to shell injection, which can be a dangerous security vulnerability.
>
> HTH,
> Hugo

Many thanks Hugo. It makes my day!
In my case there are no possibilities for shell injection. It is 
internal to a class.

Regards
Karim

[toc] | [prev] | [next] | [standalone]

#36572 — Re: [Tutor] How to run multiline shell command within python

[Multipart message — attachments visible in raw view] — view raw

Have you looked a the pexpect class? It works like gangbusters, especially
if you're trying to run something with an interactive shell.

http://www.noah.org/wiki/pexpect


On Thu, Jan 10, 2013 at 9:25 AM, Karim <kliateni@gmail.com> wrote:

>  On 10/01/2013 09:31, Hugo Arts wrote:
>
> On Thu, Jan 10, 2013 at 7:01 AM, Karim <kliateni@gmail.com> wrote:
>
>>
>>
>> Hello all,
>>
>> I want to run multiline shell command within python without using a
>> command file but directly execute several lines of shell.
>> I already use *subprocess.checkoutput("csh -f my_file.csh".split())* but
>> I want to know if it is posssible to avoid making file and execute
>> shell lines of code directly.
>>
>>
>  Yes, this is very possible. Specify shell=True as an argument and you
> can do anything you can do in a shell:
>
>   >>> commands = """echo hello
> ... echo hello | wc -l
> ... ps aux | grep python"""
> >>> b = subprocess.check_output(commands, shell=True)
> >>> print(b.decode('ascii'))
>  hello
> 1
> hugo      1255  1.0  0.6 777316 49924 ?        Sl   09:14   0:08
> /usr/bin/python2 /usr/bi
> hugo      6529  0.0  0.0  42408  7196 pts/0    S+   09:23   0:00 python
> hugo      6559  0.0  0.0  10656  1128 pts/0    S+   09:28   0:00 grep
> python
>
>  >>>
>
>  watch out though, accepting user input into the commands variable will
> lead to shell injection, which can be a dangerous security vulnerability.
>
>  HTH,
> Hugo
>
>
> Many thanks Hugo. It makes my day!
> In my case there are no possibilities for shell injection. It is internal
> to a class.
>
> Regards
> Karim
>
>
> --
> http://mail.python.org/mailman/listinfo/python-list
>
>

[toc] | [prev] | [next] | [standalone]

#36583 — Re: [Tutor] How to run multiline shell command within python

[Multipart message — attachments visible in raw view] — view raw

On 10/01/2013 16:21, Matty Sarro wrote:
> Have you looked a the pexpect class? It works like gangbusters, 
> especially if you're trying to run something with an interactive shell.
>
> http://www.noah.org/wiki/pexpect
>
>
> On Thu, Jan 10, 2013 at 9:25 AM, Karim <kliateni@gmail.com 
> <mailto:kliateni@gmail.com>> wrote:
>
>     On 10/01/2013 09:31, Hugo Arts wrote:
>>     On Thu, Jan 10, 2013 at 7:01 AM, Karim <kliateni@gmail.com
>>     <mailto:kliateni@gmail.com>> wrote:
>>
>>
>>
>>         Hello all,
>>
>>         I want to run multiline shell command within python without
>>         using a command file but directly execute several lines of shell.
>>         I already use *subprocess.checkoutput("csh -f
>>         my_file.csh".split())* but I want to know if it is posssible
>>         to avoid making file and execute
>>         shell lines of code directly.
>>
>>
>>     Yes, this is very possible. Specify shell=True as an argument and
>>     you can do anything you can do in a shell:
>>
>>      >>> commands = """echo hello
>>     ... echo hello | wc -l
>>     ... ps aux | grep python"""
>>     >>> b = subprocess.check_output(commands, shell=True)
>>     >>> print(b.decode('ascii'))
>>     hello
>>     1
>>     hugo      1255  1.0  0.6 777316 49924 ?      Sl   09:14   0:08
>>     /usr/bin/python2 /usr/bi
>>     hugo      6529  0.0  0.0  42408  7196 pts/0    S+   09:23   0:00
>>     python
>>     hugo      6559  0.0  0.0  10656  1128 pts/0    S+   09:28   0:00
>>     grep python
>>
>>     >>>
>>
>>     watch out though, accepting user input into the commands variable
>>     will lead to shell injection, which can be a dangerous security
>>     vulnerability.
>>
>>     HTH,
>>     Hugo
>
>     Many thanks Hugo. It makes my day!
>     In my case there are no possibilities for shell injection. It is
>     internal to a class.
>
>     Regards
>     Karim
>
>
>     --
>     http://mail.python.org/mailman/listinfo/python-list
>
>

Thanks Matty!

I will have a look specially for interactive session.

Regards
Karim

[toc] | [prev] | [standalone]

Back to top | Article view | comp.lang.python

csiph-web