sqlalchemy error inserting with bind parameter missing, but works in Workbench

Question

I keep having a strange issue with my program doing SQL inserts. I'm periodically (once a day) sending a lot of data using pymysql in a single INSERT statement and every few days I get the following error:

(sqlalchemy.exc.InvalidRequestError) A value is required for bind parameter 'D' [SQL:

In my code, this is how the db is connected to and insert is done:

Session creation:

engine = sqlalchemy.create_engine(db_host, echo = False)
self.metadata = sqlalchemy.MetaData(engine)
DB_session = sqlalchemy.orm.sessionmaker(bind = engine)
session = DB_session()

Insert function:

def insert(self,sql):
    complete = False
    try:
        self.session.execute(sql)
        self.session.commit()
        complete = True
    except Exception as e:
        # Do some logging
    finally:
        return complete

And here's what a typical insert statement would look like:

INSERT INTO my_db.my_table (field_1, field_2, field_3, ... field_32) 
VALUES(data_set1),(data_set2),(data_set3)...(data_set500) 
ON DUPLICATE KEY UPDATE 
field_1=VALUES(field_1), field_2=VALUES(field_2), field_3=VALUES(field_3) ... field_32=VALUES(field_32)

I've looked up this issue and normally it seems to happen when the user explicitly sets bind parameters and one is missing, but I don't understand why it's happening with my code where I don't set any, and I have no field name 'D' or know what that 'D' is even referring to. Also, to make things more confusing, when I plug in the EXACT same statement into MySQL Workbench, it executes with no problems.

I'm thinking the issue might stem from trying to send too much data at the same time, because I am trying to update/insert a lot of data with a lot of fields each. But if that's the case, how much is too much data since I don't want to be splitting this into 500+ individual inserts?

i can think of 2 possibilities, 1 too much data truncating the INSERT statement and SQL parsing just throwing incorrect error, 2 the INSERT statement accidentally contains "(D)" which sql parser is treating as parameter for substitution and not able to find it. — vk-code, Jul 29 '19 at 18:59
Try to enable mysql debug logging for a while. This can help you see what exactly going on in your DB. — Tirex, Jul 30 '19 at 18:46
Is the SQL query properly sanitised? Also if the values are larges then try to batch them to avoid any truncating issues as @vk-code mentioned — Tarun Lalwani, Aug 01 '19 at 13:07
if you would be sending too much data, you would see a different error (related to `max_allowed_packet` which limits the size of each query that you send to the server`) — bagerard, Aug 04 '19 at 19:14
For troubleshooting, you can try to modify the current query to insert just 1 row (keeping the exact same construct), and execute it 500 times. This should allow you to tell if the problem is 1) the format of the query 2) 1 particular row that is causing the issue 3) the fact that you are inserting too much data at once — bagerard, Aug 04 '19 at 19:18

bagerard · Answer 1 · 2019-08-05T21:31:18.177

You should use the textual-sql feature of sqlalchemy for sending plain sql query but note that sqlalchemy assumes that your query is sanitized.

Try to simply convert your query with text:

from sqlalchemy.sql import text 

session = ...

session.execute(text(sql))

Note that it will use autocommit=True by default, if you want to turn that off, check this doc

Otherwise there are 2 other ways to deal with bind parameters in sqlalchemy:

if you already have sqlalchemy's Table & Column objects, the preferred way and the most object-oriented is to use Bind Parameter Objects
Alternatively you can use a manually-formatted plain query and provide a dict with the bind parameters, assuming I have a table test with 2 columns id & name. This is the safest as it will rely on sqlalchemy to sanitize the params:

from sqlalchemy.sql import text 

session = ...

insert_qry = """
                INSERT INTO test (id, name) VALUES(:id1, :name1), (:id2, :name2)
                ON DUPLICATE KEY UPDATE id=VALUES(id), name=VALUES(name)
             """
params = {'id1':1, 'name1': 'John', 'id2':2, 'name2': 'Bill'}

session.execute(text(insert_qry), params)

sqlalchemy error inserting with bind parameter missing, but works in Workbench

1 Answers1