1. How to Install the Driver and Write a CRUD

1.1. Introduction

LeanXcale is a 2003 ANSI SQL database. LeanXcale provides fast inserting/updating, real-time aggregation, and linear and transactional horizontal scalability.

LeanXcale provides a DBAPI driver based on PEP 249 – Python Database API Specification v2.0. to access. The following examples show us a quick start for PYTHON developers.

In this documentation, we focus on providing practical examples for performing fundamental operations on a LeanXcale database.

1.2. Driver Deploy

In order to connect to LeanXcale you can connect through ODBC by using Python ODBC Bridge:

pip3 install pyodbc

You would need to install Leanxcale’s ODBC Driver first. Check Linux

1.3. Connect

In order to establish a connection to the LeanXcale, you need to provide an ODBC’s DSN:

import pyodbc
cnxn = pyodbc.connect('DSN=LX_DSN')

1.4. Create, Alter, Drop a Table

For the creation, modification, or deletion of a database object, such as a table or sequence, use the execute() method.

1.4.1. Create

Below, you’ll find an example of how to create a table:

createTable = "CREATE TABLE person (
            id BIGINT NOT NULL,
            name VARCHAR,
            lastName VARCHAR,
            birth_date DATE,
            passport VARCHAR,
            phone VARCHAR,
            PRIMARY KEY (id)
            )"
try:
    cursor = conn.cursor()
    cursor.execute(createTable)
    conn.commit()
except Exception as e:
    print("Error:", e)
finally:
    cursor.close()

1.4.2. Alter

This is how you modify the table previously created:

alterTable = "ALTER TABLE person (ADD PARTITION(id) FOR VALUES(100)"
try:
    cursor = conn.cursor()
    cursor.execute(alterTable)
    conn.commit()
except Exception as e:
    print("Error:", e)
finally:
    cursor.close()

1.4.3. Drop

Then, here’s the process for removing the table created:

dropTable = "DROP TABLE person"
try:
    cursor = conn.cursor()
    cursor.execute(alterTable)
    conn.commit()
except Exception as e:
    print("Error:", e)
finally:
    cursor.close()

1.5. Insert, Select, Update, and Delete rows

For data manipulation tasks, encompassing operations like inserting, updating, or deleting records, the tool at your disposal is the executeUpdate() method. This method serves the purpose of executing these SQL statements, and in turn, it provides valuable feedback - specifically, the count of rows influenced by your insert, update, or delete action.

1.5.1. Insert

This is how you add a new record:

try:
    cursor = conn.cursor()
    sql = "INSERT INTO person (id, name, lastName, birth_date, passport, phone) VALUES (%s, %s, %s, %s, %s, %s)"
    cursor.execute(sql, (id_to_insert, name_to_insert, lastName_to_insert, birth_date_to_insert, passport_to_insert, phone_to_insert))
    conn.commit()
    print("Row inserted successfully")
except Exception as e:
    print("Error:", e)
finally:
    cursor.close()

1.5.2. Upsert

LeanXcale provides upserts. Unlike traditional inserts, upserts do not perform a prior check for record existence. Instead, they directly proceed to either insert or update the description, resulting in faster performance.

try:
    cursor = conn.cursor()
    sql = "UPSERT INTO person (id, name, lastName, birth_date, passport, phone) VALUES (%s, %s, %s, %s, %s, %s)"
    cursor.execute(sql, (id_to_insert, name_to_insert, lastName_to_insert, birth_date_to_insert, passport_to_insert, phone_to_insert))
    conn.commit()
    print("Row inserted successfully")
except Exception as e:
    print("Error:", e)
finally:
    cursor.close()

1.5.3. Select

When interacting with the database using SQL statements, leverage either a Statement or PreparedStatement instance. Once you have prepared the Statement or PreparedStatement, execute the query, which will return a ResultSet instance. This ResultSet instance allows you to retrieve the complete result of your query.

try:
    cursor = con.cursor()
    cursor.execute('select * from person WHERE lastName = "Doe"')
    for row in cursor.fetchall():
        print(row)
except Exception as e:
    print("Error:", e)
finally:
    cursor.close()

1.5.4. Join

In this case, it collects data from several tables by executing a single query:

try:
    cursor = con.cursor()
    cursor.execute('SELECT title, name, isbn FROM author INNER JOIN book ON author.id = book.authorid ')
    for row in cursor.fetchall():
        print(row)
except Exception as e:
    print("Error:", e)
finally:
    cursor.close()

1.5.5. Update

Here is how to update existing records:

try:
    cursor = conn.cursor()
    sql = "UPDATE person SET passport = '666668888' WHERE id in (100, 101)"
    cursor.execute(sql)
    conn.commit()
except Exception as e:
    print("Error:", e)
finally:
    cursor.close()

1.5.6. Delete

This is the procedure for deleting records:

try:
    cursor = conn.cursor()
    sql = "DELETE FROM person WHERE id = %s"
    cursor.execute(sql, (id_to_delete,))
    rows_deleted = cursor.rowcount
    conn.commit()
    print(f"{rows_deleted} rows deleted")
except Exception as e:
    print("Error:", e)
finally:
    cursor.close()

These are the fundamental operations you can perform with the LeanXcale JDBC driver. For more advanced functionality and the optimization of your database interactions according to your specific requirements, we encourage you to delve deeper into LeanXcale’s capabilities:

  • On "How to Ingest Effectively" you will find a guide on effective data ingestion using LeanXcale.

  • "How to Read Optimally" will teach you how to maximize reading efficiency.

  • Refer to the Advance Deployment section for insights into specific details related to Python and its integration with LeanXcale.

  • You can access the comprehensive SQL reference documentation here.