Python Forum

Hi
I'm attempting to execute several create table statements in Python3 onto a PostgreSQL 10.4 database.
The python3 code looks like this:

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

con_string = "host='localhost' dbname='warehouse_db' user='user1' password='xxx'"
con = psycopg2.connect(con_string)
c = con.cursor()

# Open and read the file as a single buffer
fd = open('create_tables_restaurant.sql', 'r')
sqlFile = fd.read()
fd.close()

# all SQL commands (split on ';')
sqlCommands = sqlFile.split(';')

# Execute every command from the input file
for command in sqlCommands:
   if command:
     print("Command: ", command)
     c.execute(command)
     print("Success")

c.close()
con.close()

The output appears OK, but when the tables do not exist in the database!
Output:
$ ./create_warehouse.py
/home/google_cloud_user/.local/lib/python3.6/site-packages/psycopg2/__init__.py:144: UserWarning: The psycopg2 wheel package will be renamed from release 2.8; in order to keep installing from binary please use "pip install psycopg2-binary" instead. For details see: <http://initd.org/psycopg/docs/install.html#binary-install-from-pypi>.
""")

Command to execute:  create table warehouse (
warehouse_id integer primary key
,w_name varchar(50) not null
,w_add1 varchar(40) not null
,w_add2 varchar(40) null
,w_town varchar(50) null
,w_county varchar(50) not null
,w_postcode varchar(8) not null
)
Success
Command to execute:  
create table item (
item_id integer primary key
,item_name varchar(50)
,item_price DECIMAL(6,2)
)
Success
..
..
.

Can anybody see anything that might be incorrect ?
Is it perhaps something to do with the warning ?

Try setting the autocommit to True.

con_string = "host='localhost' dbname='warehouse_db' user='user1' password='xxx'"
con = psycopg2.connect(con_string)
con.autocommit = True
c = con.cursor()

thanks,
I got it working by including con.comit()
I tried a similar exercise with Oracle 11g using cx_Oracle, which didn't require a commit. I thought DDL does not require commit or rollback.