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

Did you try with some other query, a simple select or something trivial? — luistm
– luistm, Commented Dec 20, 2019 at 7:39
@luistm, for simple sql string it works on both connection and engine version — dingx
– dingx, Commented Dec 20, 2019 at 12:51

Ilja Everilä · Accepted Answer · 2019-12-20 09:29:52Z

6

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

edited Dec 20, 2019 at 9:29

answered Dec 20, 2019 at 8:00

Ilja Everilä

53.4k9 gold badges138 silver badges142 bronze badges

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

2 Comments

dingx Over a year ago

thank, after wrap the raw string into a sqlalchemy.text() object, it's OK to use the engine version, but failed on the connection version; why this difference? and when should I use engine and when to use connection? from pandas.read_sql_query() doc it accepts both engine and connect.

Ilja Everilä Over a year ago

text() is an SQLAlchemy construct, so you'd use it when you're using SQLAlchemy (engine). It will not work with a raw DB-API connection. And while text() escapes % even if you format the value directly to the raw SQL string manually, don't do that. Use placeholders.

Aliakbar Hosseinzadeh · Accepted Answer · 2020-04-29 07:00:40Z

3

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%%')

answered Apr 29, 2020 at 7:00

Aliakbar Hosseinzadeh

5456 silver badges5 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related