Python Data Persistence – SQLAlchemy ORM

Python Data Persistence – SQLAlchemy ORM

The first step is to connect to a database by using the create_engine () function in sqlalchemy module. This function should be provided with the URL of the database. The easiest way is to connect to an in-memory SQLite database.


>>> from sqlalchemy import create_engine
>>> engine=create_engine('sqlite:///:memory:')

To connect to a SQLite database file use URL similar to following: engine =create_engine(‘sqlite:///mydb.sqlite’)

As you know, the Python library has in-built support for SQLite in the form of a DB-API compatible sqlite3 module. However, for other databases, their respective module needs to be installed. In order to connect to a different database (other than SQLite), its corresponding connection string includes the dialect and module. The general format of use of the create_engine () function is as follows:

dialect+driver://username:password®host:port/ database

Hence, to connect to a MySQL database using pymysql module, we need to use the following statement:

engine = create_engine('mysql+pymydsql://root@ localhost/mydb')

This assumes that the MySQL server’s username is ‘roof with no password set. The create_engine () function returns Engine object. It represents the interface to the database. The ORM doesn’t use the Engine directly once created but is used behind the scenes. This function can accept the optional ‘echo’ argument which is False by default. If set to True, it causes the generated SQL to be displayed by the Python interpreter.

>>> engine=create_