Python, SQlite and string substitution

Question

I got a question, maybe it answered somewhere but I can't find answer. So I got Sqlite db with one table consist, let say of phone models and manufacturers:

Samsung
Apple
Iphone
Apple
Galaxy
Samsung
Ipod
Nexus

so what I trying to achieve is make a script to substitute:

Iphone or Ipod -> Apple
Galaxy or Nexus -> Samsung

I would like to have an data structure like dictionary (I know that example is impossible, but only for illustration) that I can read from file to make an UPDATE query:

{
'Apple':'Iphone','Ipod'
'Samsung':'Galaxy','Nexus'}

so when script find any of values it substitutes it with key

values can be quite an few - let say about 10 so using if/or statements will be unpractical and I don't everytime I need change something go to the code and correct it - so that's why I want to keep my "dictionary" in text file and read it from an script.

I will be appreciated for any ideas that point me in right direction

Thank You.

Answer 1

First, you can make a dict that maps replacement values to lists of 'original values':

replacements = {
    'Apple':['Iphone','Ipod'],
    'Samsung':['Galaxy','Nexus']
}

You can just put that into a file like mapping.py and do from mapping import replacements. JSON would be a reasonable serialization format, too. Once you get the dictionary, you can iterate over all fields, generate a parametrized query fitting the length of strings to replace.

for replacement, replacables in replacements.iteritems():
    query = 'update foo set value=? where value in ({})'.format(",".join("?"*len(replacables)))
    c.execute(query, [replacement]+replacables)

This way you don't get SQL injections. When I tried, it worked up to 100 variables, didn't with 1000. I haven't checked how far exactly it works.

Answer 2

Beware: The following is not safe agains SQL injection!

The database before:

$ sqlite3 foo.db .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE t (f varchar);
INSERT INTO "t" VALUES('foo');
INSERT INTO "t" VALUES('bar');
INSERT INTO "t" VALUES('baz');
COMMIT;

The JSON file for the mapping:

{"B": ["bar", "baz"], "F": ["foo"]}

The Python code:

import json
import sqlite3

d = json.loads("d.json")

con = sqlite3.connect("foo.db")
cur = con.cursor()

# Loop over the keys in d.
for k in d:
    # Build the SQL clause that matches all recordss for d[k].
    clause = " or ".join(" f = '{}' ".format(v) for v in d[k])
    # The SQL for the update.
    sql = "update t set f = '{}' where {}".format(k, clause)    
    cur.execute(sql)

con.commit()        

The database after:

$ sqlite3 foo.db .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE t (f varchar);
INSERT INTO "t" VALUES('F');
INSERT INTO "t" VALUES('B');
INSERT INTO "t" VALUES('B');
COMMIT;

Answer 3

If you have to do a lot of substitution in one go i would suggest to use a temp table with columns old and new

so your dictionary will look like

Iphone, Apple

Ipad,Apple

Nexus,Samsung

Galaxy,Samsung

You can upload this dictionary to the temp table using a bulk loader

Bulk load data into sqlite?

And then run a single update command to update all the old values in one go

the gist of the SQL in Oracle will be

update yourtable
set yourtable.phone = (select temp.new
from yourtable , temp
where 
temp.old = yourtable.phone)
where exists
(
select 1 from yourtable , temp
where 
temp.old = yourtable.phone
)