Building prepared statements

[grigi]

Hi, in Tortoise-ORM we would like to build prepared statements, specifically for inserts. This would allow us to use the DB-provided query cache, and DB-specific bulk insert operations, or update operations.

Right now we have a statement like: (cleaned up)

Query.into(Table(table_name)).columns(*columns).insert(*values)

Where columns and values are lists of equal length.

This generates SQL like so:

INSERT INTO table_name (col1,col2,col3) VALUES ('value1', 'value2', 'value3)

But to use the execute_many() statement, I need to have SQL like this:

INSERT INTO table_name (col1,col2,col3) VALUES (?,?,?)

(or %, depending on the DB driver)

It also raises the concern of escaping, where I would normally want to delegate escaping the values to the DB driver itself. Is escaping happening correctly?

Now, I can manually generate the SQL (I'd rather not bypass this excellent library unless needed). What would be the best way for me to get there?

[twheys]

A couple have requested this already. I don't think I'll have time to do it in the immediate future, but I could help if you would be interested in trying to add the feature yourself? Let me know if you'd be interested.

[twheys]

Going to close this as a duplicate to #113

[grigi]

I am thinking of doing something like this: Query.into(Table(table_name)).columns(*columns).prepared()

This is easy for inserts, less so for everything else. Will have to think on it some more.

MySQL wants %s, SQLite ? and PostgreSQL &1,&2 etc... Would not be surprised if it also varies by driver...