How to insert and call by row and column into sqlite3 python
- by user291071
Lets say i have a simple array of x rows and y columns with corresponding values,
What is the best method to do 3 things?
How to insert, update a value at a specific row column? How to select a value for each row and column,
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?
