Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Building prepared statements #163

Closed
grigi opened this issue Oct 2, 2018 · 3 comments
Closed

Building prepared statements #163

grigi opened this issue Oct 2, 2018 · 3 comments
Assignees

Comments

@grigi
Copy link
Contributor

grigi commented Oct 2, 2018

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 twheys self-assigned this Oct 22, 2018
@twheys
Copy link
Contributor

twheys commented Oct 22, 2018

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
Copy link
Contributor

twheys commented Oct 22, 2018

Going to close this as a duplicate to #113

@twheys twheys closed this as completed Oct 22, 2018
@grigi
Copy link
Contributor Author

grigi commented Oct 22, 2018

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...

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

2 participants