Using Flask, I'm curious to know if SQLAlchemy is still the best way to go for querying my database with raw SQL (direct SELECT x FROM table WHERE ...
) instead of using the ORM or if there is an simpler yet powerful alternative ?
Thank for your reply.
SQLAlchemy is the ORM of choice for working with relational databases in python. The reason why SQLAlchemy is so popular is because it is very simple to implement, helps you develop your code quicker and doesn't require knowledge of SQL to get started.
SQLAlchemy is great because it provides a good connection / pooling infrastructure; a good Pythonic query building infrastructure; and then a good ORM infrastructure that is capable of complex queries and mappings (as well as some pretty stone-simple ones).
Import necessary functions from the SQLAlchemy package. Establish connection with the PostgreSQL database using create_engine() function as shown below, create a table called books with columns book_id and book_price. Insert record into the tables using insert() and values() function as shown.
Conclusion. Raw SQL is for sure the most powerful way to interact with your database as it is the databases native language. The drawback is that you might use features which are specific to that database, which makes a future database switch harder.
I use SQLAlchemy for direct queries all the time.
Primary advantage: it gives you the best protection against SQL injection attacks. SQLAlchemy does the Right Thing whatever parameters you throw at it.
I find it works wonders for adjusting the generated SQL based on conditions as well. Displaying a result set with multiple filter controls above it? Just build your query in a set of if/elif/else constructs and you know your SQL will be golden still.
Here is an excerpt from some live code (older SA version, so syntax could differ a little):
# Pull start and end dates from form
# ...
# Build a constraint if `start` and / or `end` have been set.
created = None
if start and end:
created = sa.sql.between(msg.c.create_time_stamp,
start.replace(hour=0, minute=0, second=0),
end.replace(hour=23, minute=59, second=59))
elif start:
created = (msg.c.create_time_stamp >=
start.replace(hour=0, minute=0, second=0))
elif end:
created = (msg.c.create_time_stamp <=
end.replace(hour=23, minute=59, second=59))
# More complex `from_` object built here, elided for example
# [...]
# Final query build
query = sa.select([unit.c.eli_uid], from_obj=[from_])
query = query.column(count(msg.c.id).label('sent'))
query = query.where(current_store)
if created:
query = query.where(created)
The code where this comes from is a lot more complex, but I wanted to highlight the date range code here. If I had to build the SQL using string formatting, I'd probably have introduced a SQL injection hole somewhere as it is much easier to forget to quote values.
After I worked on a small project of mine, I decided to try to just use MySQLDB, without SQL Alchemy.
It works fine and it's quite easy to use, here's an example (I created a small class that handles all the work to the database)
import MySQLdb
from MySQLdb.cursors import DictCursor
class DatabaseBridge():
def __init__(self, *args, **kwargs):
kwargs['cursorclass'] = DictCursor
self.cnx = MySQLdb.connect (**kwargs)
self.cnx.autocommit(True)
self.cursor = self.cnx.cursor()
def query_all(self, query, *args):
self.cursor.execute(query, *args)
return self.cursor.fetchall()
def find_unique(self, query, *args):
rows = self.query_all(query, *args);
if len(rows) == 1:
return rows[0]
return None
def execute(self, query, params):
self.cursor.execute(query, params)
return self.cursor.rowcount
def get_last_id(self):
return self.cnx.insert_id()
def close(self):
self.cursor.close()
self.cnx.close()
database = DatabaseBridge(**{
'user': 'user',
'passwd': 'password',
'db': 'my_db'
})
rows = database.query_all("SELECT id, name, email FROM users WHERE is_active = %s AND project = %s", (1, "My First Project"))
(It's a dumb example).
It works like a charm BUT you have to take these into consideration :
multiprocessing
from Python.But on the other hand, and like SQLAlchemy, there is protections agains't SQL injection attacks :
A basic query would be like this :
cursor.execute("SELECT * FROM users WHERE data = %s" % "Some value") # THIS IS DANGEROUS
But you should do :
cursor.execute("SELECT * FROM users WHERE data = %s", ("Some value",)) # This is secure!
Saw the difference ? Read again ;)
The difference is that I replaced %
, by ,
: We pass the arguments as ... arguments to the execute, and these are escaped. When using %
, arguments aren't escaped, enabling SQL Injection attacks!
The final word here is that it depends on your usage and what you plan to do with your project. For me, SQLAlchemy was on overkill (it's a basic shell script !), so MysqlDB was perfect.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With