Postgres Connection from Python

Standard

python

In this tutorial i will try to show you how to connect and interact with a Postgresql database from an small python program, by using the psycopg2 library. We can see how to install it from the official site.

Now, the following step is to create a test database, by using the next commands.

create database test;
\c test
create table departments(id int, name varchar(50));
create table users(id int, id_dep int, name varchar(50), income float);

insert into departments values(1,'dep1');
insert into departments values(2,'dep2');
insert into departments values(3,'dep3');
insert into departments values(4,'dep4');

insert into users values(1,1,'User1',1400.0);
insert into users values(2,1,'User2',2400.0);
insert into users values(3,1,'User3',13400.0);
insert into users values(4,2,'User4',14400.0);
insert into users values(5,3,'User5',1240.0);
insert into users values(6,3,'User6',1420.0);
insert into users values(7,3,'User7',14550.0);
insert into users values(8,3,'User8',12100.0);
insert into users values(9,4,'User9',16500.0);
insert into users values(10,4,'User10',5400.0);
insert into users values(11,4,'User11',3400.0);
insert into users values(12,4,'User12',45400.0);

Note that in this database we have only two relations, departments and users, where each department may have 0 or more users, while a user may be in only one department.

Once our database is created and filled with data, we can throw queries from our python program. Too do so, we need to import the psycopg2 library.

import psycopg2

After that, we need our basis code to connect to postgres, which we can observe in the next code.

Getting Postgres Version.

con = None
try:   
    con = psycopg2.connect(database='test', user='postgres') 
    cur = con.cursor()
    cur.execute('SELECT version()')          
    ver = cur.fetchone()
    print ver    
except psycopg2.DatabaseError, e:
    print 'Error %s' % e    
    sys.exit(1)
finally:
    if con:
        con.close()

From the code above, we can observe the con object, which is the connector to our database. After we give away the needed parameters (database and user), we create our query and execute it right from our connection object. Note the try and catch blocks, which allow us to catch the connection exceptions and successfully close the connection.

Inserting Data.

In order to save some space, we will focus on code in the try block from now on…

To insert data in our tables, its pretty easy,

con = psycopg2.connect("dbname='testdb' user='janbodnar'")   
cur = con.cursor()
cur.execute("INSERT INTO Users VALUES(10,1,'NewUser1',5264.2)")
cur.execute("INSERT INTO Users VALUES(11,1,'NewUser2',5712.7)")
cur.execute("INSERT INTO Users VALUES(12,2,'NewUser3',900.0)")
cur.execute("INSERT INTO Users VALUES(13,3,'NewUser4',2900.0)")
cur.execute("INSERT INTO Users VALUES(14,4,'NewUser5',35000.0)")
    
con.commit()

With this easy code we have our new data saved in our database.

Retrieving Data.

To send a SELECT statement, the code looks very similar, but the new step is to iterate throw retrieved rows.

con = psycopg2.connect("dbname='testdb' user='janbodnar'")   
cur = con.cursor()
cur.execute("SELECT departments.name, count(*) FROM Users,Departments WHERE users.id_dep = departments.id GROUP BY departments.name")
data = cur.fetchall()
for row in data:
print row

Now we can see how many users are by every department in our database.

This is it for this post, in the next we will see how to update, delete, and construct parametrized queries.

Leave a Reply

Your email address will not be published. Required fields are marked *