(MySQLdb._exceptions.ProgrammingError) not enough arguments for format string

Question

I'm using following query to read data from a mysql db:
setup:

conn = MySQLdb.connect(host='127.0.0.1', port=3306, user='**', passwd='**', db='***')
engine = create_engine('mysql+mysqldb://***')

sql = 'show tables like "{}"'.format('aTable_' + '%')

option-1: this is OK

a1 = pd.read_sql_query(sql, conn)

option-2: this will throw errors:
ProgrammingError: (MySQLdb.exceptions.ProgrammingError) not enough arguments for format string [SQL: show tables like "aTable%" ] (Background on this error at: http://sqlalche.me/e/f405)

a1 = pd.read_sql_query(sql, engine)

what should I do if i want to use the second style(engine as the param)?

Answer 1

Instead of passing values to SQL using string formatting, use placeholders:

from sqlalchemy import text
engine = create_engine('mysql+mysqldb://***')
sql = text('SHOW TABLES LIKE :pattern')
a1 = pd.read_sql_query(sql, engine, params={'pattern': 'aTable_%'})

Using text() allows you to use the named paramstyle regardless of your DB-API driver in use. Placeholders ensure that you, or anyone else, don't accidentally inject unintended SQL to your query, for example by passing a string containing quotes as a value.

In this case the problem stems from the % character. If the MySQLdb DB-API driver's execute() method is called without arguments, it will not attempt to use placeholders in the query. On the other hand it looks like SQLAlchemy is passing execute() an empty argument container, which triggers the code path that attempts to use the placeholders in the query string, which in the end—after converting the arguments to their SQL literal form—is done using %-formatting, and so the error "not enough arguments for format string":

In [4]: 'show tables like "aTable_%"' % ()
---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
<ipython-input-4-4f31bc6a7bb6> in <module>()
----> 1 'show tables like "aTable_%"' % ()

TypeError: not enough arguments for format string

Answer 2

I had the same issue on panda read_sql_query and faced below error:

ProgrammingError: (MySQLdb._exceptions.ProgrammingError) not enough arguments for format string[SQL: SELECT * FROM `alarms` 
                    WHERE 
                    (`Supplementary` NOT LIKE '%door%') 
                    AND 
                    ( `Supplementary` LIKE '%main%') 
                     )]

(Background on this error at: http://sqlalche.me/e/f405)

It solved the issue with a double % sign as below:

(`Supplementary` NOT LIKE '%%door%%') AND ( `Supplementary` LIKE '%%main%%')