psycopg2 cursor.execute CREATE TABLE issue

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

  1. Guest

    Hi all,

    I'm trying to create a process which will create a new table and populate it.

    But something is preventing this from working, and I don't know enough to figure it out, despite having spent most of today reading up. The code executes with no error, yet no table is created or populated.

    Can anyone offer me some advice? code below.

    Thanks,

    Andy

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

    def main():
    db = psycopg2.connect(
    host = 'localhost',
    database = 'gisdb',
    user = 'postgres',
    password = 'L1ncoln0ut@'
    )
    cursor = db.cursor()
    cursor.execute("CREATE TABLE test (id serial PRIMARY KEY, num integer, data varchar);")
    cursor.execute("INSERT INTO test (num, data) VALUES (%s, %s)",(100, "abc'def"))

    if __name__ == "__main__":
    main()
    , Jan 6, 2013
    #1
    1. Advertising

  2. On Sun 06 Jan 2013 04:38:29 PM EST, wrote:
    > Hi all,
    >
    > I'm trying to create a process which will create a new table and populate it.
    >
    > But something is preventing this from working, and I don't know enough to figure it out, despite having spent most of today reading up. The code executes with no error, yet no table is created or populated.
    >
    > Can anyone offer me some advice? code below.
    >
    > Thanks,
    >
    > Andy
    >
    > #!/usr/bin/python
    > import psycopg2
    > import sys
    >
    > def main():
    > db = psycopg2.connect(
    > host = 'localhost',
    > database = 'gisdb',
    > user = 'postgres',
    > password = 'L1ncoln0ut@'
    > )
    > cursor = db.cursor()
    > cursor.execute("CREATE TABLE test (id serial PRIMARY KEY, num integer, data varchar);")
    > cursor.execute("INSERT INTO test (num, data) VALUES (%s, %s)",(100, "abc'def"))
    >
    > if __name__ == "__main__":
    > main()



    To commit a transaction, you need to do a db.commit() call.

    -m



    --
    Lark's Tongue Guide to Python: http://lightbird.net/larks/
    Mitya Sirenef, Jan 6, 2013
    #2
    1. Advertising

  3. Guest

    Wow it's as simple as that! I'm afraid my database experience is in Microsoft Access in Windows and not at the command line, so that wasn't intuitive for me.

    Thanks again,

    Andy
    , Jan 6, 2013
    #3
  4. Guest

    Wow it's as simple as that! I'm afraid my database experience is in Microsoft Access in Windows and not at the command line, so that wasn't intuitive for me.

    Thanks again,

    Andy
    , Jan 6, 2013
    #4
  5. On Sun 06 Jan 2013 04:53:32 PM EST, wrote:
    > Wow it's as simple as that! I'm afraid my database experience is in Microsoft Access in Windows and not at the command line, so that wasn't intuitive for me.
    >
    > Thanks again,
    >
    > Andy



    IIRC I made the same mistake when I was using psycopg for the first
    time.
    I think wrapper libraries like sqlalchemy usually have myrecord.save()
    method which is more intuitive.

    -m


    --
    Lark's Tongue Guide to Python: http://lightbird.net/larks/
    Mitya Sirenef, Jan 6, 2013
    #5
  6. On Mon, Jan 7, 2013 at 9:14 AM, Mitya Sirenef <> wrote:
    > On Sun 06 Jan 2013 04:53:32 PM EST, wrote:
    >>
    >> Wow it's as simple as that! I'm afraid my database experience is in
    >> Microsoft Access in Windows and not at the command line, so that wasn't
    >> intuitive for me.
    >>

    > IIRC I made the same mistake when I was using psycopg for the first time.
    > I think wrapper libraries like sqlalchemy usually have myrecord.save()
    > method which is more intuitive.


    I recommend getting used to thinking in terms of transactions and
    commits. Instead of saving a record, commit a unit of work, which
    might involve several changes all at once. A good database (like
    PostgreSQL) will guarantee you that either the whole transaction has
    happened, or none of it has. And normally, once your commit call has
    returned (assuming it doesn't raise an error), you're guaranteed that
    the transaction has been completely written to durable storage. Of
    course, that depends on *having* durable storage, and many SSDs lie
    about what's been written, but that's outside the scope of this post!

    ChrisA
    Chris Angelico, Jan 6, 2013
    #6
  7. Walter Hurry Guest

    On Sun, 06 Jan 2013 16:44:47 -0500, Mitya Sirenef wrote:

    > On Sun 06 Jan 2013 04:38:29 PM EST, wrote:
    >> Hi all,
    >>
    >> I'm trying to create a process which will create a new table and
    >> populate it.
    >>
    >> But something is preventing this from working, and I don't know enough
    >> to figure it out, despite having spent most of today reading up. The
    >> code executes with no error, yet no table is created or populated.
    >>
    >> Can anyone offer me some advice? code below.
    >>
    >> Thanks,
    >>
    >> Andy
    >>
    >> #!/usr/bin/python import psycopg2 import sys
    >>
    >> def main():
    >> db = psycopg2.connect(
    >> host = 'localhost', database = 'gisdb', user = 'postgres',
    >> password = 'L1ncoln0ut@'
    >> )
    >> cursor = db.cursor()
    >> cursor.execute("CREATE TABLE test (id serial PRIMARY KEY, num
    >> integer, data varchar);")
    >> cursor.execute("INSERT INTO test (num, data) VALUES (%s, %s)",(100,
    >> "abc'def"))
    >>
    >> if __name__ == "__main__":
    >> main()

    >
    >
    > To commit a transaction, you need to do a db.commit() call.


    Or set autocommit = True on the database connection object
    Walter Hurry, Jan 6, 2013
    #7
    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. =?Utf-8?B?VG9tYXMgS2VwaWM=?=

    Changing DEFAULT cursor to WAIT cursor in ASP

    =?Utf-8?B?VG9tYXMgS2VwaWM=?=, Apr 5, 2005, in forum: ASP .Net
    Replies:
    1
    Views:
    2,293
    Bruce Barker
    Apr 5, 2005
  2. invy
    Replies:
    4
    Views:
    379
    CBFalconer
    Dec 28, 2006
  3. ASh
    Replies:
    10
    Views:
    2,380
    Anton Shishkov
    Mar 31, 2010
  4. mrdrew
    Replies:
    5
    Views:
    2,689
    Dennis Lee Bieber
    Apr 5, 2010
  5. dmaziuk
    Replies:
    3
    Views:
    553
    Chris Gonnerman
    Jan 25, 2011
Loading...

Share This Page