How to insert and call by row and column into sqlite3 python

Question

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?

I'm surprised this hasn't been downvoted for adding "great tutorial problem" to the question. — mikerobi
– mikerobi, Commented Apr 23, 2010 at 3:25

Ian · Accepted Answer · 2012-06-04 16:25:38Z

2

So you have a dictionary of dictionaries, that you want to convert into a SQL table.

Steps I'd take

Find the columns you'll need.
Create the table schema.
Loop through each row.
1. Compile the set of values for each column.
2. Insert it.

So:

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)

Then your other questions are pretty simple:

## 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]

edited Jun 4, 2012 at 16:25

answered Apr 23, 2010 at 4:56

Ian

3,7091 gold badge24 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

user291071 Over a year ago

thanks alot for your detailed answer, nice to see great code!

Ian Over a year ago

Cool, thanks. If it was useful, would you mind accepting my answer?

user291071 Over a year ago

Oh ya didn't see it, i tried upvoting but don't have the rep for it yet, lol, one other question I am grappling with, should i put this as another answer? If i change the column values in the dict to lets say 'y3' to 'some random string' I can't get the code to work. I tried changing several things instead of using real I used text, but when I call it says the column doesn't exist any thoughts?

user291071 Over a year ago

never mind :), just seen the sql = 'SELECT %s needs to be "%s" for a string, pesky sql and its touchy syntax lol

ismail Over a year ago

also note, this solution should not be used when you cant trust the data. check pydocs for sqlinjection

|

Collectives™ on Stack Overflow

How to insert and call by row and column into sqlite3 python

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related