CUBRID Python API Documentation

Overview

This tutorial will explain all the steps that you need to perform in order to connect to CUBRID Database and run SQL statements through Python programming language. You can use this API just like JDBC Driver in Java.

Even if the APIs are standard, in order to correctly connect to a certain DBMS (CUBRID, MySQL, Oracle, DB2, MS-SQL, PostgreSQL, etc) you have to use the right module created exactly for that particular database system. The latest version upon composing this tutorial was Python DB API 2.0.

CUBRID-Python is registered as an open source project at NAVER Development Center, and the source code is distributed in an open form, thus, it has to be manually compiled before usage. CUBRID-Python API is compatible with CUBRID 2008 R4.0 and newer, and Python 2.4 or later.

Getting Help

At any time if you feel lost somewhere, you can always visit the main page for Python Tutorial to start from the beginning. Or, if you have any troubles connecting to CUBRID through Python, post your questions to the Python Forum at http://www.cubrid.org/forum.

Preparing the Environment

Preparing CUBRID

1. First thing you have to do is to make sure you have installed CUBRID Database according to all the steps explained in Install CUBRID. If you have not installed CUBRID yet, Download CUBRID and follow the Installation Instructions. In this tutorial I will be using the latest stable release CUBRID 8.4.0. On my machine it is installed in C:\CUBRID. However, you do not have to worry much about it, as the CUBRID Installer automatically adds its location path to the Windows environment.
2. NOTE For Windows users: Make sure you have installed Java Development Kit (JDK) and Microsoft C++ 2008 Redistributable Package.

Preparing Python

On Windows

Install Python

You can download Python Installer for Windows from the Official site http://python.org/download/. At this point you will see me using Python 2.7.

Install Python Driver for CUBRID

In fact, there is no need to install anything. You just have to copy the necessary files to the Lib folder of your Python installation.

1. Download CUBRID-Python Driver for your particular system. The archive you will download will have a ZIP format with a folder and two files in it: CUBRIDdb\__init__.py, CUBRIDdb\connection.py, CUBRIDdb\cursor.py, CUBRIDdb\FIELD_TYPE.py, _cubrid.pyd and _cubrid_exceptions.py (_cubrid_exceptions.py file is not required for cubrid 8.4.3).
2. Extract these files to the Lib folder where your Python has been installed. By default it is C:\Python27\Lib (assuming your python version is 2.7). This is the place where all other third-party libraries are located. Make sure that the paths are kept, meaning that _cubrid.pyd and the CUBRIDdb folders are copied into the C:\Python27\Lib completely (the paths should be C:\Python27\Lib\CUBRIDdb and C:\Python27\Lib\_cubrid.pyd).

Notes: CUBRID-Python driver depends on the CUBRID CCI(CUBRID C Interface) library. When you install CUBRID in "Prepareing CUBRID" step, the CCI library is automatically installed in %CUBRID%\bin. Otherwise, when you run your python scripts to access CUBRID DB, the below error will occur:
        ImportError: DLL load failed: The specified module could not be found.

On Linux

Install Python

Most Linux distributions often come already bundled with Python 2.6.5. However, by default the Development Header Files are usually absent. So, considering that Python 2.6.5 is installed, let's install the Development Package, which is required to build all kinds of extensions, including CUBRID-Python Connector. If you have another version of Python such as 2.7.x you have to install python2.7-x and so on.

1. In our case we have Python 2.6.x installed, so in you terminal we type:
   

   sudo apt-get install python2.6-dev
   

This Development Package is enough to build the Python Driver for CUBRID.

Install Python Driver for CUBRID

Download the Source Code

1. Download the CUBRID-Python source code.
2. Extract the source code to the desired location.
3. Navigate to the directory where you have extracted the files so that there is setup.py file inside.

Install the Connector

1. Build the connector:

   python setup.py build
   

2. Install the connector:

   sudo python setup.py install
   

Example

In this example, we will use posts for a table name. The following is the Schema for it. Likewise, we will create a database called testdb, which will store the posts table. Finally, we will store some random data to this table.

CREATE TABLE posts(
        id integer,
        title varchar(255),
	body string,
	last_updated timestamp
);

The following is the typical code used to execute the database query in CUBRID-Python API. We will use a simple query as an example to connect to the CUBRID Database.

import CUBRIDdb

con = CUBRIDdb.connect('CUBRID:localhost:33000:testdb:::', 'test_user', 'test_passwd')
c = con.cursor()

c.execute('select * from posts')
rows = c.fetchall()

for r in rows:
	print r
	# Python 3.x: put parenthesis after "print" like "print(r)"
 
"""
Below is a sample output retrieved from the "testdb" database.
 
[1, 'aaa', '2006-11-2 23:53:0', 'hello, cubrid']
[2, 'bbb', '2006-11-3 0:14:0', 'good database system']
[3, 'ccc', '2006-11-15 0:5:23', 'happy python']
"""

c.close()
con.close()

The above example is quite straightforward. If simply explained, CUBRID-Python requires to import CUBRIDdb module to get started. According to Python API name conventions, the db postfix is appended after the module name CUBRID, thus forming CUBRIDdb.

After import-ing the necessary API, in our case CUBRID-Python API, we need to establish the database connection. Use CUBRIDdb's connect() function to establish the connection. For more info about the function see the syntax below. As shown in the above example, if username and password is omitted, the DEFAULT values (username: 'PUBLIC', password: - empty password) will be used. If the connection is successfully established, the Connection class instance will be returned.

con.cursor() call returns an object of the Cursor class. All database operations should be performed through the Cursor object. Cursor class has several methods such as execute() or fetchall(). The execute method is used to actually execute the SQL statements, while fetchall() retrieves all the results in tuple format. Each tuple represents a specific row in the database.

Python DB API 2.0

Python DB API is composed of connect() module class, Connection object, Cursor object, and many other auxiliary functions. For more information refer to Python DB API 2.0 Official Documentation.

Below are the documentation of the CUBRID-Python module functions necessary during the interaction with CUBRID database system.

connect

As described in the above example, connect() function attempts to establish the connection with the database server by using connection information passed with a url string argument. If the HA feature is enabled in CUBRID, you must specify the connection information of the standby server, which is used for failover when failure occurs, in the url string argument of this function. If the user name and password is not given, then the "PUBLIC" connection will be made by default. Exclusive use of keyword parameters strongly recommended. Consult the CUBRID CCI documentation for more details.

Syntax

cubriddb.connect(url, user, passwd)

• url =  "CUBRID:host:db_name:db_user:db_password:?properties"
  The properties may be alhosts, rctime, autocommit, login_timeout, query_timeout and disconnect_on_query_timeout.
  althosts=standby_broker1_host,standby_broker2_host, . . . : String. Specifies the broker information of the standby server, which is used for failover when it is impossible to connect to the active server. You can specify multiple brokers for failover, and the connection to the brokers is attempted in the order listed in alhosts.
  rctime : An interval between the attempts to connect to the active broker in which failure occurred. After a failure occurs, the system connects to the broker specified by althosts (failover), terminates the transaction, and then attempts to connect to he active broker of the master database at every rctime. The default value is 600 seconds.
  autocommit : Configures the auto-commit mode. The value maybe true, on, yes, false, off and no.
  login_timeout : Configures the login timeout of CUBRID.
  query_timeout : Configures the query timeout of CUBRID.
  disconnect_on_query_timeout : Make the query_timeout effective. The value maybe true, on, yes, false, off and no.
• user - username, default 'PUBLIC'
• passwd - password, default 

Returns

• Connection class object, if the connection has been successful.
• NULL, otherwise.

Connection object

Connection object maintains the connection with the database server, providing the instance methods such as cursor(), commit(), rollback(), and close().

cursor()

cursor() method returns the object of the Cursor class, which can be used to retrieve the results of the recent query.

commit()

As the name of the function implies, the commit() method commits the latest changes to the database server.

rollback()

rollback() function cancels the latest changes by rolling back the transaction.

close()

close() method disconnects the connection with the database server.

Cursor object

Cursor class object is used to execute the queries and retrieve the results. As described above the Cursor object can be obtained by calling the cursor() method of the Connection class instance.

c = con.cursor()
c.execute('select * from posts')
row = c.fetchone()

print row
# Python 3.x: put parenthesis after "print" like "print(row)"
# Output: {'body': 'hello, cubrid', 'created_at': '2006-11-2 23:53:0', 'id': 1, 'title': 'aaa'}

print row['title']
# Python 3.x: put parenthesis after "print" like "print(row['title'])"
# Output: aaa

execute()

Once you have received the Cursor object, you are ready to execute() the queries.

Syntax

c.execute(SQL ,[, args],[,type])

Parameters

• SQL: The first argument to pass to the execute() function should be the SQL statement itself. It will accept all valid SQL statements that can be handles by the database server.
• args: [Optional] the execute() method can accept the second method in case the SQL was of the prepare type as shown in the example below. In this case, args will be an array representing the values to be replaced in the prepare SQL statement. 

Example

c = con.cursor()
args = (4, 'ddd', 'CUBRID-Python is Open Source!!')

c.execute('insert into posts(id, title, body, created_at) values (?, ?, ?, SYSTIMESTAMP)', args)
con.commit()

c.execute('select * from posts')
rows = c.fetchall()

print rows
# Python 3.x: put parenthesis after "print" like "print(rows)"
#[[!1, 'aaa', '2006-11-2 23:53:0', 'hello, cubrid'], [2, 'bbb', '2006-11-3 0:14:0', 'good database system'], [3, 'ccc', '2006-11-15 0:5:23', 'happy python'], [4, 'ddd', '2006-11-30 22:54:9', 'CUBRID-Python is Open Source!!']]

execute() method will automatically bind the host variables indicated as question marks (?) with the values passed in the args array. In the above example, the value 4 is binded with the first variable, 'ddd' - with the second variable, 'CUBRID-Python is Open Source!!' string is binded with the last third variable.

If  data type of args is tuple,it's mean driver will process the data as set,and type is data type of set's element.Default type is 'string'. The feature is support by version  9.1.0.0003 or later.

Example

con = CUBRIDdb.connect('CUBRID:10.34.64.218:30000:demodb:dba::')
c = con.cursor()

set_val=(('abc','def'),('1','23','48'))
etype = (FIELD_TYPE.CHAR,FIELD_TYPE.INT)

c.execute("CREATE TABLE set_tbl_char_int ( col_1 set(CHAR(5)),col_2  set(int));")
c.execute("insert into set_tbl_int VALUES(?,?)", set_val,etype)
con.commit();

c.close()
con.close()

fetchone()

This method can be used to retrieve one record at a time from the results set.

fetchall()

This method can be used to retrieve all the records that are in the results set.

fetchmany()

This method can be used to retrieve the n number of records at a time from the results set.

Syntax

c.fetchmany([n])

Parameters

• n - [Optional] the number of records to fetch at a time from the results set. If this number is not specified, the fetchmany() function will return as many records as specified by the arraysize attribute of the Cursor object. In case the value n is more than the available number of records in the results set, all the remaining records will be returned.

Example

c = con.cursor()
args = ('B')

c.execute('select * from posts where id = ?', args)
r = c.fetchone()
print r
# Python 3.x: put parenthesis after "print" like "print(r)"
# Output: 'B', 'Bronze'
 
c.execute('select * from posts where id > ?', args)
print c.arraysize
# Python 3.x: put parenthesis after "print" like "print(c.arraysize)"
# Output: 1
 
r = c.fetchmany(3)
print r
# Python 3.x: put parenthesis after "print" like "print(r)"
# Output: ['X', 'Mixed', 'W', 'Woman', 'M', 'Man']

description

If it is necessary to get the information about column properties of the obtained records, description method of the Cursor object should be called.

Example

print c.description
# Python 3.x: put parenthesis after "print" like "print(c.description)"
# Output: (('s_name', 1, 0, 0, 1, 0, 0), ('f_name', 2, 0, 0, 6, 0, 0))

Returns

• arr: an array holding the information about each column of the record. In fact, it is an array of arrays. Each column is described by 7 tuples, i.e. 7 pieces of information. Considering the above example, let's unfold the second column's description.
  
  ('title', 2, 0, 0, 256, 0, 0) has 7 pieces of information.
  1. The name of the column, in this case it is title.
  2. Its data_type = 2 -> VARCHAR (look at the table below for the data type mapping).
  3. display_size = 0 -> for VARCHAR it is not meaningful.
  4. internal_size = 0 -> for VARCHAR it is not meaningful.
  5. precision = 256.
  6. scale = 0 -> for VARCHAR it is not meaningful.
  7. nullable = 0 -> does not allow NULL.

Data Type Mapping

See also

• Ruby Programming with CUBRID
• PHP Tutorial
• API References