Setting advanced options of SQL connection pools
In this article, we will cover the details of how Zato SQL connection pools can be configured to take advantage of features and options specific to a particular driver or to the SQLAlchemy library.
SQL connection pools
First, let's review the basic Zato Dashboard form that creates a new SQL connection pool.
Above, we find options that are common to all the supported databases:
- Name of the connection as it is referenced to in your Python code
- Whether the connection is active or not
- How big the pool should be
- What kind of a database it is (here, Oracle DB)
- Where the database is located - host and port
- What is the name of the database
- The username to connect with (password is changed using a separate form)
More options
The basic form covers the most often required, common options but there is more to it and that comes in two flavors:
- Options specific to the driver library for a given database
- Options specific to SQLAlchemy, which is the underlying toolkit used for SQL connections in Zato
As to how to declare them:
Options from the first group are specified using a query string appended to the database's name, e.g. mydb?option=value&another_option=another_value.
Options from the second group go to the Extra field in the form.
We will cover both of them using a few practical examples.
Specifying encoding in MySQL connections
When connecting to MySQL, there may arise a need to be explicit about the character set to use when issuing queries. This is a driver-level setting so it is configured by adding a query string to the database's name, such as mydb?charset=utf8.
Using a service name when connecting to Oracle DB
Oracle DB connections will at times require a service name alone to connect to, i.e. without a database name. This is also a driver-level option but this time around the query string is the sole element that is needed, for instance: ?service_name=MYDB.
Echoing all SQL queries to server logs
It is often convenient to be able to quickly check what queries exactly are being sent to the database - this time, it is an SQLAlchemy-level setting which means that it forms part of the Extra field.
Each entry in Extra is a key=value pair, e.g. echo=True. If more than one is needed, each such entry is in its own line.
Knowing which options can be set
At this point, you may wonder about how to learn which options can be used by which driver and what can go to the extra field?
The driver-specific options will be in that particular library's documentation - each one will have a class called Connection whose __init__ method will contain all such arguments. This is what the query string is built from.
As for the extra field - it accepts all the keyword arguments that SQLAlchemy's sqlalchemy.create_engine function accepts, e.g. in addition to echo, it may be max_overflow, isolation_level and others.
And that sums up this quick how-to - now, you can configure more advanced SQL options that are specific either to each driver or to SQLAlchemy as such.