Python, SQLAlchemy pass parameters in connection.execute

by

The tutorial gives a pretty good example for this:

>>> from sqlalchemy.sql import text
>>> s = text(
...     "SELECT users.fullname || ', ' || addresses.email_address AS title "
...         "FROM users, addresses "
...         "WHERE users.id = addresses.user_id "
...         "AND users.name BETWEEN :x AND :y "
...         "AND (addresses.email_address LIKE :e1 "
...             "OR addresses.email_address LIKE :e2)")
SQL>>> conn.execute(s, x='m', y='z', e1='%@aol.com', e2='%@msn.com').fetchall() 
[(u'Wendy Williams, [email protected]',)]

First, take your SQL string and pass it to sqalchemy.sql.text(). This isn’t necessary, but probably a good idea…

The advantages text() provides over a plain string are backend-neutral
support for bind parameters, per-statement execution options, as well
as bind parameter and result-column typing behavior, allowing
SQLAlchemy type constructs to play a role when executing a statement
that is specified literally.

Note that even if you didn’t use text(), you should NEVER just use sql.format(...). This leads to greater risk of SQL injection attacks.

Next, you can specify the actual arguments using keyword parameters to the execute() function you’ve already been using.

Now, in your example, you have a function that wraps the execute functionality. So, if you want to use this for multiple queries, you’ll need to make the parameters able to receive your arguments. You could do this pretty simple as a dictionary:

def _sql_to_data(sql, values):
    ...
    conn.execute(sql, values)

values would be a dictionary.You could then use your function like this…

sql="SELECT ..."
data = { 'user_id' : 3 }
results = _sql_to_data(sql, data)

Using keywords as your parameters is just one way of specifying the arguments to the execute() function. You can read the documentation for that function for a few different ways.

More Related Contents:

  1. How to execute raw SQL in Flask-SQLAlchemy app
  2. How do I get a raw, compiled SQL query from a SQLAlchemy expression?
  3. sqlalchemy: how to join several tables by one query?
  4. Complex foreign key constraint in SQLAlchemy
  5. How to get rows which match a list of 3-tuples conditions with SQLAlchemy
  6. SQLAlchemy: how to filter date field?
  7. How to execute “left outer join” in SqlAlchemy
  8. Getting random row through SQLAlchemy
  9. Select NULL Values in SQLAlchemy
  10. python pandas to_sql with sqlalchemy : how to speed up exporting to MS SQL?
  11. SQLAlchemy Obtain Primary Key With Autoincrement Before Commit
  12. VALUES clause in SQLAlchemy
  13. flask sqlalchemy query with keyword as variable
  14. Sqlite / SQLAlchemy: how to enforce Foreign Keys?
  15. How to create a large pandas dataframe from an sql query without running out of memory?
  16. SQLAlchemy IN clause
  17. Passing table name as a parameter in psycopg2
  18. Getting the SQL from a Django QuerySet [duplicate]
  19. Using a WHERE ___ IN ___ statement
  20. Django: Adding “NULLS LAST” to query
  21. How to create a new database using SQLAlchemy?
  22. How to log all sql queries in Django?
  23. IntegrityError when inserting data in an association table using SQLAlchemy
  24. AttributeError: ‘int’ object has no attribute ‘_sa_instance_state’
  25. How do I connect to SQL Server via sqlalchemy using Windows Authentication?
  26. Best way to do enum in Sqlalchemy?
  27. SQLAlchemy Joinedload filter column
  28. How to set connection timeout in SQLAlchemy
  29. SQLAlchemy classes across files
  30. How can I achieve a self-referencing many-to-many relationship on the SQLAlchemy ORM back referencing to the same attribute?

Leave a Comment