compare a string in sql database 1

[Annie12345]

Hi, I have a script to search for strings in a file and look them up in a database, however sql won't accept my string (for example ENSG00000152822), how should I do it?

import MySQLdb
db = MySQLdb.connect(host = "ensembldb.sanger.ac.uk",user = "anonymous", passwd = "", db = "ensembl_mart_23_1")
cursor = db.cursor()

for line in open('list.txt').readlines():
array = line.split("\t")
ensemblid = array[1]
cursor.execute("select gene_stable_id, display_id from hsapiens_gene_est__gene__main where gene_stable_id= ???ensemblid??? ")

print cursor.fetchall()

Thanks for you help

 

[ericbrunson]

WTF is "???ensemblid???" ?

 

[ericbrunson]

Oh, I see, you were just hilighting the ensemblid

You need to create a string that is a valid SQL statement. Try:

cursor.execute("select gene_stable_id, display_id from hsapiens_gene_est__gene__main where gene_stable_id= '%s'" % ensemblid )

 

[Annie12345]

That works
This works too:
cursor.execute("select gene_stable_id, display_id from hsapiens_gene_est__gene__main where gene_stable_id = '" + ensemblid + "'")

Tnx

 

[ericbrunson]

Get used to using the '%' to construct strings. It's *way* powerful and in some cases will Do The Right Thing(tm) coercing the variables to strings where '+'won't.

 

[Annie12345]

Can you put lists to strings as well with %? I got strings like: "A23". I've got to have the " removed. I used list to get it removed so now i have ['A','2','3'], but I don't know how to get it back as A23.

 

[ericbrunson]

the easiest way to do that would be to use:

>>> mystr = '"thing"'
>>> print mystr
"thing"
>>> print mystr[1:-1]
thing

 

[Annie12345]

That would be indeed the easiest way, but it's a for-loop (lines out of a file) and not all strings have "", and some are empty. So i have strings like this: A12, "A12", "", and empty. I put this in the for loop and now i get that list ['A','2','3'] and empty lists for the empty strings (I have to keep the empty strings in between). So if i could convert it to strings it would be perfect.

lijst = list (array[13])
try:
lijst.remove('"')
except:
pass
try:
lijst.remove('"')
except:
pass

 

[ericbrunson]

First, because it irks the hell out of me when I ask a question and I get the reply, "You don't want to do that, do this", here's the simple answer to your question...

Any string is of the python class 'String', and therefore has all the methods of String, so you can take the empty string, '', and use its join() method to concatenate list elements:

>>> str = 'ABCD'
>>> lst = list( str )
>>> print lst
['A', 'B', 'C', 'D']
>>> print ''.join( lst )
ABCD
>>> print ','.join( lst )
A,B,C,D

However, converting a string to a list is redudant, because a String is a special case of a list:

>>> for char in str:
...     print char
... 
A
B
C
D

So you can skip that step.

Also, if you look at the string methods and regexp methods you can probably find something that would be more efficient.

>>> str = '"ABCD"'
>>> print str.replace( '"', '' );
ABCD
>>> print ''.join( str.split( '"' ) )
ABCD

(Both of these examples eliminate *all* quotes, not just enclosing)

It's not just about not reinventing the wheel, but when you can replace a loop with a map() or a method, those are (usually) implemented in C and will be much faster at runtime. Probably not important for a one off script, but it's good to learn the tools available.

And, finally (will this guy ever shut up?!?), since we're talking about knowing your tools, if you're using python 2.3, it comes with a built in csv parsing module. You instantiate a reader with an iterable (in your case, open your file and pass the file handle to the reader() method, in the case of the example, I'm going to use a list with one item in it) and iterate over the elements, the default parser is pretty intelligent:

>>> import csv
>>> # you would use: iterator = open( "list.txt" )
>>> iterator = [ '1,two,3,"I think, therefore I am",5,6', ]  
>>> p = csv.reader( iterator )
>>> for thing in p:
...     print thing
... 
['1', 'two', '3', 'I think, therefore I am', '5', '6']

Notice that it properly handled the numbers, the unquoted string and the quoted string with an embedded delimiter. All in five lines of code.

Python Rocks.