Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Is SQLAlchemy still recommended if only used for raw sql query?

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.

like image 934
Cyril N. Avatar asked Jul 18 '12 14:07

Cyril N.


People also ask

When should I use SQLAlchemy?

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.

Is it worth using SQLAlchemy?

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

How do I run a raw SQL query in SQLAlchemy?

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.

Should I use raw SQL?

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.


2 Answers

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.

like image 127
Martijn Pieters Avatar answered Nov 01 '22 15:11

Martijn Pieters


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 :

  • Multithreading is not supported ! It's ok if you don't work with multiprocessing from Python.
  • You won't have all the advantages of SQLAlchemy (Database to Class (model) wrapper, Query generation (select, where, order_by, etc)). This is the key point on how you want to work with your database.

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.

like image 22
Cyril N. Avatar answered Nov 01 '22 16:11

Cyril N.