Joe Joe - 2 months ago 13
Python Question

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.

Answer Source

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

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

.