Zato Blog: Setting advanced options of SQL connection pools

In this article, we cover 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 flavours:

• 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.

Next steps

• Start the tutorial to learn more technical details about Zato, including its architecture, installation and usage. After completing it, you will have a multi-protocol service representing a sample scenario often seen in banking systems with several applications cooperating to provide a single and consistent API to its callers.

• Visit the support page if you would like to discuss anything about Zato with its creators

• Para aprender más sobre las integraciones de Zato y API en español, haga clic aquí

from Planet Python
via read more