Setting up a Connection

Connecting to a Database

Let's try connecting to SQLite, a relatively lightweight database.
You can do it as follows:

>>> from sqlalchemy import create_engine
>>> engine = create_engine("sqlite+pysqlite:///:memory:", echo=True, future=True)

• Use the sqlalchemy.create_engine function to create an 'engine' that establishes a connection to the database.
• The first argument is a string URL.
  • Typically, the string URL is structured as dialect+driver://username:password@host:port/database.
    • If you don't specify a driver, SQLAlchemy's default settings will be used.
  • Here, sqlite+pysqlite:///test.db is the string URL.
    • For sqlite, the format follows sqlite://<nohostname>/<path>.
• From the string URL sqlite:///test.db, we can understand the following information:
  • Which database to use (dialect, in this case, sqlite)
  • Which database API (the driver interacting with the database) to use (in this case, pysqlite)
  • How to find the database (in this case, it uses the in-memory feature provided by sqlite)
• Setting the echo parameter to True prints all executed SQL.

Creating an engine doesn't yet attempt an actual connection. The real connection occurs only when a request to perform an operation on the database is received for the first time.