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-11T18:38:28.593

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 '11 at 18:38

answered Nov 11 '11 at 17:49

Kylos

1,888
14
24

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
In your first example, the second parameter is a tuple. Perhaps you can check to make sure that the parameter is not a bare string (or maybe a tuple wrapped in a 1-tuple `(("one", "two", "three"))`). These are just some things you could check. – Kylos Nov 14 '11 at 17:13

score 0 · Answer 2 · edited Jan 05 '20 at 09:04

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 05 '20 at 09:04

Roberto Caboni

7,252
10
25
39

answered Jan 05 '20 at 07:01

Joe Bek

31
1
6

Executing different queries using mysql-python

2 Answers2

Linked