python - 如何在sqlite3 python中按行和列插入和调用

Question

假设我有一个简单的 x 行和 y 列数组以及相应的值，做三件事的最佳方法是什么？如何在特定行列插入、更新值？如何为每行和每列选择一个值，

import sqlite3
con = sqlite3.connect('simple.db')
c = con.cursor()
c.execute('''create table simple (links text)''')
con.commit()

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}}
ucols = {}
## my current thoughts are collect all row values and all column values from dic and populate table row and columns accordingly how to call by row and column i havn't figured out yet
##populate rows in first column
for row in dic:
    print row
    c.execute("""insert into simple ('links') values ('%s')"""%row)
con.commit()

##unique columns
for row in dic:
    print row
    for col in dic[row]:
        print col
        ucols[col]=dic[row][col]

##populate columns    
for col in ucols:
    print col
    c.execute("alter table simple add column '%s' 'float'" % col)
con.commit()

#functions needed
##insert values into sql by row x and column y?how to do this  e.g. x1 and y2 should put in 0.0
##I tried as follows didn't work
for row in dic:
    for col in dic[row]:
        val =dic[row][col]
        c.execute("""update simple SET '%s' = '%f' WHERE 'links'='%s'"""%(col,val,row))
con.commit()

##update value at a specific row x and column y?


## select a value at a specific row x and column y?

Answer 1

因此，您有一个字典的字典，您希望将其转换为 SQL 表。

我会采取的步骤

1. 找到您需要的列。
2. 创建表架构。
3. 循环每一行。
   1. 编译每列的值集。
   2. 插入它。

所以:

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)

那么你的其他问题就很简单了:

## update value at a specific row x and column y?
def set_cell(connection, x_name, y_name, value):
    sql = 'UPDATE simple SET %s="%s" WHERE row_name="%s"' % (
        y_name, value, x_name
        )
    connection.execute(sql)

## select a value at a specific row x and column y?
def get_cell(connection, x_name, y_name):
    sql = 'SELECT %s FROM simple WHERE row_name="%s"' % (
        y_name, x_name
        )
    # Return the first row of results (there should be only one)
    # and the first column from that row
    return list(connection.execute(sql))[0][0]