current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 4 down vote favorite

I'm working with a remote db for importing data to my Django proyect's db.

With the help of MySQLdb I've managed with ease to create an importing function like the following:

def connect_and_get_data(useful_string):
    CONNECTION = MySQLdb.connect(host=..., port=...,
                                 user=..., passwd=..., db=...,
                                 cursorclass=MySQLdb.cursors.DictCursor,
                                 charset = "utf8")
    cursor = CONNECTION.cursor()
    cursor.execute("SELECT ... FROM ... WHERE ... AND some_field=%s", (useful_string))
    result = cursor.fetchall()
    cursor.close()

Very happy with that, works as expected.

But going on with the code, I've noticed that sometimes I'll need to connect to the db again, in order to execute other different queries.

The first idea was quite logical, to me: for every query I'll need, define a function which calls connect_and_get_data with the given query as parameter... something like this:

def get_data_about_first_amazing_topic(useful_string):
    query = "SELECT ... FROM ... WHERE ... AND some_field=%s" %(useful_string)
    connect_and_get_data(query)
    ...

def get_data_about_second_amazing_topic(other_useful_string):
    query = "SELECT ... FROM ... WHERE ... AND some_field=%s" %(other_useful_string)
    connect_and_get_data(query)
    ...

with this modifications to connect_and_get_data:

def connect_and_get_data(query):
    ...
    cursor.execute(query)
    ...

As you already might imagine, this solutions fails.

Reading mluebke's answer to the question python mysql fetch query

"You are passing arguments to the execute function, not doing python string substitution"

I understood immediately where I was wrong; but I still feel that something is missing: I've tried different solutions, but I'm definitely unhappy with all of them.

Is there a "good" way to encapsulate my connect_and_get_data(query) function, in order to serve me the way I want, or I'm totally in the wrong path with this?

Which ones are considered "best practices" in this situation?

share|improve this question
add comment

1 Answer

up vote 3 down vote accepted

I think this is what you're looking for.

def connect_and_get_data(query, data):
    ...
    cursor.execute(query, data)
    ...

def get_data_about_first_amazing_topic(useful_string):
    query = "SELECT ... FROM ... WHERE ... AND some_field=%s"
    connect_and_get_data(query, ("one","two","three"))
    ...

But, if you're going to be making several queries quickly, it would be better to reuse your connection, since making too many connections can waste time.

...
CONNECTION = MySQLdb.connect(host=..., port=...,
                             user=..., passwd=..., db=...,
                             cursorclass=MySQLdb.cursors.DictCursor,
                             charset = "utf8")
cursor = CONNECTION.cursor()
cursor.execute("SELECT ... FROM ... WHERE ... AND some_field=%s", ("first", "amazing", "topic"))
first_result = cursor.fetchall()

cursor.execute("SELECT ... FROM ... WHERE ... AND some_field=%s", (("first", "amazing", "topic")))
second_result = cursor.fetchall()

cursor.close()
...

This will make your code perform much better.

share|improve this answer
    
Yes... You're right! I'm going towards this direction... But what if I'll need two useful_strings in my query? that would transform data an args array... right? and how will react cursor.execute while receiving an array of strings? mmm... –  dolma33 Nov 11 '11 at 17:58
1  
You can use a tuple (a sequence similar to an array) as the 2nd parameter to connect_and_get_data. cursor.execute accepts tuples as the second parameter. Tuples are just a sequence of elements surrounded by parentheses, e.g. ("one","two","three"). In fact, if you look at your code, you can see (useful_string) in parentheses as the argument to execute(). –  Kylos Nov 11 '11 at 18:19
    
Can't reuse my connection for many queries: unfortunately, the remote db lies on a machine with a really slooow (less than 10k upload) and super-unstable connection (working on an island in the middle of the pacific has his cons, and one of them is slow satellite connection)... so I must have short and light "atomic" db connections... –  dolma33 Nov 11 '11 at 20:33
    
BTW, I like your solution (1st piece of code, because 2nd doesn't apply). I don't know why I've blocked myself in front of that tuple-argument thing... It's true that sometimes we are simply too submerged in our code that we can't see obvious solutions lying in front of our noses... Thank you ;) –  dolma33 Nov 11 '11 at 21:14
    
"funny" note: in the first version, the one described in my original code, the result returned from execute() is a list of dictionaries. in the modified version, the one with encapsulated function, the result returned from execute() is a tuple of dictionaries... any idea about the reason? –  dolma33 Nov 11 '11 at 21:54
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.