CODEWITHSUNDEEP
pythonsqlsqlitecsv
Håvard
Håvard

Reputation: 97

How to use filenames from csv-files as table names in sqlite?

I'm trying to import eight csv-files as separate tables into a sqlite database using python. The name of the database table should be the same as the name of the csv-file (minus the .csv-extension). How do I create tables with the same name as the csv-files and how do I insert data in the right database table?

Here is my attempt at coding this. Import statements, the UnicodeDictReader function and the connection to the database is omitted.

filmappe = '../csv'
for csvfile in os.listdir(filmappe):
    with open(os.path.join(filmappe, csvfile), 'rb') as fil:
        spamreader = UnicodeDictReader(fil)
        t = (csvfile[:-4],)
        to_db = [(i['fnr'], i['kjonn'], i['landbakgrunn'], i['alder'], i['arbkomm'], i['naring']) for i in spamreader]
        cur.execute("CREATE TABLE IF NOT EXISTS ? (fnr, kjonn, landbakgrunn, alder, arbkomm, naring);", t )
        cur.executemany("INSERT OR IGNORE INTO ? (fnr, kjonn, landbakgrunn, alder, arbkomm, naring) VALUES (?,?,?,?,?,?);",t, to_db)

Upvotes: 0

Views: 1016

Answers (1)

Martijn Pieters
Martijn Pieters

Reputation: 1123710

You cannot use a tablename as a query parameter; that is one of the points of using SQL parameters, prevent that data is interpreted as an object name.

You'll have to use string interpolation here instead:

cur.execute("""
    CREATE TABLE IF NOT EXISTS "{}"
    (fnr, kjonn, landbakgrunn, alder, arbkomm, naring)
    """.format(t))
cur.executemany("""
    INSERT OR IGNORE INTO "{}" (fnr, kjonn, landbakgrunn, alder, arbkomm, naring)
    VALUES (?,?,?,?,?,?)
    """.format(t), to_db)

I added " quotes around the table name to make sure that names that are also keywords can still be used.

Do not do this with user-provided data; if you cannot trust the filenames, you are wide open to SQL injection attacks.

Upvotes: 2

Related Questions