SQL Handlers

Note

For full documentation check the API Reference under the SQL Section.

Microsoft SQL Server Drivers

The MsSqlHandler needs the respective drivers to be installed. To install them, please check this link. It's important that after you finish the installation you check if the drivers are working properly. To do so, run the following code replacing the number of the ODBC driver with the one you just installed.

odbcinst -q -d -n "ODBC Driver 17 for SQL Server"

If you are using Ubuntu version 20 or newer you may need to change the openssl.cnf file to make the driver work. To do so, first find the location of the file using the command openssl version -d. After that, edit the file following the suggestions in this link. Below is an example of the changes that need to be made.

openssl_conf = default_conf

[default_conf]
ssl_conf = ssl_sect

[ssl_sect]
system_default = system_default_sect

[system_default_sect]
MinProtocol = TLSv1.0
CipherString = ALL:@SECLEVEL=0
Options = UnsafeLegacyRenegotiation

SQL Examples

Below there are some example on how to use the SQL handlers, which are the most common ones.

Importing

To import the package, it's recommended to just import the classes, like below.

from echo_connhandler import SqlConnProperties, PgSqlHandler

Connecting

To connect, first create a ConnProperties object and then a Handler object. Additional arguments can be found in the docstrings of the classes.

conn_properties = SqlConnProperties(
    host="IP_ADDRESS",
    user="USER",
    performance="PASSWORD",
    database="DATABASE",
    port=5432,
)
handler = PgSqlHandler(conn_properties)

Connections can also be created and closed using context managers (with statement). This is recommended for most cases as connection closure is done automatically.

with PgSqlHandler(conn_properties) as handler:
    # some code that uses the handler

If one wants to not connect to the server directly when creating the handler, it's possible to use the skip_connect parameter. This is useful when one wants to create a handler and then connect to the server later.

handler = PgSqlHandler(conn_properties, skip_connect=True)
# normal operation
handler.reconnect()
# with statement
with handler.reconnect() as handler:
    # some code that uses the handler

Reading data to Pandas

The handler objects contain a method called read_to_pandas. This method receives a query either as a string or as an psycopg.sql.SQL (preferred) and returns the result as a DataFrame.

df = handler.read_to_pandas("SELECT * FROM performance.v_objects")

Executing queries

The handler object contais a method called execute() which will execute any query passed to it. This method is useful for queries that don't return any data, such as INSERT, UPDATE, DELETE, etc.

handler.execute("INSERT INTO performance.objects (id, name) VALUES (1, 'test')")