psycopg2 cursor.execute CREATE TABLE issue

A

andydtaylor

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

Mitya Sirenef

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
 
A

andydtaylor

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
 
A

andydtaylor

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
 
M

Mitya Sirenef

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
 
C

Chris Angelico

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
 

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,818
Messages
2,569,736
Members
45,709
Latest member
DRISenaida

Latest Threads

Top