SQLAlchemy Core - Connecting to Database

In the previous chapter, we have discussed about expression Language in SQLAlchemy. Now let us proceed towards the steps involved in connecting to a database.

Engine class connects a Pool and Dialect together to provide a source of database connectivity and behavior. An object of Engine class is instantiated using the create_engine() function.

The create_engine() function takes the database as one argument. The database is not needed to be defined anywhere. The standard calling form has to send the URL as the first positional argument, usually a string that indicates database dialect and connection arguments. Using the code given below, we can create a database.

>>> from sqlalchemy import create_engine
>>> engine = create_engine('sqlite:///college.db', echo = True)

For a MySQL database, use the below command −

engine = create_engine("mysql://user:pwd@localhost/college",echo = True)

To specifically mention DB-API to be used for connection, the URL string takes the form as follows −

dialect[+driver]://user:password@host/dbname

For example, if you are using PyMySQL driver with MySQL, use the following command −

mysql+pymysql://<username>:<password>@<host>/<dbname>

The echo flag is a shortcut to set up SQLAlchemy logging, which is accomplished via Python’s standard logging module. In the subsequent chapters, we will learn all the generated SQLs. To hide the verbose output, set echo attribute to None. Other arguments to create_engine() function may be dialect specific.

The create_engine() function returns an Engine object. Some important methods of Engine class are −