SQLAlchemy 2.0:
with engine.connect() as connection:
result = connection.execute(text('SELECT * FROM your_table'))
# do something with the result..
SQLAlchemy 1.x:
from sqlalchemy import text
sql = text('select name from penguins')
result = db.engine.execute(sql)
names = [row[0] for row in result]
print names
Note that db.engine.execute() is "connectionless", which is deprecated in SQLAlchemy 2.0.
python - How to execute raw SQL in Flask-SQLAlchemy app - Stack Overflow
Records: Python library for making raw SQL queries to Postgres databases
python - How to query with raw SQL using Session or engine - Stack Overflow
Do Programmers Still Write Raw SQL?
Definitely yes. Any large enough project has That One Query.
More on reddit.comVideos
SQLAlchemy 2.0:
with engine.connect() as connection:
result = connection.execute(text('SELECT * FROM your_table'))
# do something with the result..
SQLAlchemy 1.x:
from sqlalchemy import text
sql = text('select name from penguins')
result = db.engine.execute(sql)
names = [row[0] for row in result]
print names
Note that db.engine.execute() is "connectionless", which is deprecated in SQLAlchemy 2.0.
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})
You can use the execute() method of Session:
session.execute('select * from table')
The execute method's documentation can be found here:
http://docs.sqlalchemy.org/en/latest/orm/session_api.html#sqlalchemy.orm.session.Session.execute
Please note this does not protect against SQL Injection.
With SQLAlchemey 1.4/2.0, you need to wrap the SQL string in an Executable.
from sqlalchemy import text
session.execute(text("select * from table"))
Today's world is full of ORMs and database abstraction layers.
From your experience and point of view, is writing raw SQL still relevant today?
Do you work on tuning raw SQL queries and handle performance issues as in the past?
Please share your thoughts :)
Definitely yes. Any large enough project has That One Query.
ORMs cover only a very small part of what modern SQL databases can do. If you limit yourself to this subset, you are doing it wrong.
Have a look at my presentation "modern SQL" to get an idea what SQL can do knowdays. I don't think that any of these capabilities is used by any ORM (two times any, likely to be wrong in at least on case ;)
http://modern-sql.com/slides
I wrote a very small flask API to serve data from a MySQL database to a frontend application as a pet project.
When a user accesses an endpoint and provides arguments, the script reads a SQL query stored in a .sql file, formats it with said provided arguments, and runs it via mysql-connector-python.
Examples (syntax may be incorrect, this is not from my code, just for demonstration)
conn = mysql.connector.connect(<...>)
cursor = conn.cursor()
@app.route("/users", methods=["GET"])
def get_user():
user_name = request.args.get('name')
# open SQL file, read query, and format
with open("./sql/example_sql_query.sql") as f:
query = f.read()
cursor.execute(query.format(name=user_name))
result = cursor.fetchone()
< logic to return resulting data >Example SQL query
SELECT *
FROM users
WHERE user_name = {name}I ask because from all examples I've seen, I've never seen anyone doing this. Everyone seems to use SQLAlchemy, but given my current role as a SQL dev I'm much more comfortable using raw SQL to generate results. This method has been working so far.
I'd like to hear your thoughts!
Django (like other similar ORM tools) is a connection between relational databases and object-oriented programming. One of the very important functions that it implements is providing a uniform interface to the database -- regardless of the underlying database.
When you use underlying Django functionality, the code should be supported on any database (there may be specific limits on this). This makes it particularly easy to port to another database. It also helps ensure that the generated queries do what you intend.
When you use raw SQL, the code is likely to be specific to one database (creating a porting problem). The code is also not checked, which can result in hard-to-understand errors.
I have a strong preference for using SQL directly -- but that is because I am not a programmer using an ORM framework. If you are going to use such a framework, it is probably better to use the built-in functionality wherever possible.
This is a borderline opinion question so might get flagged, but it is a good point. Essentially the raw SQL queries are intended to only be used for the edge cases where the Django ORM does not fulfil your needs (and with each new version of Django it support more and more query types so raw becomes less useful).
In general I would suggest using the ORM for the more helpful error messages, maintainability, and plain ease of use, and only use raw as a last-resort