CODEWITHSUNDEEP
pythonmysqldatabase
Recourse
Recourse

Reputation: 11

Python using mysql connector list databases LIKE and then use those databases in order and run query

I'm trying to write a script using pythong and the mysql-connector library. The script should connect to the mysql server do a "SHOW DATABASES LIKE 'pdns_%' and then using the results returned by the query use each database and then run another query while using that database.

Here is the code 

import datetime
import mysql.connector
from mysql.connector import errorcode


cnx = mysql.connector.connect (user='user', password='thepassword',
                               host='mysql.server.com',buffered=True)
cursor = cnx.cursor()

query = ("show databases like 'pdns_%'")

cursor.execute(query)

databases = query

for (databases) in cursor:
    cursor.execute("USE %s",(databases[0],))
    hitcounts = ("SELECT Monthname(hitdatetime) AS 'Month', Count(hitdatetime) AS 'Hits' WHERE  hitdatetime >= Date_add(Last_day(Date_sub(Curdate(), interval 4 month)), interval 1 day) AND hitdatetime < Date_add(Last_day(Date_sub(Curdate(), interval 1 month)), interval 1 day) GROUP  BY Monthname(hitdatetime) ORDER BY Month(hitdatetime)")
    cursor.execute(hitcounts)
    print(hitcounts)


cursor.close()
cnx.close()

When running the script it stops with the following error'd output

Traceback (most recent call last):
  File "./mysql-test.py", line 18, in <module>
    cursor.execute("USE %s",(databases[0],))
  File "/usr/lib/python2.6/site-packages/mysql/connector/cursor.py", line 491, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File "/usr/lib/python2.6/site-packages/mysql/connector/connection.py", line 635, in cmd_query
    statement))
  File "/usr/lib/python2.6/site-packages/mysql/connector/connection.py", line 553, in _handle_result
    raise errors.get_exception(packet)
mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''pdns_382'' at line 1

Based on the error I'm guessing there is an issue with how its doing the datbase name from the first query. Any pointers in the correct direction would be very helpful as I'm very much a beginner. Thank you very much.

Upvotes: 1

Views: 2704

Answers (1)

Alex Martelli
Alex Martelli

Reputation: 881605

Alas, the two-args form of execute does not support "meta" parameters, such as names of databases, tables, or fields (roughly, think of identifiers you wouldn't quote if writing the query out manually). So, the failing statement:

cursor.execute("USE %s",(databases[0],))

needs to be re-coded as:

cursor.execute("USE %s" % (databases[0],))

i.e, the single arg form of execute, with a string interpolation. Fortunately, this particular case does not expose you to SQL injection risks, since you're only interpolating DB names coming right from the DB engine.

Upvotes: 2

Related Questions