Python Forum
python and postgres DB SQL execution
Thread Rating:
  • 1 Vote(s) - 4 Average
  • 1
  • 2
  • 3
  • 4
  • 5
python and postgres DB SQL execution
#1
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 ?
Reply
#2
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()
Reply
#3
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.
Reply
#4
https://pypi.org/project/psycopg2-contextmanager/
"As they say in Mexico 'dosvidaniya'. That makes two vidaniyas."
https://freedns.afraid.org
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  kill python execution program lebossejames 0 182 Mar-16-2024, 11:16 AM
Last Post: lebossejames
  Confused about python execution jpezz 4 1,324 Oct-09-2022, 06:56 PM
Last Post: Gribouillis
  Migrating data from oracle into postgres python_student 1 2,363 Feb-10-2022, 09:16 PM
Last Post: buran
  install apache-airflow[postgres,google] on Python 3.8.12 virtual env ShahajaK 1 6,326 Oct-07-2021, 03:05 PM
Last Post: Larz60+
  How to to tie the execution of one process to another inside a loop in Python ignorant_wanderer 0 2,018 Jul-11-2020, 03:44 AM
Last Post: ignorant_wanderer
  Python - change variable type during program execution ple 1 2,331 Apr-12-2020, 08:43 AM
Last Post: buran
  How to get memory usage and execution time of each line in python SriRajesh 2 3,044 Mar-07-2019, 12:59 PM
Last Post: SriRajesh
  Python Thread stops execution neethuvp 1 3,353 Feb-18-2019, 06:36 PM
Last Post: micseydel
  How to Make Python code execution pause and resume to create .csv and read values. Kashi 2 3,716 Jun-14-2018, 04:16 PM
Last Post: DeaD_EyE
  How does multi-processing in python increase the execution time? kadsank 0 2,292 Jan-15-2018, 01:15 PM
Last Post: kadsank

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020