New Table with Fields from Dictionary?

Question

How do I create a table in sqlite3 with dictionary items as fields?

I can't simply define each and every field manually, since I have a lot of dictionaries of various sizes. Is there a way to accomplish this?

Here's what I've come up so far:

import sqlite3

DICT = {   'field 1': 'TEXT',
           'field 2': 'TEXT',
           'field 3': 'INT',
           'field 4': 'TEXT'
           'field x': '???'
            }

def create_table(DICT):

    with sqlite3.connect("data.db") as connection:
        cursor = connection.cursor()

        # should instead iterate over the dictionary and create a field for each entry
        cursor.execute("""CREATE TABLE table_name
                        (dict_key_x, DICT_VALUE_X)""")

        connection.close()

Any ideas are welcome. Thanks! :)

Isn't this just string interpolation? – Daniel Roseman Jul 20 '15 at 19:05 — Daniel Roseman, Jul 20 '15 at 19:05

Adam Smith · Accepted Answer · 2015-07-21T15:24:09.720

2

You could certainly do exactly this by building a list of strings and using ',\n' .join

DICT = {'field 1': 'TEXT',
        'field 2': 'TEXT',
        'field 3': 'INT',
        'field 4': 'TEXT'
        'field x': '???'}

columns = "(" + ",\n".join(["{} {}".format(k,v) for k,v in DICT.items()]) + ")"
# looks like:
# # """(field 1 TEXT,
# # field 2 TEXT,
# # field 3 INT,
# # field 4 TEXT,
# # field x ???)"""

with sqlite.connect('data.db') as connection:
    cursor = connections.cursor()
    cursor.execute("CREATE TABLE table_name\n" + columns)
# you don't have to close the connection if you're using a with statement

edited Jul 21 '15 at 15:24

answered Jul 20 '15 at 19:29

Adam Smith

52,157
12
73
112

Thank you! It worked after I changed your columns formatting to 'columns = "("+", ".join("{} {}".format(k,v) for k,v in DICT.items())+")"'. This produces the following sqlite3 command: '"CREATE TABLE table_name (field 1 TEXT, field 2 TEXT, etc)"' Your suggestion returned an error, because the formatting was like this: '"CREATE TABLE table_name (field 1, TEXT), (field 2, TEXT), etc)"' – St4rb0y Jul 21 '15 at 08:44
@St4rb0y edited to reflect those changes. It's been beyond forever since I had to build raw SQL queries by hand -- I didn't remember the syntax! sqlalchemy is your friend :D – Adam Smith Jul 21 '15 at 15:24

New Table with Fields from Dictionary?

1 Answers1