CODEWITHSUNDEEP
pythonsqlsqlite
Joe
Joe

Reputation: 1388

Python & Sqlite: Can't execute query due to a syntax error

I have a sqlite database filled with stuff, and I am currently writing code to search for and extract the data I want. Here's where I slammed into trouble:

conn = sqlite3.connect("ensembl.db")
cur = conn.cursor()
...
cur.execute('SELECT b.chr,(b.start-e.start) as StartD, (b.end-e.end) as EndD,b.tcon,b.tname,b.gname FROM ensembl e blast b WHERE b.tcon=? AND b.tname=e.tname AND b.gname=e.gname AND b.chr=e.chr',tcon)
        print cur.fetchone()

This returns the error:

File "data.py", line 12, in <module>
    cur.execute('SELECT b.chr,(b.start-e.start) as StartD, (b.end-e.end) as EndD,b.tcon,b.tname,b.gname FROM ensembl e blast b WHERE b.tcon=? AND b.tname=e.tname AND b.gname=e.gname AND b.chr=e.chr',tcon)
sqlite3.OperationalError: near "blast": syntax error

I don't know what this syntax error is that python is referring to -- I've done queries similar (granted, not quite as complicated) to this before in sqlite&python and they have worked. I have tried various other ways but none seemed to work... am I missing something really simple?

Also, another question -- what would be the best way to individually extract the columns from the results once I get this working? I have used cur.fetchone() before and assigned it to a single variable for queries returning just one thing, but not sure if it will work for queries returning multiple things.

Upvotes: 1

Views: 867

Answers (2)

Tritlo
Tritlo

Reputation: 549

To extract the colum you could map a lambda function to a list of lists i.e. map( lamda x: x[0], listoflist) for the first column, map(lambda x: x[1], listoflists) for the second etc.

you could even create a lambda function that does this, i.e.

extractor = lambda i,l : map(lambda x: x[i],l)

and use

extractor(i,listofLists)

to get a list of the i-th column.

Upvotes: 1

Patrick B.
Patrick B.

Reputation: 12333

You need comma-separate the tables in the FROM-section:

[..] FROM ensembl e, blast b [..]

.

Upvotes: 2

Related Questions