如何从字典创建sqlite3表 [英] how to create a sqlite3 table from a dictionary
本文介绍了如何从字典创建sqlite3表的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
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()
有没有一种方法可以将这个 tables
嵌套的dict对象简单地转换为db表?我收到 sqlite3.OperationalError:table Players已经存在的错误
,这很明显,因为我多次调用 CREATE TABLE
.
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?
谢谢!
我如何解决:
答案在下面的评论中.
推荐答案
在这里,一次快速且可能很脏的查询全部在一个查询中.
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()
这篇关于如何从字典创建sqlite3表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文