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 −

Sr.No. Method & Description
1

connect()

Returns connection object

2

execute()

Executes a SQL statement construct

3

begin()

Returns a context manager delivering a Connection with a Transaction established. Upon successful operation, the Transaction is committed, else it is rolled back

4

dispose()

Disposes of the connection pool used by the Engine

5

driver()

Driver name of the Dialect in use by the Engine

6

table_names()

Returns a list of all table names available in the database

7

transaction()

Executes the given function within a transaction boundary

Advertisements