Python Driver¶

Installing and Configuring Python¶

easy_install CUBRID-Python

Python Programming¶

The CUBRIDdb package is supposed to have the following constants according to Python Database API 2.0.

Python Sample Program¶

This sample program will show steps that you need to perform in order to connect to the CUBRID database and run SQL statements from Python programming language. Enter the command line below to create a new table in your database.

csql -u dba -c "CREATE TABLE posts( id integer, title varchar(255), body string, last_updated timestamp );" demodb

Connecting to demodb from Python

1. Open a new Python console and enter the command line below to import CUBRID Python driver.

   import CUBRIDdb
   

2. Establish a connection to the demodb database located on localhost.

   conn = CUBRIDdb.connect('CUBRID:localhost:30000:dba::')
   

For the demodb database, it is not required to enter any password. In a real-world scenario, you will have to provide the password to successfully connect. The syntax to use the connect () function is as follows:

connect (url[,user[password]])

If the database has not started and you try to connect to it, you will receive an error such as this:

Traceback (most recent call last):
  File "tutorial.py", line 3, in <module>
    conn = CUBRIDdb.connect('CUBRID:localhost:30000:dba::')
  File "/usr/local/lib/python2.6/site-packages/CUBRIDdb/__init__.py", line 48, in Connect
    return Connection(*args, **kwargs)
  File "/usr/local/lib/python2.6/site-packages/CUBRIDdb/connections.py", line 19, in __init__
    self._db = _cubrid.connect(*args, **kwargs)
_cubrid.Error: (-1, 'ERROR: DBMS, 0, Unknown DBMS Error')

If you provide wrong credentials, you will receive an error such as this:

Traceback (most recent call last):
  File "tutorial.py", line 3, in <module>
    con = CUBRIDdb.connect('CUBRID:localhost:33000:demodb','a','b')
  File "/usr/local/lib/python2.6/site-packages/CUBRIDdb/__init__.py", line 48, in Connect
    return Connection(*args, **kwargs)
  File "/usr/local/lib/python2.6/site-packages/CUBRIDdb/connections.py", line 19, in __init__
    self._db = _cubrid.connect(*args, **kwargs)
_cubrid.Error: (-1, 'ERROR: DBMS, 0, Unknown DBMS Error')

Executing an INSERT Statement

Now that the table is empty, insert data for the test. First, you have to obtain a cursor and then execute the INSERT statement.

cur = conn.cursor()
cur.execute("INSERT INTO posts (id, title, body, last_updated) VALUES (1, 'Title 1', 'Test body #1', CURRENT_TIMESTAMP)")
conn.commit()

The auto-commit in CUBRID Python driver is disabled by default. Therefore, you have to manually perform commit by using the commit () function after executing any SQL statement. This is equivalent to executing cur.execute("COMMIT") . The opposite to executing commit() is executing rollback (), which aborts the current transaction.

Another way to insert data is to use prepared statements. You can safely insert data into the database by defining a row that contains the parameters and passing it to the execute () function.

args = (2, 'Title 2', 'Test body #2')
cur.execute("INSERT INTO posts (id, title, body, last_updated) VALUES (?, ?, ?, CURRENT_TIMESTAMP)", args)

The entire script up to now looks like this:

import CUBRIDdb
conn = CUBRIDdb.connect('CUBRID:localhost:33000:demodb', 'public', '')
cur = conn.cursor()

# Plain insert statement
cur.execute("INSERT INTO posts (id, title, body, last_updated) VALUES (1, 'Title 1', 'Test body #1', CURRENT_TIMESTAMP)")

# Parameterized insert statement
args = (2, 'Title 2', 'Test body #2')
cur.execute("INSERT INTO posts (id, title, body, last_updated) VALUES (?, ?, ?, CURRENT_TIMESTAMP)", args)

conn.commit()

Fetching all records at a time

You can fetch entire records at a time by using the fetchall () function.

cur.execute("SELECT * FROM posts ORDER BY last_updated")
rows = cur.fetchall()
for row in rows:
    print row

This will return the two rows inserted earlier in the following form:

[1, 'Title 1', 'Test body #1', '2011-4-7 14:34:46']
[2, 'Title 2', 'Test body #2', '2010-4-7 14:34:46']

Fetching a single record at a time

In a scenario where a lot of data must be returned into the cursor, you can fetch only one row at a time by using the fetchone () function.

cur.execute("SELECT * FROM posts")
row = cur.fetchone()
while row:
    print row
    row = cur.fetchone()

Fetching as many as records desired at a time

You can fetch a specified number of records at a time by using the fetchmany () function.

cur.execute("SELECT * FROM posts")
rows = cur.fetchmany(3)
for row in rows:
    print row

Accessing Metadata on the Returned Data

If it is necessary to get information about column attributes of the obtained records, you should call the description method.

for description in cur.description:
    print description

The output of the script is as follows:

('id', 8, 0, 0, 0, 0, 0)
('title', 2, 0, 0, 255, 0, 0)
('body', 2, 0, 0, 1073741823, 0, 0)
('last_updated', 15, 0, 0, 0, 0, 0)

Each of row has the following information.

(column_name, data_type, display_size, internal_size, precision, scale, nullable)

For more information about numbers representing data types, see http://packages.python.org/CUBRID-Python/toc-CUBRIDdb.FIELD_TYPE-module.html .

Releasing Resource

After you have done using any cursor or connection to the database, you must release the resource by calling both object's close () function.

cur.close()
conn.close()

Python API¶

Python Database API is composed of connect() module class, Connection object, Cursor object, and many other auxiliary functions. For more information, see Python DB API 2.0 Official Documentation at http://www.python.org/dev/peps/pep-0249/.

You can find the information about CUBRID Python API at http://ftp.cubrid.org/CUBRID_Docs/Drivers/Python/.