SQLAlchemy is a powerful SQL tool for Python that provides developers with a wide range of features for designing and managing high-performance databases. In this article, we will briefly cover how to use SQLAlchemy and then delve deeper into executing raw SQL statements in the Python domain language.

Execute Raw SQL Queries in SQLAlchemy

• To get started, you will need to install SQLAlchemy. Once it is installed, you can start using it in Python. To do this, import the SQLAlchemy library and a few other necessary modules:

#import necessary libraries
import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey
from sqlalchemy import inspect

• Check that SQLAlchemy was properly installed and imported by calling sqlalchemy.__version__.

print (sqlalchemy.__version__)

1.4.46

• Next, we will create a new table using SQLAlchemy’s SQL Expression Language. The table will be defined using metadata that contains a number of related modules:

metadata = MetaData()
employee= Table('employee', metadata,
  Column('id', Integer, primary_key=True),
  Column('name', String),
  Column('designation', String),
)

engine = create_engine('sqlite:///employee.db')
metadata.create_all(engine)

Here, we define a new table called the employee and its columns. The metadata object is then passed into the Table method to create the table. A database engine is created using the create_engine function, which is used to connect to the database. In this example, we use a SQLite database, but you can also use other engines such as MySQL or PostgreSQL.

• Finally, to verify that the table was created successfully, use the inspect module to view the columns of the table:

inspector = inspect(engine)
inspector.get_columns('employee')

The output will be:

[{'name': 'id',
  'type': INTEGER(),
  'nullable': False,
  'default': None,
  'autoincrement': 'auto',
  'primary_key': 1},
{'name': 'name',
  'type': VARCHAR(),
  'nullable': True,
  'default': None,
  'autoincrement': 'auto',
  'primary_key': 0},
{'name': 'designation',
  'type': VARCHAR(),
  'nullable': True,
  'default': None,
  'autoincrement': 'auto',
  'primary_key': 0}]

• Now that we have the basics in place, we can start executing raw SQL statements in SQLAlchemy. There are two methods for doing this: using the text module or using the execute method.

1. Text Method: To use the text module, you would import the module, connect to the engine, define the text SQL statement string, and then use the .execute method to run it:

from sqlalchemy.sql import text

with engine.connect() as connect:

    data = ( { "id": 1, "name": "H Travis", "designation": "Data Analyst" },
            { "id": 2, "name": "Mark Buttler", "designation": "Senior Support Executive" },
    )

    statement = text("""INSERT INTO employee(id, name, designation) VALUES(:id, :name, :designation)""")

    for line in data:
        connect.execute(statement, **line)

2. Execute Method: Alternatively, you can skip using the text module and directly pass a raw SQL string to the .execute method:

with engine.connect() as connect:
    result = connect.execute("SELECT * FROM employee")
    for res in result:
        print(res)

The output will be:

(1, 'H Travis', 'Data Analyst')
(2, 'Mark Buttler', 'Senior Support Executive')

With these basics in place, you are now equipped to start using SQLAlchemy to execute raw SQL statements in Python!

Conclusion

SQLAlchemy is a powerful SQL tool built in Python for designing and managing high-performance databases. It starts with installing SQLAlchemy, followed by importing the required modules and checking its version. The basic functionality of SQLAlchemy is the SQL Expression Language, used to create a metadata object containing related modules that define a new database table. A database engine is then created using the create_engine method, where a connection string is passed to connect to the database. With the engine created, the metadata object is passed to the .create_all() method, which generates the table in the database. SQLAlchemy offers two methods for executing raw SQL statements: using the text module and using the .execute() method. The text module is more readable, as the SQL statement string is defined before execution. The .execute() method skips the text module and directly passes a raw SQL string to the .execute() method for execution.