[triangle-zpug] Python and psycopg

[Tom Bryan]

We were discussing Python and PostreSQL at the meeting last night.  I thought 
that I'd go ahead and send some links and sample code since I've got it 
available.  I ripped this code from the EcoAccess project's unit tests, and I 
didn't test the code after I removed it from its original file, so it may not 
quite work.  

The psycopg page is here: http://initd.org/software/psycopg
If you're on a Red Hat system, I have an RPM spec file for compiling and 
wrapping the psycopg Python and compiled C code into a binary RPM.  If you're 
interested, send me an e-mail.

-- SQL script to create the table needed by the example below
create sequence ECOUSER_ID_SEQ start 1 increment 1 maxvalue 2147483647 
minvalue 1  cache 10;
create table ECOUSER (
        ECOUSER_ID          int4 default nextval('ECOUSER_ID_SEQ') not null,
        ECOUSER_NAME        varchar(20) not null,
        ECOUSER_PASSWORD    varchar(20) not null,
        ACTIVE_FLAG         bool default FALSE not null,
        LAST_NAME           varchar(40),
        FIRST_NAME          varchar(40),
        EMAIL               varchar(40),
        CHALLENGE_QUESTION  text,
        CHALLENGE_ANSWER    text,
        LOGIN_TOKEN         int8,
        LAST_LOGIN_DATE     timestamp,
        FAILED_LOGIN1       varchar(100),
        FAILED_LOGIN2       varchar(100),
        FAILED_LOGIN3       varchar(100),
        FAILED_LOGIN4       varchar(100),
        FAILED_LOGIN5       varchar(100),
        constraint ecouser_pkey primary key (ECOUSER_ID),
        constraint ecouser_uq1 unique (ECOUSER_NAME)
);


# Python code to insert and fetch some data
base_users = (
    {'userId': 1, 'name':'darcy', 'password': 'peEhH7F4iC2l6', 
     'first_name': 'Darcy', 'last_name': 'Fitzwilliam', 
     'email': 'darcy at pemberly.org', 'active_flag':1},
    {'userId': 2, 'name': 'lizzy', 'password': 'peEhH7F4iC2l6', 
     'first_name': 'Bennett', 'last_name': 'Elizabeth', 
     'email': 'lizzy at pemberly.org', 'active_flag': 1},
    {'userId': 3, 'name': 'emma', 'password': 'IDta7uvnvGYCY', 
     'first_name': 'Woodhouse', 'last_name': 'Emma', 
     'email': 'emma at donwell.org', 'active_flag': 1 }
}
import psycopg
conn = psycopg.connect( 'dbname=ecoaccess' )
insert_cursor = conn.cursor()
for user in base_users:
	# Note that we don't use % and interpolate the values ourselves
	insert_cursor.execute(
		"""INSERT into ECOUSER (ecouser_id, ecouser_name, ecouser_password,
		first_name, last_name, email, active_flag) values 
		(%(userId)d, %(name)s,%(password)s, %(first_name)s, 
		%(last_name)s, %(email)s,%(active_flag)d::bool); """
                , user )
conn.commit()
insert_cursor.close()

select_cursor = conn.cursor()
cursor.execute(
    """SELECT login_token, last_login_date from ECOUSER 
    where ECOUSER_NAME = %(name)s""", {'name': 'darcy'} )
results = cursor.fetchone()
select_cursor.close()
print results
conn.close()


One thing to note in this code.  While I'm using the Python dictionary style 
of interpolation in the string "%(key)s", I'm *not* actually interpolating 
the dictionary values into the string and then passing it into pyscopg.  I 
pass the string and the dictionary into psycopg.  The reason we do this is 
that one hopes that the DB supports DB statement caching.  So, basically, we 
hope that the driver and the DB do something with that insert string that 
looks like 
"INSERT into ECOUSER (ecouser_id, ecouser_name, ecouser_password,
first_name, last_name, email, active_flag) values 
(?,?,?,?,?,?,?);"
Then, for each call to insert, the driver would bind specific values to the 
placeholders (the ? in this example).  For large apps and DBs, this is 
important because otherwise, in a tight code loop like that, the DB may have 
to recreate the identical execution plan for each call to execute().  In 
general, you'd like to write your SQL statements with placeholders and let 
the driver do the interpolation of values into the string.  

Unfortunately, the format in Python of the string you pass to the execute() 
call varies between drivers.  The DB API specifies several acceptable 
formats.  You can always pass a string with all of the values already 
interpolated, but then you have to worry about DB quoting rules, and you 
probably prevent the DB driver from doing any statement caching.  

Did I mention at the meeting that I primarily do server-side, DB apps? :)

Finally, the "embedded" DB I was trying to remember at the meeting is SQLite.  
See http://www.hwaci.com/sw/sqlite/index.html  
There is a Python driver for the DB, but I haven't used it.  
http://www.sourceforge.net/projects/pysqlite

Happy Pythoning, 
---Tom