Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Python > Psycopg2 SyntaxError: invalid syntax on "INSERT INTO" database

Reply
Thread Tools

Psycopg2 SyntaxError: invalid syntax on "INSERT INTO" database

 
 
andydtaylor@gmail.com
Guest
Posts: n/a
 
      01-09-2013
Hi,

I'm a bit stuck on this "INSERT INTO" syntax error. I have no idea why it'snot 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 setme 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()
 
Reply With Quote
 
 
 
 
John Gordon
Guest
Posts: n/a
 
      01-10-2013
In <(E-Mail Removed)> http://www.velocityreviews.com/forums/(E-Mail Removed) 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
(E-Mail Removed) B is for Basil, assaulted by bears
-- Edward Gorey, "The Gashlycrumb Tinies"

 
Reply With Quote
 
 
 
 
andydtaylor@gmail.com
Guest
Posts: n/a
 
      01-10-2013
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/resolvethe 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
 
Reply With Quote
 
Mitya Sirenef
Guest
Posts: n/a
 
      01-10-2013
On Wed 09 Jan 2013 07:19:10 PM EST, (E-Mail Removed) 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/
 
Reply With Quote
 
MRAB
Guest
Posts: n/a
 
      01-10-2013
On 2013-01-10 00:19, (E-Mail Removed) 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.
 
Reply With Quote
 
andydtaylor@gmail.com
Guest
Posts: n/a
 
      01-10-2013
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
 
Reply With Quote
 
andydtaylor@gmail.com
Guest
Posts: n/a
 
      01-10-2013
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
 
Reply With Quote
 
Mitya Sirenef
Guest
Posts: n/a
 
      01-10-2013
On Wed 09 Jan 2013 09:20:10 PM EST, (E-Mail Removed) 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/
 
Reply With Quote
 
Hugo Arts
Guest
Posts: n/a
 
      01-10-2013
On Thu, Jan 10, 2013 at 7:01 AM, Karim <(E-Mail Removed)> 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

 
Reply With Quote
 
Karim
Guest
Posts: n/a
 
      01-10-2013
On 10/01/2013 09:31, Hugo Arts wrote:
> On Thu, Jan 10, 2013 at 7:01 AM, Karim <(E-Mail Removed)
> <(E-Mail Removed)>> 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


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
psycopg2 insertion and reading binary data to PostgreSQL database(bytea datatype) romap@libero.it Python 0 03-01-2011 10:57 AM
psycopg2 for insertion of binary data to PostgreSQL database Julia Jacobson Python 7 08-23-2010 08:13 AM
psycopg2 / psycopg2.ProgrammingError: syntax error at or near"E'mytable'" mrdrew Python 5 04-05-2010 05:27 PM
psycopg2 / psycopg2.DataError: invalid input syntax for type ASh Python 10 03-31-2010 10:50 AM
Can't Write to PostGIS PostGreSQL database via psycopg2 David Michael Schruth, Python 2 10-23-2007 05:37 PM



Advertisments