SQL is a powerful language, but sometimes it is hard to integrate it into your application. You may have used string manipulation to generate queries to run over an ODBC interface or used a DB API as a Python Engineer. In those ways might be hard to support security and database changing.
About
SQLAlchemy — powerful and flexible python library. It is the tools that let you map your application’s classes and objects to database tables once and then to “forget about it,” or to return to your model again and again to fine-tune performance. SQLAlchemy was created by Mike Bayer in 2005.
Why use?
The main reason to use it is to separate code away from the underlying
database. It makes it easy to migrate lThe SQL Expression Language is a Pythonic way of representing common SQL state‐ ments and expressions, and is only a mild abstraction from the typical SQL languageogic from Oracle to PostgreSQL or from an application database to a data warehouse etc. It also helps ensure that database input is sanitized and properly escaped prior to being submitted to the database. This prevents common issues like SQL injection attacks.
SQLAlchemy Core and the SQL Expression Language
The SQL Expression Language is a language that is abstracted from SQL language, in a Pythonic way for representing common SQL statements. It is standardized in such a way that it provides a consistent language across a large number of backend databases.
ORM(object relation mapping) — is a code library that automates the transfer of data stored in relational databases tables into objects that are more commonly used in application code.
Should I choose SQLAlchemy Core or ORM?
To chose the dominant data access layer for an application you may consider personal preference and/or a few factors.
The biggest difference between Core and ORM is the view of data as schema or business objects. SQLAlchemy Core has a schema-centric view, which like traditional SQL is focused around tables, keys, and index structures. This works well for big databases and multiple databases.
In ORM encapsulation can make it easy to make database interactions feel more like normal Python code. Microservices also benefit from the abstraction of the underlying database, allowing the developer to focus on just the process being implemented.
Checklist to help you decide which option is best for you:
Use Core:
- If you are working with a framework that already has an ORM built-in, but want to add more powerful reporting, use Core.
- If you want to view your data in a more schema-centric view (as used in SQL), use Core.
- If you have data for which business objects are not needed, use Core.
Use ORM:
• If you view your data as business objects, use ORM.
- If you are building a quick prototype, use ORM.
Use Core and ORM:
- If you have a combination of needs that really could connect the business objects and other data unrelated to the problem domain then use both.