Django development of products is usually easy and straightforward: great documentation, many tools out of the box, plenty of open source libraries and big community. Django ORM takes full control about SQL layer protecting you from mistakes, and underlying details of queries so you can spend more time on designing and building your application structure in Python code.<\/p>\n
However, sometimes such behavior may hurt – for example, when Django developers are building a project related to data analysis. Building advanced queries with Django is not very easy; it\u2019s hard to read (in Python) and hard to understand what\u2019s going on in SQL-level without logging or printing generated SQL queries somewhere. Moreover, such queries could not be efficient enough, so this will hit you back when you load more data into DB to play with.<\/p>\n
In one moment, you can find yourself doing too much raw SQL through Django cursor, and this is the moment when you should do a break and take a look on another interesting tool, which is placed right between ORM layer and the layer of raw SQL queries.<\/p>\n
As you can see from the title of the article, we successfully mixed Django ORM and SQLAlchemy Core together, and we\u2019re very satisfied with results. We built an application which helps to analyze data produced by EMR systems by aggregating data into charts and tables, scoring by throughput\/efficiency\/staff cost, and highlighting outliers which allows to optimize business processes for clinics and save money. Well, let’s learn more about the integration of Django with SQLAlchemy.<\/p>\n
By the way, our Django specialists have extensive experience in using Django and SQLAlchemy. Therefore, if you need additional advice or assistance in developing a project, please contact us or see our portfolio<\/a>.<\/p>\n There are a few reasons why we stepped out from Django ORM for this task:<\/p>\n It\u2019s worth saying that we also set up a second database, which is handled by Django ORM to cover other web application related tasks and business-logic needs, which it perfectly does. Django ORM is evolving from version to version, giving more and more features. For a Django upgrade<\/a>, keep analytics in a decoupled SQLAlchemy Core layer\u2014often on a separate database\u2014so you can adopt new Django releases without rewriting domain models.<\/p>\n For example, in recent releases, a bunch of neat features were added like support of Subquery expressions<\/a> or Window functions<\/a> and many others, which you should definitely try before doing raw SQL or looking at the tools like SQLAlchemy if your problem is more complex than fixing a few queries.<\/p>\n So that\u2019s why we decided to take a look at SQLAlchemy. It consists of two parts – ORM and Core. SQLAlchemy ORM is similar to Django ORM, but at the same time, they differ. SQLAlchemy ORM uses a different concept, Data Mapper, compared to Django’s Active Record approach. As far as you\u2019re building projects on Django, you definitely should not switch ORM (if you don\u2019t have very special reasons to do so), as you want to use Django REST framework, Django-admin, and other neat stuff which is tied to Django models. In Django vs Flask<\/a> evaluations, use Django\u2019s ORM for app workflows and layer in SQLAlchemy Core for complex, aggregation-heavy analytics\u2014gaining both productivity and explicit, readable SQL where it counts. Which will result into<\/p>\n Note: with such tricks, we don\u2019t fall into So if you\u2019re interested and want to try to mix SQLAlchemy with Django application, here are some hints which could help you. You can use this instruction as a tutorial for using SQLALchemy with Django. (This one looks not so scary, but with more complex queries it could be about 20+ placeholders, which are very annoying and time-expensive to fill manually to play later in SQL console.) For dates, such a trick will not work. There is a discussion<\/a> on StackOverflow on how to achieve the desired results, but solutions look unattractive. Note: Pytest multidb note<\/a> says \u201cCurrently pytest-django does not specifically support Django\u2019s multi-database support. You can however use normal Django TestCase instances to use it\u2019s multi_db support.\u201d So with default transaction isolation level, the application will not see any data, only empty tables. It\u2019s possible to change transaction isolation level to To sum up everything above, SQLAlchemy Core is a great tool which brings you closer to SQL and gives you understanding and full control over the queries. If you\u2019re building the application (or a part of it) which requires advanced aggregations, it is worth it to check out SQLAlchemy Core capabilities as an alternative to Django ORM tools.<\/p>\n By the way, if you want to hire an SQL Alchemy ORM developer, you can contact<\/a> our Django agency<\/a> for advice.<\/p>\n For a more in-depth dive into the topic, I would recommend to check out my talk on Djangocon Europe 2019.<\/p>\nWhat is the point of mixing Django ORM with SQLAlchemy?<\/h2>\n
\n
\nThe second part of SQLAlchemy is called Core. It\u2019s placed right between high-level ORM and low-level SQL. The Core is very powerful and flexible; it gives you the ability to build any SQL-queries you wish, and when you see such queries in Python, it\u2019s easy to understand what\u2019s going on. For example, take a look into a sample query from the documentation:<\/p>\nq = session.query(User).filter(User.name.like('e%')).\r\n limit(5).from_self().\r\n join(User.addresses).filter(Address.email.like('q%')).\r\n order_by(User.name)\r\n<\/code><\/pre>\nSELECT anon_1.user_id AS anon_1_user_id,\r\n anon_1.user_name AS anon_1_user_name\r\nFROM (SELECT \"user\".id AS user_id, \"user\".name AS user_name\r\nFROM \"user\"\r\nWHERE \"user\".name LIKE :name_1\r\n LIMIT :param_1) AS anon_1\r\nJOIN address ON anon_1.user_id = address.user_id\r\nWHERE address.email LIKE :email_1 ORDER BY anon_1.user_name\r\n<\/code><\/pre>\nN+1 problem<\/code>: from_select<\/code> makes an additional SELECT<\/code> wrapper around the query, so we reduce the amount of rows at first (via LIKE<\/code> and LIMIT<\/code>) and only then we join the address information.<\/p>\nHow to mix Django and SQLALchemy<\/h2>\n
\nFirst of all, you need to create<\/a> a global variable with Engine, but the actual connection with DB will be established on first connect<\/code> or execute<\/code> call.<\/p>\nsa_engine = create_engine(settings.DB_CONNECTION_URL, pool_recycle=settings.POOL_RECYCLE)\r\n<\/code><\/pre>\nCreate_engine<\/code> accepts additional configuration for connection. MySQL\/MariaDB\/AWS Aurora (MySQL compatible) have an interactive_timeout setting which is 8h by default, so without pool_recycle<\/code> extra parameter you will get annoying SQLError: (OperationalError) (2006, \u2018MySQL server has gone away\u2019)<\/code>. So the POOL_RECYCLE<\/code> should be smaller than interactive_timeout<\/code>. For example a half of it: POOL_RECYCLE = 4 * 60 * 60<\/code>
\nNext step is to build your queries. Depending on your application architecture, you can declare tables and fields using<\/a> Table<\/code> and Column<\/code> classes (which also can be used with ORM), or if your application already stores tables and columns names in another way, you can do it in place, via Table<\/code>\u00a0and Column<\/code> functions (as shown here<\/a>).
\nIn our app, we picked the second option, as we stored information about aggregations, formulas, and formatting in our own declarative syntax. Then we built a layer which read such structures and executed queries based on the provided instructions.
\nWhen your query is ready, simply call sa_engine.execute(query)<\/code>. The cursor would be opened until you read all the data or if you close it explicitly.
\nThere is one very annoying thing worth mentioning. As a documentation says<\/a>, SQLAlchemy in Django has limited ability to do query stringification, so it\u2019s not so easy to get a final query which will be executed. You can print query by itself:<\/p>\nprint(query)\r\nSELECT\r\nrole_group_id, role_group_name, nr_patients\r\nFROM \"StaffSummary\"\r\nWHERE day >= :day_1 AND day <= :day_2 AND location_id = :location_id_1 AND service_id = :service_id_1\r\n<\/code><\/pre>\n
\nIf you have only strings and numbers to be inserted into query, this will work for you<\/p>\nprint(s.compile(compile_kwargs={\"literal_binds\": True}))\r\n<\/code><\/pre>\n
\nAnother option is to enable queries logging into the file via database configuration, but in this case, you could face another issue; it becomes hard to find a query you want to debug if Django ORM connected to this database too.<\/p>\nTesting<\/h2>\n
\nSo what does it mean – not support? By default, Django will create and remove (at the end of all tests) a test-database for each db listed in DATABASES<\/code> definition. This feature works perfectly with pytests<\/code> also.
\nDjango TestCase<\/code> and TransactionTestCase<\/code> with multi_db=True<\/code> enables erasing of data in multiple databases between tests. Also it enables data loading into second database via django-fixtures<\/code>, but it\u2019s much better to use modelmommy<\/em><\/a> or factoryboy<\/a> instead, which are not affected by this attribute.
\nThere are a few hacks suggested in pytest-django discussion<\/a> how to work around the issue and enable multi_db<\/code> to continue pytesting.
\nThere is one important advice – for tables that have Django-models, you should save data to DB via Django-ORM. Otherwise, you will face issues during writing tests. TestCase<\/code> will not be able to rollback other transactions which happened outside from Django DB connection. If you have such a situation, you may use TransactionalTestCase<\/code> with multi_db=True<\/code> for tests which trigger functionality, which produces DB writes through SQLAlchemy connection, but remember that such tests are slower than regular TestCase<\/code>.
\nAlso, another scenario is possible – you have Django-models only in one database and you\u2019re working with the second database via SQLAlchemy. In this case, multi_db<\/code> doesn\u2019t affect you at all. In such cases, you need to write a pytest-fixture (or do it as a mixin and trigger logic in setUp<\/code> if you\u2019re using unittests) which will create DB structure from SQL file. Such a file should contain DROP TABLE IF EXISTS<\/code> statements before CREATE TABLE<\/code>. This fixture should be applied to each test case which manipulates with this database. Other fixture could load data into created tables.
\nNote: Such tests will be slower as tables will be recreated for each test. Ideally, tables should be created once (declared as @pytest.fixture(scope='session', autouse=True)<\/code>), and each transaction should rollback data for each test. It\u2019s not easy to achieve because of different connections: Django & SQLAlchemy or different connections of SQLAlchemy connection-pool, e.g in your tests you start the transaction, fill DB with test data, then run test and rollback transaction (it wasn\u2019t committed). But during the test, your application code may do queries to DB like connection.execute(query) which performed outside of transaction which created test data.<\/p>\nREAD UNCOMMITTED<\/code> for SQLAlchemy connection, and everything will work as expected, but it\u2019s definitely not a solution at all.<\/p>\nConclusion<\/h2>\n
Edit:<\/h3>\n