Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
665 views
in Technique[技术] by (71.8m points)

python - how to create a sqlite3 table from a dictionary

import sqlite3

db_file = 'data/raw/db.sqlite'
tables = {
    'Players': {
        'id': 'INTEGER PRIMARY KEY',
        'fname': 'TEXT',
        'lname': 'TEXT',
        'dob': 'DATETIME',
        'age': 'INTEGER',
        'height': 'INTEGER', # inches
        'weight': 'INTEGER', # pounds
        'rank': 'INTEGER',
        'rhlh': 'INTEGER', # 0 - right, 1 - left
        'bh': 'INTEGER', # 0 - onehand, 1 - twohand
        'city': 'TEXT', # birth city
        'county': 'TEXT' #birth country
        }
}


conn = sqlite3.connect(db_file)
c = conn.cursor()

for table in tables.keys():
    for cols in tables[table].keys():
        c.execute("CREATE TABLE {} ( 
                        {} {})".format(table, cols, tables[table][cols]))

c.close()
conn.close()

Is there a way to simply turn this tables nested dict object into a db table? The error I am getting sqlite3.OperationalError: table Players already exists which is obvious because I am calling CREATE TABLE more than once.

Does anyone have a quick trick in making a DB like so, using a nested dictionary which will eventually contain multiple tables? Is this a terrible pracitce? What should I do differently?

Thank you!


HOW I SOLVED:

Answer is below in comments.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

Here, quick and probably dirty one, all in one query.

import sqlite3

db_file = 'data/raw/db.sqlite'
tables = {
    'Players': {
        'id': 'INTEGER PRIMARY KEY',
        'fname': 'TEXT',
        'lname': 'TEXT',
        'dob': 'DATETIME',
        'age': 'INTEGER',
        'height': 'INTEGER', # inches
        'weight': 'INTEGER', # pounds
        'rank': 'INTEGER',
        'rhlh': 'INTEGER', # 0 - right, 1 - left
        'bh': 'INTEGER', # 0 - onehand, 1 - twohand
        'city': 'TEXT', # birth city
        'county': 'TEXT' #birth country
        }
}


conn = sqlite3.connect(db_file)
c = conn.cursor()

for table in tables.keys():
    fieldset = []
    for col, definition in tables[table].items():
        fieldset.append("'{0}' {1}".format(col, definition))

    if len(fieldset) > 0:
        query = "CREATE TABLE IF NOT EXISTS {0} ({1})".format(table, ", ".join(fieldset))

        c.execute(query)

c.close()
conn.close()

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...