Skip to main content

SQL Python tutorial

SQL quick start tutorial for Python programmers

FairCom_DB_Horizontal_logo.svg

This tutorial helps Python programmers interact with FairCom servers.

Note

The <root> directory for this driver is <faircom>\drivers\python.sql\

  1. Install and start up the FairCom server if it is not currently running.

  2. Install FairCom Python if not already installed.

  3. Get started immediately with the source code found in the <faircom>\drivers\python.sql\tutorials directory, or proceed with the following instructions.

Introduction to FairCom SQL for Python

FairCom has implemented a pure Python module called "pyctree" that interfaces with FairCom SQL Server through the well-known ctypes module. This architecture has been chosen because it makes the module immediately available for multiple Python implementations, such as PyPy and Jython, and it does not require a C compiler to install the extension.

Pyctree consists of two parts both completely written in Python:

  • pyctsqlapi.py - A wrapper library over the DSQL API

  • pyctree.py - A DB-API 2.0 implementation conforming to Python PEP 249 standards.

FairCom DB also supports Python SQLAlchemy.

FairCom Python installation

Install pyctree on your system.

  1. Open a Unix shell or the Windows command prompt.

  2. Move to the <faircom>\drivers\python.sql directory and run:

    python setup.py install

    Note

    On Linux/Unix, you may need to "sudo" or "su" first. On Windows, if you get a "Permission denied" error, open a command prompt as Administrator and try again.

  3. Place the ctsqlapi shared library (or DLL in Windows) in a place where the Python executable can load it (for example: LD_LIBRARY_PATH on some versions of Unix).

Notes:

  • The FairCom pyctree interface (the Python DB API 2.0-compliant driver) is compatible with Python versions 2.6 and 3.x.

  • Support for the following data types has not been implemented: BINARY, VARBINARY, LONGVARBINARY, LONGVARCHAR and UNICODE fields.

  • The following features do not conform to the PEP 249 standard:

    Auto Commit - The autocommit read/write property of the Connection class allows the programmer to turn on/off the autocommit capability.

    Query Timeout - The querytimeout read/write attribute of the Cursor class can be set to 0 (default) to indicate no timeout or to a positive value to specify a query timeout in seconds.

  • The provided FairCom SQL libraries are linked with OpenSSL support. Due to the link order of the libraries, some platforms may require specific run-time library pre-load instructions. For example, on some Linux distros:

    export LD_LIBRARY_PATH=../../ctree.drivers/lib

    LD_PRELOAD=/usr/lib/libssl.so python PY_tutorial2.py

Best practice dictates LD_PRELOAD should only specify full paths to your libssl.so library, which may reside in different locations on different Unix/Linux distros.

Unicode Support

The Python driver has been enhanced to detect the nature of the ctsqlapi library in use (Unicode verses non-Unicode) and consider this when calling into the library. The driver can now be used with a Unicode server as well as non-Unicode servers.

SSL security options

The default connection string provided in the FairCom Python tutorials does not use SSL security. The following connection strings provide various levels of SSL support:

No SSL Connection. This is the default connection string provided in the tutorials:

conn = pyctree.connect( host='localhost', port='6597', user='admin', password='ADMIN', database='ctreeSQL' )

Equivalent:

conn = pyctree.connect( host='localhost', port='6597', user='admin', password='ADMIN', database='ctreeSQL', ssl=None )

SSL without certificate checking:

conn = pyctree.connect( host='localhost', port='6597', user='admin', password='ADMIN', database='ctreeSQL', ssl='BASIC' )

SSL with certificate checking; certificate file as specified relative to the client working directory:

conn = pyctree.connect( host='localhost', port='6597', user='admin', password='ADMIN', database='ctreeSQL', ssl='<certificate file name>' )

SSL with certificate checking; certificate named ctsrvr.pem in client working directory:

conn = pyctree.connect( host='localhost', port='6597', user='admin', password='ADMIN', database='ctreeSQL', ssl='' )

SSL Support Parameter

A new optional parameter has been added to the "connect" function as follows:

connect(**kw_args)

Constructor for creating a connection to the database.

Returns a Connection Object.

It takes parameters as keyword parameters for more intuitive use as follows:

  • user - User name as string (optional default guest)

  • password - Password as string (optional default None)

  • host - Hostname (optional default localhost)

  • database - Database name (optional default ctreeSQL)

  • port - TCP/IP port number (optional default 6597)

  • ssl - None: No SSL, (optional default None)

    'BASIC': SSL without certificate checking,

    '<certificate file name>': SSL with certificate checking, certificate file as specified,

    '' (two single quotation marks): SSL with certificate checking, certificate in current working dir named ctsrvr.pem

Tutorial walkthrough

This quick start has two programs to demonstrate table relationships and transactions. They are located in the <faircom>\drivers\python.sql\tutorials directory.

PY_tutorial2.py - This tutorial demonstrates relational tables and indexing.

PY_tutorial4.py - This tutorial demonstrates transaction processing.

You can run the tutorials from Python. For example, to execute tutorial 2, type the following on a command-line:

python PY_tutorial2.py

PY_tutorial2.py: Relationships

This tutorial establishes some table relationships between tables. It creates a simple order system with customer, order, order detail, and product tables. It inserts records in the tables and runs a query that lists each customer's name and the total amount of each order.

tut2.svg

  1. Initialize()

    Connects to the FairCom Database Engine.

  2. Define()

    Creates "custmast", "custordr", "ordritem" and the "itemmast" tables with related indexes.

  3. Manage()

    1. Adds records to each of these tables.

    2. Lists each customer's name and the total amount of each order.

  4. Done()

    Disconnects from FairCom Database Engine.

PY_tutorial4.py: Use transactions

This tutorial shows how to process transactions in a simple order system with customer, order, order detail, and product tables. With transaction processing, related updates are either all completed or none of them are completed which ensures data integrity.

  1. Initialize()

    Connects to the FairCom Database Engine.

  2. Define()

    Creates "custmast", "custordr", "ordritem" and the "itemmast" tables with related indexes.

  3. Manage()

    Adds records/rows to multiple tables under transaction control.

  4. Done()

    Disconnects from FairCom Database Engine which releases resources.

ASCII Characters above 127

When using Python with extended ASCII characters (those with ASCII codes above 127), the default encoding in Python may result in errors trying to interpret FairCom SQL data.

The procedure below applies only to the Python driver. It is not necessary when using SQLAlchemy.

To properly display these characters, follow these steps:

  1. Locate these lines in the pyctsqlapi.py module:

    return str(s).encode()

    return s.decode()

  2. Change the two lines shown above to the following, respectively:

    return str(s).encode('ansi',errors = 'strict')

    return s.decode('ansi',errors = 'strict')

  3. Reinstall the module by executing the following:

    py setup.py install

Your FairCom data will be properly displayed.

See also