create table using python objects

Question

I'm using sqlite3 in a Python script to extract data from a client's spreadsheet. My client is planning to add on to the spreadsheet, so my sqlite code should generate its columns based on the headers I extract from the first line. How do I do this? This is my naive attempt:

import sqlite3
conn = sqlite3.connect('./foo.sql')
c = conn.cursor()
for line in file:
 if line[0] == 'firstline':
 # Below is the line in question
 c.execute(""" create table if not exists bar(?, ? ,?); """, lineTuple)
 else:
 c.execute(""" insert into bar values (?, ?, ?); """, lineTuple)

• Owen Pierce: I am aware of the distinction.
• Mike DeSimone: Making a database look like a spreadsheet for import purposes is the first step toward getting people off the spreadsheet. Also, for the record, having a script routinely call create table creeps me out because then, in this case, you spend your time debugging the trash (spaces, special characters, unicode, newlines, etc.) the users put into the column headings.
• Peter Graham: Using headers from the spreadsheet is a bad idea. You'll have to convert them to a valid sql identifier in a consistent way. Generate column names like 'c1', 'c2' etc. instead and keep a mapping of spreadsheet header -> sql column names somewhere.

Answer 1

I think, csv module of python can help you to extract file data.

First, convert your spreadsheet in csv format (save as csv command) with appropriate delimiter.

then, try below code snippet:

import csv
file_ptr = open('filename.csv','r');
fields = range(0, total number of columns in file header)
file_data = csv.DictReader(file_ptr, fields, delimiter=',')
for data in file_data:
 print data
 # data will be in dict format and first line would be all your headers,else are column data
 # here, database query and code processing 

Hope, it will be helpful.