{"id":174083,"date":"2023-11-27T10:59:20","date_gmt":"2023-11-27T09:59:20","guid":{"rendered":"https:\/\/liora.io\/en\/?p=174083"},"modified":"2026-02-23T14:05:52","modified_gmt":"2026-02-23T13:05:52","slug":"sqlalchemy-what-is-it-whats-it-for","status":"publish","type":"post","link":"https:\/\/liora.io\/en\/sqlalchemy-what-is-it-whats-it-for","title":{"rendered":"SQLAlchemy: What is it? What’s it for?"},"content":{"rendered":"\n

How to combine the flexibility of SQL with the simplicity and efficiency of the Python language? That’s the ambition of SQLAlchemy. Discover everything you need to know about SQLAlchemy, a tool based on the object-relational mapping (ORM) principle.<\/strong><\/p>\n\n\n\n

What is object-relational mapping?<\/h2>\n\n\n\n

SQLAlchemy<\/strong> is a tool based on the object-relational mapping (ORM) principle.<\/a> ORM is a computer technique that maps the schema of a relational database (commonly known as SQL databases<\/a>) and the classes of an object-oriented programming language (in this case, Python).<\/p>\n\n\n\n

It creates a virtual layer between us and the database, which we can then use to query the database directly from a Python script.<\/a> SQLAlchemy simplifies the connection between Python and SQL databases by automatically converting Python<\/a> class calls into SQL statements. This makes it possible to query relational databases in a Pythonic way.<\/p>\n\n\n\n

Parts\/Decomposition Alchemy<\/h3>\n\n\n\n

SQLAlchemy<\/strong> can be divided into several parts:<\/p>\n\n\n\n

    \n
  1. The engine, which is used to connect to the database and communicate our queries: create_engine()<\/code>. Typically, you pass a string argument indicating the location of the database (local path or URL), the database driver\/dialect, and connection arguments (which can include credentials). In the following example, we use SQLite with a local database:\u00a0<\/strong><\/li>\n<\/ol>\n\n\n\n

    engine = create_engine(“sqlite:\/\/\/base1.db”, echo=False)<\/p>\n\n\n\n

    However, you could also use PostgreSQL:<\/p>\n\n\n\n

    engine = create_engine(“postgresql:\/\/\/base1.db”, echo=False)<\/p>\n\n\n\n

      \n

    1. DBAPI stands for “Python Database API Specification.<\/a>” SQLAlchemy, like other database connection packages, is built on this global interface and adds its specific services on top of it.<\/p><\/li>\n\n\n\n

    2. SQLAlchemy also allows you to manage the Data Modification Language (DML) part of SQL, which includes basic table modification operations known as CRUD operations (Create, Read, Update, Delete).<\/p><\/li>\n<\/ol>\n\n\n\n

      SQLAlchemy<\/strong> maps SQL INSERT, UPDATE, and DELETE<\/strong> statements to its own methods: insert<\/code>, update<\/code>, and delete<\/code>, thanks to the ORM mechanism. This allows you to modify tables in an object-oriented way, as shown in the following example.<\/p>\n\n\n\n

      We connect to the database via .connect(), then give SQL statements (INSERT to create a row, DELETE to delete it,<\/a> SELECT for a simple read) followed by .execute() to send them to the database.<\/p>\n\n\n\n