Executing different queries using mysql-python

Question

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?

Kylos · Accepted Answer · 2011-11-11 18:38:28Z

7

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.

edited Nov 11, 2011 at 18:38

answered Nov 11, 2011 at 17:49

Kylos

1,94814 silver badges25 bronze badges

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

6 Comments

dolma33 Over a year ago

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...

Kylos Over a year ago

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().

dolma33 Over a year ago

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 Over a year ago

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 Over a year ago

"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?

|

Roberto Caboni · Accepted Answer · 2020-01-05 09:04:46Z

0

I am doing a web application project with Python and MYSQL, and I had the same error type :

MySQLdb._exceptions.OperationalError: (1045, "Access denied for user 'root'@'localhost' (using password: YES)").

All I did is changing the app configuration password to empty string "" as follows:

app.config['MYSQL_PASSWORD'] = ""

And then I succeeded in logging in.

edited Jan 5, 2020 at 9:04

Roberto Caboni

7,51010 gold badges29 silver badges42 bronze badges

answered Jan 5, 2020 at 7:01

Joe Bek

311 silver badge6 bronze badges

Collectives™ on Stack Overflow

Executing different queries using mysql-python

2 Answers 2

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related