0

I'm trying to create a web application where users can input their csv data that I will then return analysis to the user. CSV data files can come in various sizes and number of fields, so I have to be able to dynamically create tables in my sqlite database.

So far, I've created a very rough and hackish way of creating tables, but now I'm simply stuck at being able to insert queries into the table. I am aware of DictReader, which I have been trying to incorporate it after reading this answer. However, the INSERT query in the Python script

with open('data.csv','rb') as fin: # `with` statement available in 2.5+
    # csv.DictReader uses first line in file for column headings by default
    dr = csv.DictReader(fin) # comma is default delimiter
    to_db = [(i['col1'], i['col2']) for i in dr]

cur.executemany("INSERT INTO t (col1, col2) VALUES (?, ?);", to_db)

is based on a fixed number of fields. I can't find a way to dynamically make (col1, col2) VALUES (?, ?) work.

Here is what I've done so far to dynamically CREATE tables.

import sqlite3, csv
conn = sqlite3.connect("test.db")
cur = conn.cursor()
csv_file = open("test.csv", "rb")
csv_reader = csv.reader(csv_file)
header = csv_reader.next()

header_str = ', '.join(str(e) for e in header)
sql = "CREATE TABLE t (%s) " % header_str

cur.execute(create_SQL_str)

I used the header row to determine to make a string from the list that is header. I then made a CREATE SQL query and used string format (I'm aware that parameterized values with ? is better, but this isn't my primary concern at the moment.)

However, I don't think I can do the same type of hackish solution for INSERT query.

Is there an easy solution to this that I missed? I've been looking everywhere for a better solution, but couldn't find one.

Community
  • 1
  • 1
Nopiforyou
  • 350
  • 1
  • 7
  • 20
  • It does not follow from "I need to analyze CSV files of various constructions" that "I have to be able to dynamically create tables in a SQLite database." This sounds harder than it needs to be and potentially very insecure. [What are you _really_ trying to do?](https://meta.stackexchange.com/questions/66377/what-is-the-xy-problem) – Two-Bit Alchemist Feb 21 '15 at 02:15
  • @Two-Bit Alchemist: Well I'm trying to develop a two page static website using Flask. Users input a .csv file to get some kind of statistical analysis in the front page. No user registration, login. Once user inputs data file, user is redirected to a results page that will then receive a PDF copy of said analysis. Between pages 1 and 2, I wanted to store the data in a database because I thought that would make it easier to do. The controller in the 2nd page will then be able to identify the proper user and data from the DB. That's the big picture anyways. – Nopiforyou Feb 21 '15 at 03:57
  • Flask will keep up with the user and data using the request even if the user is anonymous, and if you even wanted to store it in a DB (not necessary with 2 views and no permanent state) you shouldn't do it using dynamic tables. – Two-Bit Alchemist Feb 21 '15 at 15:36
  • I would recommend NoSQL in this case(CouchDB or MongoDB). No more table creation using them. – chfw Feb 21 '15 at 20:23

0 Answers0