1

I am trying to execute a query using SQLAlchemy

dev_engine = create_engine('mysql://...')

dev_connect = dev_engine.connect()
prod_connect = prod_engine.connect()


get_dev_instrument = "select * from market_instrument inner join cme_instrument on market_instrument.id = cme_instrument.instrument_id \
inner join tick_size on market_instrument.id = tick_size.instrument_id \
left join futures on market_instrument.id = futures.instrument_id \
left join `options`on market_instrument.id = `options`.instrument_id \
left join spreads on market_instrument.id = spreads.instrument_id \
inner join (select distinct(substring(m.symbol, 1, 2)) as product from market_instrument as m inner join fills as f on m.id=f.instrument_id \
inner join cme_instrument where symbol<>\"\" and fill_timestamp between CURDATE() - INTERVAL 60 DAY AND NOW()) r \
on market_instrument.symbol like concat(r.product, \'%\') \
where symbol not like \'%_old%\' and symbol not like \'%:%\';"

instruments = dev_engine.execute(get_dev_instrument)

I keep getting TypeError: not enough arguments for format string

Traceback (most recent call last):
  File "check_instrument_transactions-cme.py", line 122, in <module>
    main()
  File "check_instrument_transactions-cme.py", line 120, in main
    transactions(exchange, last_days, filepath)
  File "check_instrument_transactions-cme.py", line 28, in transactions
    instruments = dev_engine.execute(get_dev_instrument)
  File "/home/local/filepath/anaconda2/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1991, in execute
    return connection.execute(statement, *multiparams, **params)
  File "/home/local/filepath/anaconda2/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 906, in execute
    return self._execute_text(object, multiparams, params)
  File "/home/local/filepath/anaconda2/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1054, in _execute_text
    statement, parameters
  File "/home/local/filepath/anaconda2/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1146, in _execute_context
    context)
  File "/home/local/filepath/anaconda2/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1344, in _handle_dbapi_exception
    util.reraise(*exc_info)
  File "/home/local/filepath/anaconda2/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context
    context)
  File "/home/local/filepath/anaconda2/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 450, in do_execute
    cursor.execute(statement, parameters)
  File "/home/local/filepath/anaconda2/lib/python2.7/site-packages/MySQLdb/cursors.py", line 187, in execute
    query = query % tuple([db.literal(item) for item in args])
TypeError: not enough arguments for format string

I looked this error up but it says to pass the string you want to format as a tuple and this error seems to be part of the underlying API rather than my code. What is the issue? I have run a similar length query before using the same exact code and I had no TypeError doing so.

Jeremy Fisher
  • 1,744
  • 2
  • 21
  • 46
  • What is `query`? It should be a string containing exactly as many `%`-format parameters as there are items in `args`. – Tim Pietzcker Jan 14 '16 at 16:00
  • the thing is `query` is not part of my code. it's part of SQLAlchemy's code. so i'm not sure how to debug it. – Jeremy Fisher Jan 14 '16 at 16:00
  • but my guess would be that `query` is whatever is passed to `dev_engine.execute()` – Jeremy Fisher Jan 14 '16 at 16:01
  • 2
    This seems relevant http://stackoverflow.com/questions/10678229/how-can-i-selectively-escape-percent-in-python-strings – pvg Jan 14 '16 at 16:05
  • 1
    lol, I was escaping the `%` incorrectly. That was it. thanks – Jeremy Fisher Jan 14 '16 at 16:17
  • First, use the `filter` and `join` methods of SQLAlchemy. If you have multi-line strings, use triple `"""` instead of `\`. Escape only when needed (e.g. almost never). – Daniel Jan 14 '16 at 17:07
  • for the filter and join methods wouldn't I have to use declarative syntax which means I would need to construct mappers and classes for the tables etc? I'm just getting the info from this database and not modifying it, plus the tables in this DB are huge and I query different ones, so it would be quite difficult to write mappers for all of them. – Jeremy Fisher Jan 14 '16 at 17:19
  • There are '%'s in your query. I found this helpful: https://stackoverflow.com/questions/33584069/sql-string-substitution-error-not-enough-arguments-for-format-string. – Quan-Lai May 25 '18 at 23:31

0 Answers0