SQLAlchemy Raw SQL Execution

This guide demonstrates how to execute raw SQL queries using SQLAlchemy's powerful Text module in Python. This is particularly useful when you need to perform operations that are more efficiently handled by direct SQL statements or when working with complex queries not easily expressed through an ORM.

Using the Text Module for Database Operations

SQLAlchemy's text() construct allows you to embed literal SQL expressions within your Python code. This provides a flexible way to interact with your database, offering fine-grained control over SQL statements.

Source: - https://chartio.com/resources/tutorials/how-to-execute-raw-sql-in-sqlalchemy/

Importing Necessary SQLAlchemy Components

Begin by importing the required modules from SQLAlchemy:

>>> import sqlalchemy
>>> from sqlalchemy import create_engine
>>> from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey
>>> from sqlalchemy import inspect

Defining Database Schema with MetaData

Define your database schema using SQLAlchemy's MetaData and Table objects. This example sets up a simple 'book' table.

>>> metadata = MetaData()
>>> books = Table('book', metadata, Column('id', Integer, primary_key=True), Column('title', String), Column('primary_author', String))
>>> engine = create_engine('sqlite:///books.db')
>>> metadata.create_all(engine)

Inspecting Database Table Structure

Use the inspect function to examine the structure of your database tables, such as retrieving column information.

>>> inspector = inspect(engine)
>>> inspector.get_columns('book')
[
  {'name': 'id', 'type': INTEGER(), 'nullable': False, 'default': None, 'autoincrement': 'auto', 'primary_key': 1},
  {'name': 'title', 'type': VARCHAR(), 'nullable': True, 'default': None, 'autoincrement': 'auto', 'primary_key': 0},
  {'name': 'primary_author', 'type': VARCHAR(), 'nullable': True, 'default': None, 'autoincrement': 'auto', 'primary_key': 0}
]

Inserting Data using Parameterized SQL

Execute raw SQL INSERT statements using the text() construct with named parameters to safely insert data. This prevents SQL injection vulnerabilities.

>>> from sqlalchemy.sql import text
>>> with engine.connect() as con:
...     data = ( { "id": 1, "title": "Crushing It", "primary_author": "Gary Vaynerchuck" },{ "id": 2, "title": "Start with Why", "primary_author": "Simon Sinek" })
...     statement = text("""INSERT INTO book(id, title, primary_author) VALUES(:id, :title, :primary_author)""")
...     for line in data:
...         con.execute(statement, **line)
...
<sqlalchemy.engine.cursor.LegacyCursorResult object at 0x10106cf50>
<sqlalchemy.engine.cursor.LegacyCursorResult object at 0x101ed0c50>

Selecting Data with Raw SQL Queries

Retrieve data from your database using a raw SQL SELECT statement executed via the connection object.

>>> with engine.connect() as con:
...     rs = con.execute('SELECT * FROM book')
...     for row in rs:
...         print(row)
...
(1, 'Crushing It', 'Gary Vaynerchuck')
(2, 'Start with Why', 'Simon Sinek')

External Resources for SQLAlchemy

Explore these resources to deepen your understanding of SQLAlchemy and its capabilities:

SQLAlchemy Text Module Guide