Quick Start

This section features a set of code snippets to connect, read, insert, and update information through the SQLAlchemy interface.

Connect and create an engine

Unresolved include directive in modules/developer/pages/python/old/sql_crud.adoc - include::partial$/conn_params_sql.adoc[]

The starting point for connecting SQLALchemy to LeanXcale is creating an engine, which uses a specific dialect to know how to interact with the underlying DBAPI.

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 must define the parameters for the connection URL by setting the username and password as well as the database to which you want to connect. Also, the IP address and port of the LeanXcale server must be configured.

To close the connection, call the following:

engine.dispose()

If you have created specifically a connection calling engine.connect(), you will need to close this connection by yourself.

con = engine.connect()
con.close()

Create a table

To create a table in LeanXcale, run the following code:

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)

Create an index

Several ways are available 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)

Create a sequence

You can create sequences as standalone objects:

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)

Then, you can obtain 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 autoincrement its value. The following code should be run to create a table with a sequence as the primary key, and then insert a new record:

from sqlalchemy import insert
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')

Insert, update and delete

Insert one record

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

Insert many records

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

Update a record by the key

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

Delete a record by the key

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

Read and scan 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)

Create a table with arrays columns

The following example shows how to create a table with arrays columns.

from sqlalchemy import MetaData, Table, Column, Integer, String, Float, BigInteger, VARCHAR, ARRAY

metadata = MetaData()
metadata.bind = engine
table = Table('T_array', metadata,
    Column( 'id', Integer, primary_key=True),
    Column('arryBigInt', ARRAY(BigInteger)),
    Column('arryFloat', ARRAY(Float)),
    Column('arryVARCHAR', ARRAY(VARCHAR)),
    )

# create all elements in metadata objects
metadata.create_all(engine)
con = engine.connect()
con.close()

Insert and update arrays data

The following are examples of how to insert arrays data to a table.

  • Insert many records at the same time

from sqlalchemy import insert, LXARRAY

conn = engine.connect()
conn.execute(table.insert(), [
      {'id': 1, 'arryBigInt': LXARRAY([35656222554887711, 1, -89], int), 'arryFloat': LXARRAY([1.10, -69.48], float), 'arryVARCHAR': LXARRAY(['Cartagena', 'Almería'], str)},
	  {'id': 2, 'arryBigInt': LXARRAY([100, -270, 150000000], int), 'arryFloat': LXARRAY([1.0, -3.1415,1.67], float), 'arryVARCHAR': LXARRAY(['Barcelona', 'Cádiz', 'Valencia'], str)}
])
con = engine.connect()
con.close()

  • Insert a record with SQL syntax

sqlInsert = "upsert into T_array values(3, array[-45,1234567890], array[0.5,9.55], array['Salamanca','Toledo','Alicante'])"
con = engine.connect()
con.execute(sqlInsert)
con = engine.connect()
con.close()

  • Insert a record with array data by filling all the parameters

from sqlalchemy import insert, LXARRAY

con = engine.connect()
engine.execute(table('T_array', column('id'), column('arryBigInt'), column('arryFloat'), column('arryVARCHAR')) \
			.insert().values({'id':4, 'arryBigInt':LXARRAY([300000,10], int), 'arryFloat':None, 'arryVARCHAR':LXARRAY(['Tenerife', 'Menorca'], str)}))
con = engine.connect()
con.close()

This is another way to insert many records at once. In this example we also show two ways to insert null values.

from pyLeanxcale.types import LXARRAY

sqlInsertMany = "upsert into T_array values(?,?,?,?)"

arrayInts = LXARRAY([], int)
arrayFloats = LXARRAY([], float)
arrayStr = LXARRAY([], str)

con_raw = engine.raw_connection()
params = [
    (5, LXARRAY([10783654298645], int), LXARRAY([1.0, 1.75], float), LXARRAY(['Valladolid'], str)),
    (6,LXARRAY([9265894128625,-2], int), LXARRAY([-1.0,2.0], float), LXARRAY(['Torrevieja','La Manga'], str)),
    (7,None,None,None)
]
con_raw.cursor().executemany(sqlInsertMany, params)
con_raw.cursor().execute(sqlInsertMany, (8, arrayInts, arrayFloats, arrayStr))
print("Inserted")
con_raw.close()

This example shows how to update an array field of a record by the key.

from sqlalchemy import update, LXARRAY

stmt = update(table).where(table.c.id == 1).values(arryVARCHAR=LXARRAY(["Jaén","Madrid"], str))
stmt.compile()
conn.execute(stmt)
conn.close()

Read and scan arrays data

The following examples show how to read data from a table with arrays columns.

In this example we show how to read data from a table using the SQL syntax.

sqlSelect = "SELECT id, TO_string(arryBigInt), TO_string(arryFloat), TO_string(arryVARCHAR) FROM T_ARRAY where id=1"
con = engine.connect()
rs = con.execute(sqlSelect)
for row in rs:
  print(row[0])	#1
	print(row[1])	#[35656222554887711, 1, -89]
	print(row[2])	#[1.10, -69.48]
	print(row[3])	#['Jaén', 'Madrid']

This example shows how to scan records by a key value.

from sqlalchemy import select
sel = select([table.c.id, table.c.longs]).where(table.c.id == 5)
sel.compile()
result = conn.execute(sel)
for row in result:
  print(row[0]) #5
	print(row[1]) #[10783654298645]
	print(row[2]) #[1.0, 1.75]
	print(row[3]) #['Valladolid']

Select query to join 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)

DBAPI Interface

This SQLAlchemy driver is based on the PEP 249 – Python Database API Specification v2.0. This API can be accessed and used to interact directly against the LX Database.

Create a connection

We can just create a DBAPI connection from the engine object.

con = engine.raw_connection()

Operate

Once the connection is created, we can perform different actions. Here, for INSERT oprations, we can decide whether to use single execute or perform several executes in a batch mode. In the typical scenario where the roundtrip time between client and server is the main bottleneck, we will benefit from this second option.

Single operation

cursor = con.cursor()
cursor.execute('select * from table')
for row in cursor.fetchall():
    print(row)

Batch operation

As previously said, we can only use batch operations with INSERT like operations. The second argument is a list of tuples with all the parameters for the SQL INSERT.

cursor = con.cursor()
cursor.executemany("INSERT INTO TABLE VALUES (?)", [(1),(2),(3)])

Close Connection

It is very important to close the connection once it is not going to be used more or in a long period of time. In order to close it, just:

con.close()