sqlite结合python_如何在sqlite3python中插入和按列调用

所以你有一个字典，你想把它转换成一个SQL表。在

我要采取的步骤找到您需要的列。在

创建表架构。在

遍历每一行。

为每列编译一组值。在

插入。在

所以：import sqlite3

con = sqlite3.connect('simple.db')

c = con.cursor()

dic = {

'x1':{'y1':1.0,'y2':0.0},

'x2':{'y1':0.0,'y2':2.0,'y3':1.5},

'x3':{'y2':2.0,'y3':1.5}

}

# 1. Find the unique column names.

columns = set()

for cols in dic.values():

for key in cols:

columns.add(key)

# 2. Create the schema.

col_defs = [

# Start with the column for our key name

'"row_name" VARCHAR(2) NOT NULL PRIMARY KEY'

]

for column in columns:

col_defs.append('"%s" REAL NULL' % column)

schema = "CREATE TABLE simple (%s);" % ",".join(col_defs)

c.execute(schema)

# 3. Loop through each row

for row_name, cols in dic.items():

# Compile the data we have for this row.

col_names = cols.keys()

col_values = [str(val) for val in cols.values()]

# Insert it.

sql = 'INSERT INTO simple ("row_name", "%s") VALUES ("%s", "%s");' % (

'","'.join(col_names),

row_name,

'","'.join(col_values)

)

c.execute(sql)

你的其他问题也很简单：

^{pr2}$