Psycopg2 SyntaxError: invalid syntax on "INSERT INTO" database

[andydtaylor]

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()

 

[John Gordon]

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.

 

[andydtaylor]

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

 

[Mitya Sirenef]

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

 

[MRAB]

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.

 

[andydtaylor]

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

 

[andydtaylor]

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

 

[Mitya Sirenef]

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

 

[Hugo Arts]

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:
.... echo hello | wc -l
.... ps aux | grep python"""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

 

[Karim]

On Thu, Jan 10, 2013 at 7:01 AM, Karim <[email protected]



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:

... echo hello | wc -l
... ps aux | grep python"""
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