Good way to read csvData using psycopg2

Question

I am trying to quickly get fast, but not very much code to get csv data in a postgres database. I read in python using csvDictreader, which works fine. Then I need to somehow generate code that takes dicts and puts it in a table. I want to do this automatically, since my tables often contain hundreds of variables. (I don't want to read Postgres directly, because in many cases I have to convert the data, and python is suitable for this)

Here is what I got:

import psycopg2 import sys import itertools import sys, csv import psycopg2.extras import psycopg2.extensions csvReader=csv.DictReader(open( '/home/matthew/Downloads/us_gis_data/statesp020.csv', "rb"), delimiter = ',') #close.cursor() x = 0 ConnectionString = "host='localhost' dbname='mydb' user='postgres' password='######" try: connection = psycopg2.extras.DictConnection(ConnectionString) print "connecting" except: print "did not work" # Create a test table with some data dict_cur = connection.cursor() #dict_cur.execute("CREATE TABLE test (id serial PRIMARY KEY, num integer, data varchar);") for i in range(1,50): x = x+1 print x dict_cur.execute("INSERT INTO test (num, data) VALUES(%s, %s)",(x, 3.6))#"abc'def")) ### how to I create the table and insert value using the dictreader? dict_cur.execute("SELECT * FROM test") for k in range(0,x+1): rec = dict_cur.fetchone() print rec['num'], rec['data'] 

Answer 1

Let's say you have a list of field names (presumably you can get this from the header of your csv file):

 fieldnames = ['Name', 'Address', 'City', 'State'] 

Assuming all of them are VARCHAR, you can create a table called "TableName":

 sql_table = 'CREATE TABLE TableName (%s)' % ','.join('%s VARCHAR(50)' % name for name in fieldnames) cursor.execute(sql_table) 

You can insert lines from the dict dictionary:

 sql_insert = ('INSERT INTO TableName (%s) VALUES (%s)' % (','.join('%s' % name for name in fieldnames), ','.join('%%(%s)s' % name for name in fieldnames))) cursor.execute(sql_insert, dict) 

Or do it at a time, given the list of dictionaries:

 dictlist = [dict1, dict2, ...] cursor.executemany(sql_insert, dictlist) 

You can adapt this as needed based on the type of your fields and the use of DictReader.

Answer 2

I'm a newbie, but it worked for me. I used PG Admin to create the testCSV table.

 import psycopg2 as dbapi con = dbapi.connect(database="testpg", user="postgres", password="secret") cur = con.cursor() import csv csvObject = csv.reader(open(r'C:\testcsv.csv', 'r'), dialect = 'excel', delimiter = ',') passData = "INSERT INTO testCSV (param1, param2, param3, param4, param5) VALUES (%s,%s,%s,%s,%s);" for row in csvObject: csvLine = row cur.execute(passData, csvLine) con.commit()