Unable to execute Postgres queries in Python

Question

I'm trying to create a Postgres table using psycopg2 in Python as follows:

import psycopg2

class DbOperations (object):

def __init__(self):

    self.dummy = None
    self.conn = None
    self.cur = None
    self.query = None
    self.db_name = "alarm_log"
    self.table_name = "alarms"
    self.user = "cayman"
    self.password = "admin"
    self.host = "127.0.0.1"

def db_connect(self):

    self.conn = psycopg2.connect(dbname=self.db_name, user=self.user, password=self.password, host=self.host)
    self.cur = self.conn.cursor()

def db_disconnect(self):

    self.conn.close()

def db_create_table(self):

    self.query ="""
                CREATE TABLE COMPANY(
           ID INT PRIMARY KEY     NOT NULL,
           NAME           TEXT    NOT NULL,
           AGE            INT     NOT NULL,
           ADDRESS        CHAR(50),
           SALARY         REAL
        );
    """
    print (self.query)
    self.cur.execute(self.query)

Then I construct the object as follows:

db_app = DbOperations()
db_app.db_connect()
db_app.db_create_table()

I am able to manually connect to the database and create the table. However, I'm not able to do so using Python. There are no exceptions or error messages. When I try to list the tables in the database manually, I don't find my newly created table. Any suggestions what could be wrong ?

So what went wrong?, Any error message to post that could help solving your case? :) — Iron Fist
– Iron Fist, Commented Jan 13, 2017 at 18:38
Eventually there are no exceptions or error messages. When I try to list the tables in the database manually, I don't find my newly created table — Joseph Wahba
– Joseph Wahba, Commented Jan 13, 2017 at 18:39

Iron Fist · Accepted Answer · 2017-01-13 18:50:11Z

2

Seems, you are missing the commit at the end of db_create_table method:

self.conn.commit()

answered Jan 13, 2017 at 18:50

Iron Fist

11k2 gold badges20 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Joseph Wahba Over a year ago

Thanks it worked. I was following Python's documentation for psycopg2 but it never mentioned so! weird

Iron Fist Over a year ago

Can you post the link to it?

FlipperPA · Accepted Answer · 2017-01-13 18:55:11Z

1

Iron Fist's answer is absolutely correct, but if you don't want to have commits all over your code, you can also set it on the connection like this:

def db_connect(self):
    self.conn = psycopg2.connect(dbname=self.db_name, user=self.user, password=self.password, host=self.host)
    self.conn.autocommit = True
    self.cur = self.conn.cursor()

answered Jan 13, 2017 at 18:55

FlipperPA

14.4k5 gold badges46 silver badges76 bronze badges

Collectives™ on Stack Overflow

Unable to execute Postgres queries in Python

2 Answers 2

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Related