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)

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. — Mike DeSimone
– Mike DeSimone, Commented Sep 20, 2011 at 4:46
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. — Peter Graham
– Peter Graham, Commented Sep 20, 2011 at 5:09

Yajushi · Accepted Answer · 2011-09-20 07:19:21Z

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.

answered Sep 20, 2011 at 7:19

Yajushi

1,1952 gold badges10 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

create table using python objects

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related