DEV Community

Michael
Michael

Posted on • Originally published at gbase8.cn

Using Python with GBase 8a: Connection, CRUD, Stored Procedures, and Data Loading

#gbase #database #数据库 #python

This guide demonstrates how to interact with a gbase database using Python. It covers driver installation, connection setup, DDL, DML, DQL, stored procedure calls, batch inserts, and data loading. All examples use Python 2 and the official gbase-connector-python2-5.0.1 driver.

Driver Installation

Extract the tarball and install the driver:

tar xvf gbase-connector-python2-5.0.1.tar.gz
cd gbase-connector-python2-5.0.1
python setup.py build
python setup.py install
Enter fullscreen mode Exit fullscreen mode

Connecting to the Database 

from GBaseConnector import connect, GBaseError

config = {
    'host': '10.10.10.10',
    'port': 5258,
    'database': 'testdb',
    'user': 'root',
    'passwd': 'PASSWORD_OF_ROOT',
    'connection_timeout': 3600,
    'charset': 'utf8'
}
try:
    conn = connect()
    conn.connect(**config)
    print conn
except GBaseError.DatabaseError, err:
    print err
finally:
    conn.close()
Enter fullscreen mode Exit fullscreen mode

DDL Operations 

cur = conn.cursor()
cur.execute("CREATE TABLE IF NOT EXISTS testPython(id INT)")
Enter fullscreen mode Exit fullscreen mode

DML Operations 

cur.execute("INSERT INTO testPython VALUES(12345678)")
Enter fullscreen mode Exit fullscreen mode

DQL – Querying Data

Use fetchone() or fetchall() for single queries. Always consume the entire result set before executing another query on the same cursor; otherwise, you'll get Unread result found.

cur.execute("SELECT * FROM testPython")
row = cur.fetchone()
print row  # (12345678,)

# Multiple queries
cur.execute("SELECT * FROM testPython")
rows = cur.fetchall()
cur.execute("INSERT INTO testPython VALUES(22222),(33333)")
cur.execute("SELECT * FROM testPython")
rows2 = cur.fetchmany(2)
Enter fullscreen mode Exit fullscreen mode

Executing Multiple SQL Statements at Once

Set multi_stmt=True to run several statements in a single call.

cur.execute("DROP TABLE IF EXISTS testPython; CREATE TABLE IF NOT EXISTS testPython(id INT); INSERT INTO testPython VALUES(12345678)", multi_stmt=True)

# Running multiple SELECTs
rss = cur.execute("SELECT * FROM testPython; SELECT COUNT(*) FROM testPython", multi_stmt=True)
for rs in rss:
    print rs.fetchall()
Enter fullscreen mode Exit fullscreen mode

Batch Insert

Use executemany() for efficient batch inserts.

opfmt = "INSERT INTO testPython2(id, name) VALUES(%s, %s)"
rows = [(i, "row" + str(i)) for i in xrange(0, 100)]
cur.executemany(opfmt, rows)
Enter fullscreen mode Exit fullscreen mode

Stored Procedures 

cur.execute("DROP PROCEDURE IF EXISTS testPython_proc")
cur.execute("CREATE PROCEDURE testPython_proc() BEGIN SELECT 1+2; SELECT now(); END")
rss = cur.callproc("testPython_proc")
for rs in rss:
    print rs.fetchall()
# Output: [(3,)]  [(datetime.datetime(2023, 9, 22, 9, 30, 31),)]
Enter fullscreen mode Exit fullscreen mode

Loading Data

After a LOAD DATA command, inspect cur.info and cur.rowcount to obtain load statistics.

cur.execute("LOAD DATA INFILE 'file://10.10.10.10/home/gbase/py.txt' INTO TABLE testpython2 FIELDS TERMINATED BY ','")
print cur.info          # Task 3237 finished, Loaded 1 records, Skipped 0 records
print("Loaded", cur.rowcount)
infors = cur.info.split()
print("TaskId", infors[1])
print("Skipped", infors[7])
Enter fullscreen mode Exit fullscreen mode

With these examples, you can quickly get started using Python with GBASE's GBase 8a for data processing and automation tasks.

Top comments (0)