blog [mike atlas]

I had to teach a co-worker how to insert data from a comma separated value file to a PostgreSQL database table today, using Python as the scripting language. This utilizes the pyscopg2 database adapter for Python 2.4.

import psycopg2, sys

try:
	conn = psycopg2.connect("""dbname='mydatabase'
		user='mikeatlas'
		host='localhost'
		port='5432'
		password='apassword'""")
except:
	print "Unable to connect to the database"
try:
	myfile = open('mydata.csv', 'r')
	for line in myfile:
		splitlineArray = line.split(',')
	cur = conn.cursor()
	sqlStatement = """INSERT INTO myTable
			(col0,  col1,  col2,  col3,  col4)
			VALUES ('"""+splitlineArray[0]+""",
				'"""+splitlineArray[1]+"""',
				'"""+splitlineArray[2]+"""',
				'"""+splitlineArray[3]+"""',
				'"""+splitlineArray[4]+"""')"""
	cur.execute(sqlStatement)
except:
	print "Error looping file into database"
try:
	conn.commit()
except:
	conn.rollback()
	conn.close()
	print "Error with transaction"

This post is filed under Computer Science. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.