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

Question

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.

You might consider removing the python-tag. You have an SQL-syntax problem. — Patrick B.
– Patrick B., Commented Feb 28, 2013 at 22:28

Patrick B. · Accepted Answer · 2013-02-28 22:27:00Z

2

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

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

.

answered Feb 28, 2013 at 22:27

Patrick B.

12.6k9 gold badges64 silver badges108 bronze badges

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

Comments

Tritlo · Accepted Answer · 2013-02-28 22:59:12Z

1

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.

edited Feb 28, 2013 at 22:59

answered Feb 28, 2013 at 22:31

Tritlo

5494 silver badges8 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related