I often need to extract some data from a MySQL database, apply some changes in Python and then send the file to recipient. I need help with parameter style for SQL files, containing scripts. I am looking for python-mysql connector that supports "named" parameter style. I've been using pymysql in conjuntion with sqlalchemy's create_engine and pandas.
For the purpose of example my SQL script called export.sql would look like this:
SELECT transaction_id, user_email
FROM purchases
WHERE date_added >= :export_date
The format with the colon is something customary we use in script files and works well with MySQL Workbench, DBeaver, while other formats had some trouble.
So I would like from a Python script load the SQL script file, providing the parameter in the function. Something like that:
export_date = '2023-06-30'
df = pd.read_sql(sql=query,con=engine, params={'export_date':export_date})
However, it seems that pymysql only accepts named params in the format of %(export_date)s in the SQL script, which I would like to avoid. I have found this Pandas read_sql with parameters thread, which was useful, but unfortunately I have not found yet any connector, that would support that and many have lacking documentation on that (or as a beginner I have trouble finding it).
Are there any other connectors that support params in the format with the colon in front of variable (named parameter)?
