How to execute raw SQL in Flask-SQLAlchemy app

Question

How do you execute raw SQL in SQLAlchemy   I have a python web app that runs on flask and interfaces to the database through SQLAlchemy    I need a way to run the raw SQL  The query involves multiple table joins along with Inline views    I ve tried   connection   db session connection   connection execute   lt sql here gt      But I keep getting gateway errors

User · Accepted Answer

Have you tried   result   db engine execute   lt sql here gt      or   from sqlalchemy import text  sql   text  select name from penguins   result   db engine execute sql  names    row 0  for row in result  print names

User · Answer

SQL Alchemy session objects have their own execute method   result   db session execute  SELECT   FROM my table WHERE my column    val     val   5     All your application queries should be going through a session object  whether they re raw SQL or not  This ensures that the queries are properly managed by a transaction  which allows multiple queries in the same request to be committed or rolled back as a single unit  Going outside the transaction using the engine or the connection puts you at much greater risk of subtle  possibly hard to detect bugs that can leave you with corrupted data  Each request should be associated with only one transaction  and using db session will ensure this is the case for your application   Also take note that execute is designed for parameterized queries  Use parameters  like  val in the example  for any inputs to the query to protect yourself from SQL injection attacks  You can provide the value for these parameters by passing a dict as the second argument  where each key is the name of the parameter as it appears in the query  The exact syntax of the parameter itself may be different depending on your database  but all of the major relational databases support them in some form   Assuming it s a SELECT query  this will return an iterable of RowProxy objects   You can access individual columns with a variety of techniques   for r in result      print r 0     Access by positional index     print r  my column      Access by column name as a string     r dict   dict r items      convert to dict keyed by column names   Personally  I prefer to convert the results into namedtuples   from collections import namedtuple  Record   namedtuple  Record   result keys    records    Record  r  for r in result fetchall    for r in records      print r my column      print r      If you re not using the Flask-SQLAlchemy extension  you can still easily use a session   import sqlalchemy from sqlalchemy orm import sessionmaker  scoped session  engine   sqlalchemy create engine  my connection string   Session   scoped session sessionmaker bind engine    s   Session   result   s execute  SELECT   FROM my table WHERE my column    val     val   5

User · Answer

You can get the results of SELECT SQL queries using from statement   and text   as shown here  You don t have to deal with tuples this way  As an example for a class User having the table name users you can try  from sqlalchemy sql import text  user   session query User  from statement      text  quot  quot  quot SELECT   FROM users where name  name quot  quot  quot     params name  quot ed quot   all    return user

User · Answer

This is a simplified answer of how to run SQL query from Flask Shell   First  map your module  if your module app is manage py in the principal folder and you are in a UNIX Operating system   run   export FLASK APP manage   Run Flask shell  flask shell   Import what we need    from flask import Flask from flask sqlalchemy import SQLAlchemy db   SQLAlchemy app  from sqlalchemy import text   Run your query   result   db engine execute text   lt sql here gt    execution options autocommit True     This use the currently database connection which has the application

User · Answer

docs  SQL Expression Language Tutorial - Using Text  example   from sqlalchemy sql import text  connection   engine connect      recommended cmd    select   from Employees where EmployeeGroup    group  employeeGroup    Staff  employees   connection execute text cmd   group   employeeGroup     or - wee more difficult to interpret the command employeeGroup    Staff  employees   connection execute                    text  select   from Employees where EmployeeGroup    group                       group   employeeGroup     or - notice the requirement to quote  Staff  employees   connection execute                    text  select   from Employees where EmployeeGroup    Staff       for employee in employees  logger debug employee    output  0   Tim    Gurra    Staff    991-509-9284    1   Jim    Carey    Staff    832-252-1910    2   Lee    Asher    Staff    897-747-1564    3   Ben    Hayes    Staff    584-255-2631

User · Answer

Have you tried using connection execute text   lt sql here gt      lt bind params here gt    and bind parameters as described in the docs  This can help solve many parameter formatting and performance problems  Maybe the gateway error is a timeout  Bind parameters tend to make complex queries execute substantially faster

User · Answer

If you want to avoid tuples  another way is by calling the first  one or all methods   query   db engine execute  SELECT   FROM blogs                               WHERE id   1     assert query first   name     Welcome to my blog

User · Answer

result   db engine execute text  quot  lt sql here gt  quot     executes the  lt sql here gt  but doesn t commit it unless you re on autocommit mode  So  inserts and updates wouldn t reflect in the database  To commit after the changes  do result   db engine execute text  quot  lt sql here gt  quot   execution options autocommit True

[python] How to execute raw SQL in Flask-SQLAlchemy app

Examples related to python

Examples related to sql

Examples related to sqlalchemy

Examples related to flask

Examples related to flask-sqlalchemy