Valid update statement not working in sqlite3

Question

I am trying to use the fields of one table, chr_map in this case, to cast the values of a column chr in other tables between the fields of chr_map. That isn't terribly clear, but the goal is to use chr_map to facilitate translating between the many different chromosome naming conventions that exist.

Here is a toy example with 1 table other than chr_map:

BEGIN TRANSACTION;
DROP TABLE IF EXISTS "chr_map";
CREATE TABLE IF NOT EXISTS "chr_map" (
    "id"    INTEGER,
    "refseq"    ,
    "igenomes"  ,
    "ensembl"   ,
    "ucsc"  ,
    "mitra" ,
    PRIMARY KEY("id" AUTOINCREMENT)
);
DROP TABLE IF EXISTS "regions_test";
CREATE TABLE IF NOT EXISTS "regions_test" (
    "id"    INTEGER,
    "chr"   ,
    "start" ,
    "end"   ,
    "name"  ,
    "value" ,
    "strand"    ,
    PRIMARY KEY("id" AUTOINCREMENT)
);
INSERT INTO "chr_map" ("id","refseq","igenomes","ensembl","ucsc","mitra") VALUES (1,'NC_001133.9','I','I','chrI','NC_001133'),
 (2,'NC_001134.8','II','II','chrII','NC_001134'),
 (3,'NC_001135.5','III','III','chrIII','NC_001135'),
 (4,'NC_001136.10','IV','IV','chrIV','NC_001136'),
 (5,'NC_001137.3','V','V','chrV','NC_001137'),
 (6,'NC_001138.5','VI','VI','chrVI','NC_001138'),
 (7,'NC_001139.9','VII','VII','chrVII','NC_001139'),
 (8,'NC_001140.6','VIII','VIII','chrVIII','NC_001140'),
 (9,'NC_001141.2','IX','IX','chrIX','NC_001141'),
 (10,'NC_001142.9','X','X','chrX','NC_001142'),
 (11,'NC_001143.9','XI','XI','chrXI','NC_001143'),
 (12,'NC_001144.5','XII','XII','chrXII','NC_001144'),
 (13,'NC_001145.3','XIII','XIII','chrXIII','NC_001145'),
 (14,'NC_001146.8','XIV','XIV','chrXIV','NC_001146'),
 (15,'NC_001147.6','XV','XV','chrXV','NC_001147'),
 (16,'NC_001148.4','XVI','XVI','chrXVI','NC_001148'),
 (17,'NC_001224.1','MT','Mito','chrM','NC_001224'),
 (18,'RDM_2','RDM_2','RDM_2','RDM_2','RDM_2'),
 (19,'RDM_3','RDM_3','RDM_3','RDM_3','RDM_3'),
 (20,'RDM_5','RDM_5','RDM_5','RDM_5','RDM_5');
INSERT INTO "regions_test" ("id","chr","start","end","name","value","strand") VALUES (1,'NC_001133.9',11951,12651,'.',0,'-'),
 (2,'NC_001133.9',11567,12267,'.',0,'-'),
 (3,'NC_001133.9',11345,12045,'.',0,'+'),
 (4,'NC_001133.9',11723,12423,'.',0,'+');
DROP INDEX IF EXISTS "chr_map_index";
CREATE INDEX IF NOT EXISTS "chr_map_index" ON "chr_map" (
    "chrom",
    "start" ASC,
    "end"   ASC,
    "strand"
);
DROP INDEX IF EXISTS "regions_test_index";
CREATE INDEX IF NOT EXISTS "regions_test_index" ON "regions_test" (
    "chrom",
    "start" ASC,
    "end"   ASC,
    "strand"
);
COMMIT;

I want to run a command like so:

UPDATE regions_test 
SET chr = r.ucsc 
FROM (SELECT refseq,ucsc FROM chr_map) AS r 
WHERE regions_test.chr = r.refseq;

This works in the DB browser for sqlite:

Execution finished without errors.
Result: query executed successfully. Took 0ms, 4 rows affected
At line 1:
UPDATE regions_test SET chr = r.ucsc FROM (SELECT refseq,ucsc FROM chr_map) AS r WHERE regions_test.chr = r.refseq;

But using python sqlite3:

sql = swap_chr_format_sql % (table, 
                             self.standard_chr_format, 
                             curr_chrom_format, 
                             self.standard_chr_format,
                             table, curr_chrom_format)
print(sql)

output:

'UPDATE regions_test SET chr = r.ucsc FROM (SELECT refseq,ucsc FROM chr_map) AS r WHERE regions_test.chr = r.refseq;'

When I try to execute

con.execute(sql)

I get the following error:

Traceback (most recent call last):
  File "", line 1, in 
sqlite3.OperationalError: near "FROM": syntax error

The command looks right in form, so I suspect I am misunderstanding something about string formatting. Any help will be appreciated.

Valid update statement not working in sqlite3

0 Answers