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

Re: Psycopg2 SyntaxError: invalid syntax on "INSERT INTO" database
 

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"

Re: Psycopg2 SyntaxError: invalid syntax on "INSERT INTO" database
 

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

Re: Psycopg2 SyntaxError: invalid syntax on "INSERT INTO" database
 

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/

Re: Psycopg2 SyntaxError: invalid syntax on "INSERT INTO" database
 

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.

Re: Psycopg2 SyntaxError: invalid syntax on "INSERT INTO" database
 

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

Re: Psycopg2 SyntaxError: invalid syntax on "INSERT INTO" database
 

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

Re: Psycopg2 SyntaxError: invalid syntax on "INSERT INTO" database
 

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/

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

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

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

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