create a temporary TABLE using a csv file

Question

In python, I would like to:

1. Obtain a filename from a user to a csv file
2. convert the csv into a temporary table
3. run user defined queries against a central database to add data to the table
4. output to a new csv for the user to use.

I can handle most of these steps there are just a few things that I don't understand.

Is this possible using a sqlite script? Would python sqlite class be able to execute that script?

conn = sqlite3.connect(central_database, check_same_thread=False)
cur = self.conn.cursor()

#user defines csv file and other params via http form -> script

cur.execute(script)
csv = cur.fetchall()

I could be thinking about this all wrong. So here is my setup:

1. python 3.6.3
2. flask/jinja2 (javascript capable)
3. sqlite 3.22.0

Answer 1

Haven't tested it, but you could try this:

conn = sqlite3.connect(central_database, check_same_thread=False)
cur = self.conn.cursor()

# Create table using first row of csv (column names)

#user defines csv file and other params via http form -> script
with open ('test.csv', 'r') as f:
    reader = csv.reader(f)
    columns = next(reader) 
    query = 'insert into MyTable({0}) values ({1})'
    query = query.format(','.join(columns), ','.join('?' * len(columns)))
    for data in reader:
        cur.execute(query, data)
    cur.commit()

cur.execute(script)
csv = cur.fetchall()

# Delete table 

Source: Writing a csv file into SQL Server database using python