Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.python > #36287 > unrolled thread

psycopg2 cursor.execute CREATE TABLE issue

Back to article view | Back to comp.lang.python

Contents

  psycopg2 cursor.execute CREATE TABLE issue andydtaylor@gmail.com - 2013-01-06 13:38 -0800
    Re: psycopg2 cursor.execute CREATE TABLE issue Mitya Sirenef <msirenef@lightbird.net> - 2013-01-06 16:44 -0500
      Re: psycopg2 cursor.execute CREATE TABLE issue Walter Hurry <walterhurry@lavabit.com> - 2013-01-06 22:37 +0000
    Re: psycopg2 cursor.execute CREATE TABLE issue Chris Angelico <rosuav@gmail.com> - 2013-01-07 08:45 +1100
      Re: psycopg2 cursor.execute CREATE TABLE issue andydtaylor@gmail.com - 2013-01-06 13:53 -0800
        Re: psycopg2 cursor.execute CREATE TABLE issue Mitya Sirenef <msirenef@lightbird.net> - 2013-01-06 17:14 -0500
        Re: psycopg2 cursor.execute CREATE TABLE issue Chris Angelico <rosuav@gmail.com> - 2013-01-07 09:19 +1100
      Re: psycopg2 cursor.execute CREATE TABLE issue andydtaylor@gmail.com - 2013-01-06 13:53 -0800

#36287 — psycopg2 cursor.execute CREATE TABLE issue

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

[toc] | [next] | [standalone]

#36288

On Sun 06 Jan 2013 04:38:29 PM EST, andydtaylor@gmail.com 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/

[toc] | [prev] | [next] | [standalone]

#36299

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

> On Sun 06 Jan 2013 04:38:29 PM EST, andydtaylor@gmail.com 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

[toc] | [prev] | [next] | [standalone]

#36289

On Mon, Jan 7, 2013 at 8:38 AM,  <andydtaylor@gmail.com> wrote:
> 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.

Standard databasing requirement: You need to commit your work.

http://initd.org/psycopg/docs/connection.html#connection.commit

Otherwise, the transaction gets rolled back, and nobody sees your changes.

ChrisA

[toc] | [prev] | [next] | [standalone]

#36290

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

[toc] | [prev] | [next] | [standalone]

#36292

On Sun 06 Jan 2013 04:53:32 PM EST, andydtaylor@gmail.com 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/

[toc] | [prev] | [next] | [standalone]

#36294

On Mon, Jan 7, 2013 at 9:14 AM, Mitya Sirenef <msirenef@lightbird.net> wrote:
> On Sun 06 Jan 2013 04:53:32 PM EST, andydtaylor@gmail.com 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

[toc] | [prev] | [next] | [standalone]

#36298

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

[toc] | [prev] | [standalone]

Back to top | Article view | comp.lang.python

csiph-web