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.

Bash

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.

Text Only

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.

Python

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.

Python

conn_properties = SqlConnProperties(
    host="IP_ADDRESS",
    user="USER",
    password="PASSWORD",
    database="DATABASE",
    port=6432,
)
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.

Python

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.

Python

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.

Python

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.

Python

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