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.

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()

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()

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()

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()

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()

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.