Python - CUBRID Tutorial

This tutorial will explain all the steps that you need to perform in order to connect to the CUBRID Database and run SQL statements from Python programming language.
In order to do that, will will need to install the CUBRID driver as a Python module, required to communicate with the CUBRID database. You can download the CUBRID-Python Driver from the link below.
CUBRID-Python API is compatible with CUBRID version 8.4.0 and newer, and supports Python 2.6 or higher.

Prerequisites

In order get through this tutorial you will have to install the following software components:

• CUBRID Database
  Download the latest version of the CUBRID Database for your platform from http://www.cubrid.org/downloads and follow the CUBRID Installation Instructions.
• Python Interpreter
  Download the Python installer for your platform from http://python.org/download/. On most Linux systems, Python can be easily installed using the distribution's package manager. For example:
  
  Debian based distro:
  

  $ sudo apt-get install python
  

  Red Hat based distro:

  $ yum install python
  

• CUBRID-Python Driver
  Download the CUBRID-Python Driver and follow the Installation Instructions.

NOTE for Linux users: In order to build the CUBRID-Python Connector you will have to install the Python header files (distributed in Python Development Package) and the GNU Compiler, gcc. See Install Python Development Package for instructions.

Start the demodb database

Now that everything is set up, let's move on and create a database to connect to from the Python interpreter. When you have installed the CUBRID database, the installer had created a demo database named demodb. Before connecting to this database make sure that the database you are trying to connect to is started. From the console execute cubrid service status:

$ cubrid service status 

@ cubrid master status
++ cubrid master is running.
@ cubrid server status Server demodb (rel 8.4, pid 13468)
 Server demodb (rel 8.4, pid 13468)
@ cubrid broker status
  NAME           PID  PORT  AS  JQ      REQ  TPS  QPS    LONG-T    LONG-Q  ERR-Q
================================================================================
* query_editor 13682 44898   5   0        0    0    0    0/0.0    0/0.0      0
* broker1      13692 44449   5   0        0    0    0    0/0.0    0/0.0      0
@ cubrid manager server status
++ cubrid manager server is running.

The above output displays the Server demodb (rel 8.4, pid 13468) which indicates that our demodb database has been started. If the demodb database is not started, type the following command:

cubrid server start demodb

Create a test TABLE

Let's create in our database a new table to use throughout our tutorial. In the command line type the following command.

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

• csql - The above command starts CUBRID's default SQL Interpreter called CSQL.
• -u dba - Login as a dba user.
• -c - We indicate that we will type the SQL statement directly in the command line. After that our CREATE TABLE statement follows.
• demodb - Execute the SQL statement on the demodb database.

Connect to demodb from Python

Open up a new Python console and let's see how we can connect to the CUBRID database. First, command the Python to import the CUBRID-Python Driver.

import CUBRIDdb

Now let's establish a connection to the demodb database located on 'localhost'.

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

For the demodb database it is not required to enter any password. In a real-world scenario you will have to provide also the password as the last parameter in order to successfully connect.
connect (url[,user[password]])

• url - CUBRID:host:port:db_name:db_user:db_password
• user - Authorized username. The 'demodb' database has a user called 'public' which does not require a password.
• password - Password associated with the username.

In case the database is not started and you try to connect to it, you will receive an exception such as this:

Traceback (most recent call last):
  File "tutorial.py", line 3, in ‹module›
    con = CUBRIDdb.connect('CUBRID:localhost:33000:demodb:::','public')
  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 the 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')

Execute an INSERT Statement

Now that the table is empty, let's insert some data for the test. First we will have to obtain a cursor 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 autocommit in CUBRID-Python Driver is disabled by default. Therefore after we execute any SQL statement we have to manually perform the .commit() command. This is equivalent to executing cur.execute("COMMIT"). The opposite to commit() is the rollback() method which aborts the current transaction.
Another way to insert the data is to execute the prepared INSERT statements. This is the safe method of inserting data into the database. We can do this by defining a tuple that contains the parameters and pass it to the execute method.

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

The Complete Code

Up to now our entire script looks like this:

import CUBRIDdb
conn = CUBRIDdb.connect('CUBRID:localhost:33000:demodb:::', 'd', '')
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)")

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

SELECT data from database

Now that we have some data in our database, let's see how we can execute SELECT statements.

fetchall() records

With the same initialized cursor we can execute plain SQL statements:

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']

fetchone() record

In a scenario where a lot of data must be returned into the cursor, you have also the possibility to fetch only one row at a time from the database and process instead of getting all the result set into memory. You can achieve this with the following code:

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

close() the cursor

Now that we have finished working with the cursors we can close it:

cur.close()

Access metadata on the returned data

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

for description in cur.description:
    print description

The output would be:

('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 these tuples holds the following information:

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

The following ID are associated with data types:

fetchmany() rows at once

In previous examples we saw that we could fetch one ? fetchone() or all records ? fetchall(). There is a third way to allow retrieving an specified number of rows. This is achieved using the cursor's fetchmany() method in the following manner:

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

Clean up

After you have done using any cursor or connection to the database you must release the resources. This is done by calling both object's close() method:

cur.close()
conn.close()

The Complete Example

Below is the complete example used in the above tutorial.

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

# Create the posts table
cur.execute("DROP TABLE IF EXISTS posts");
cur.execute("CREATE TABLE posts( id integer, title varchar(255), body string, last_updated timestamp )");

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

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

# Commit the current pending transaction to database
conn.commit()

# Simple select with fetchall()
cur.execute("SELECT * FROM posts")
rows = cur.fetchall()
for row in rows:
    print row

# Select row-by-row with fetchone()
cur.execute("SELECT * FROM posts")
row = cur.fetchone()
while row :
    print row
    row = cur.fetchone()
cur.close()
conn.close()

Getting Help

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

See also

• Python Programming with CUBRID
• CUBRID-Python API
• The CUBRID-Python Driver at PYPI (Python Package Index) - http://pypi.python.org/pypi/CUBRID-Python
• CUBRID API Reference
• CUBRID Tutorials

Tags:

python