Python

1. SQLAlchemy Driver

SQLAlchemy is one of the most popular frameworks for database manipulation on Python. By using a custom dialect from LeanXcale, shipped with the driver, we can make use of SQLAlchemy toolkit alongside LeanXcale, by interacting through our implementation of the well known PEP 249 — Python Database API Specification v2.0.

1.1. Requirements

Python 3

2. SQLAlchemy resources

There is a lot of information in the official documentation of SQLAlchemy, so in this documentation we will provide some examples to perform basic operations over a LeanXcale database.

3. SQLAlchemy Installation

In this section you will find the steps to install Leanxcale SQLAlchemy driver.

LeanXcale SQLAlchemy driver comes in two flavours at the time on installing, you can install the wheel package using pip or you can use Easy Install to install the egg package. Download the chosen type of the Python SQLAlchemy driver from the Drivers page.

As a general recommendation, we recommend that you work with virtual environments, to properly manage and avoid conflicts in the versions of the dependencies.

There are two alternatives for managing the dependencies: you can manage the dependencies yourself by downloading the required driver from the Drivers page or you can have PIP or Easy Install manage the dependencies and install the driver using LeanXcale public repository.

3.1. PIP Installation

3.1.1. Unmanaged Installation

If you decided to download manually the driver and install it using pip3 you have to:

pip3 install pyLeanXcale-1.5.4-py3-none-linux_x86_64.whl

And this will install all the necessary dependencies required for the driver.

3.1.2. Managed Installation

On the other hand, you can add the LeanXcale public repository to your PIP configuration and let PIP take care of finding and installing the required dependency. On your environment, create a pip .conf file with the following content:

[global]
index = https://nexus.leanxcale.com/repository/pypi-releases/pypi
index-url = https://nexus.leanxcale.com/repository/pypi-releases/simple

Then, to install the driver you just need to:

pip3 install pyLeanxcale==1.5.4

3.2. Easy Install Installation

3.2.1. Unmanaged Installation

In the same way than the unmanaged installation using PIP, it is possible to download manually the .egg LeanXcale python driver and install it using Easy Install.

For installing the driver using Easy Install you have to:

easy_install pyLeanXcale-1.5.4-py3-none-linux_x86_64.egg

3.2.2. Managed Installation

If using Easy Install to install the driver from the public repository, you need to create a setup.cfg file in your environment with the following content:

[easy_install]
index-url = https://nexus.leanxcale.com/repository/pypi-releases/simple

Then, to install it just run:

easy_install pyLeanxcale==1.5.4

4. Quick Start

In this section you will find a set of code snippets to connect, read, insert and update information through the SQLAlchemy interface.

4.1. Connecting and creating an engine

Connecting to the SQL interface of requires some basic information:

If you are using LeanXcale as a Cloud Service, you can find the IP or hostname for your instance in the LeanXcale Cloud Console.
If you’re using LeanXcale on-premise, you can use the information from your installation.

In our examples we will use these parameters:

IP / hostname: 123.45.67.89
Port: 1529
DB name: dbtest
Username: user1
Password: pass4user1

The starting point when connecting with SQLALchemy to LeanXcale is creating an engine. The engine makes use of the specific dialect to know how to interact with the DBAPI below it.

from sqlalchemy import create_engine

leanxcale_IP = '123.45.67.89'
leanxcale_port = '1529'
database = 'dbtest'
user = 'user1'
pwd = 'pass4user1'

url = 'leanxcale://' + user + ':' + pwd + '@' + leanxcale_IP +':' + leanxcale_port +'/' + database

#Autocommit is by default True, can be set as a parameter
#Parellelism is by default False, can be set as a parameter

url = 'leanxcale://' + user + ':' + pwd + '@' + leanxcale_IP +':' + leanxcale_port +'/' + database + '?autocommit=False&parallel=True'

engine = create_engine(url)

To configure the engine you have to define the parameters for the connection URL. You have to set the username and password as well as the database to connect. Also, the IP address and port of the LeanXcale server must be configured.

LeanXcale database can work in secure mode using TLS protocol. If your database is using the secure mode, you must specify the secure option when creating the engine as a connection argument, in the following way. By adding this option, your client will use HTTPS offering a secure communication.

engine = create_engine(url, , connect_args={'secure': True})

To close the connection, you need to do the following.

engine.dispose()

4.2. Creating a table

In order to create a table in LeanXcale, you should do the following.

from sqlalchemy import MetaData, Table, Column, Integer, String

metadata = MetaData()
metadata.bind = engine
table = Table(
      'test_table', metadata,
      Column('id', Integer, primary_key=True),
      Column('name', String)
)
# create all elements in metadata objects
metadata.create_all(engine)

4.3. Creating an index

There are several ways to create an index.

from sqlalchemy import Index

#index creation as a column parameter configuration

table_index_1 = Table(
      'test_table_index_1', metadata,
      Column('id', Integer, primary_key=True),
      Column('name', String, index=True)
)

#index creation as a separate object when table creation

table_index_2 = Table(
      'test_table_index_2', metadata,
      Column('id', Integer, primary_key=True),
      Column('name', String),
      Index('index_2_name', 'name')
)

#index creation after table creation

table_index_3 = Table(
      'test_table_index_3', metadata,
      Column('id', Integer, primary_key=True),
      Column('name', String),
)
# create specific table
table_index_1.create(engine)
table_index_2.create(engine)
table_index_3.create(engine)

index_3 = Index('index_3_name', table_index_3.c.name)
index_3.create(engine)

4.4. Creating a sequence

You can create sequences standalone.

from sqlalchemy import sequence
#assume an engine created and a metadata object binded to engine
seq = Sequence('test_sequence', metadata=metadata)
metadata.create_all(engine)

And get values from the created sequence.

nextid = engine.connect().execute(seq)

On the other hand, you can create table columns as sequences and have an implicit field autoincrementing its value. You should do the following to create a table with a sequence as primary key and insert a record.

 table_sequence = Table(
            'table_sequence', metadata,
            Column('id', Integer, Sequence('table_sequence_seq'),
                   primary_key=True),
            Column('name', String)
        )
        metadata.create_all(engine)

        conn = engine.connect()
        query = insert(table_sequence).values(name='John')

4.5. Inserting, updating and deleting

4.5.1. Insert one record

from sqlalchemy import insert
conn = engine.connect()
query = insert(table).values(id=1, name='John')
query.compile()
conn.execute(query)

4.5.2. Insert many records

conn.execute(table.insert(), [
      {'id': 2, 'name': 'Bob'},
      {'id': 3, 'name': 'Alice'},
      {'id': 4, 'name': 'Matt'}
])

4.5.3. Update a record by key

from sqlalchemy import update
stmt = update(table).where(table.c.id == 1).values(name='John F')
stmt.compile()
conn.execute(stmt)

4.5.4. Delete a record by key

from sqlalchemy import delete
stmt = delete(table).where(table.c.id == 4)
stmt.compile()
conn.execute(stmt)

4.6. Reading and scanning data

from sqlalchemy import select
sel = select([table.c.id, table.c.name]).where(table.c.id == 1)
sel.compile()
result = conn.execute(sel)
for row in result:
      print(row)

4.7. Select joining two tables

from sqlalchemy import join

# Equivalent join to
# SELECT *
# FROM students JOIN addresses ON students.id = addresses.st_id

j = students.join(addresses, students.c.id == addresses.c.st_id)
stmt = select([students, addresses]).select_from(j)
result = conn.execute(stmt)
result.fetchall()
for row in result:
      print(row)

5. Other Examples

We have more detailed examples and real-world scenarios available on our blog.

Tweet Sentiment Analysis on a LeanXcale database. Explore how you can perform analytical queries using SQL with Python to later use natural language processing (NLP) techniques to perform sentiment analysis on tweets.