Psycopg2 SyntaxError: invalid syntax on "INSERT INTO" database

Discussion in 'Python' started by andydtaylor@gmail.com, Jan 9, 2013.

  1. Guest

    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()
     
    , Jan 9, 2013
    #1
    1. Advertising

  2. John Gordon Guest

    In <> 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
    B is for Basil, assaulted by bears
    -- Edward Gorey, "The Gashlycrumb Tinies"
     
    John Gordon, Jan 10, 2013
    #2
    1. Advertising

  3. Guest

    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
     
    , Jan 10, 2013
    #3
  4. On Wed 09 Jan 2013 07:19:10 PM EST, 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/
     
    Mitya Sirenef, Jan 10, 2013
    #4
  5. MRAB Guest

    On 2013-01-10 00:19, 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.
     
    MRAB, Jan 10, 2013
    #5
  6. Guest

    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
     
    , Jan 10, 2013
    #6
  7. Guest

    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
     
    , Jan 10, 2013
    #7
  8. On Wed 09 Jan 2013 09:20:10 PM EST, 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/
     
    Mitya Sirenef, Jan 10, 2013
    #8
  9. Hugo Arts Guest

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

    On Thu, Jan 10, 2013 at 7:01 AM, Karim <> 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
     
    Hugo Arts, Jan 10, 2013
    #9
  10. Karim Guest

    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 <
    > <mailto:>> 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
     
    Karim, Jan 10, 2013
    #10
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. DIBS

    SyntaxError: invalid syntax

    DIBS, Dec 27, 2005, in forum: Python
    Replies:
    5
    Views:
    39,392
    Terry Hancock
    Dec 27, 2005
  2. ronrsr

    SyntaxError: Invalid Syntax.

    ronrsr, Nov 10, 2006, in forum: Python
    Replies:
    10
    Views:
    1,385
    John Machin
    Nov 10, 2006
  3. Replies:
    3
    Views:
    1,649
  4. ASh
    Replies:
    10
    Views:
    2,444
    Anton Shishkov
    Mar 31, 2010
  5. mrdrew
    Replies:
    5
    Views:
    2,762
    Dennis Lee Bieber
    Apr 5, 2010
Loading...

Share This Page