Open Source RDBMS - Seamless, Scalable, Stable and Free

English | Login |Register

CUBRID Python Tutorial – Part I (2010)

>> CUBRID Python Tutorial Part II

The scope of this tutorial is to give you a quick introduction to the CUBRID Python driver. The first part of this tutorial will explain the steps you need to perform in order to connect to the CUBRID database and to run SQL statements using Python.

The first thing we need to do is to install the CUBRID Python driver. All the details for this operation can be found online: http://www.cubrid.org/wiki_apis/entry/cubrid-python-driver-installation-instructions. Just follow the steps in the link and you will complete the driver installation in no time.

Also, the standard demodb database is required for the code examples – we will assume it is already installed on your environment. And we will use CUBRID 9.0 in the examples below – you can always download the latest version from here: http://www.cubrid.org/downloads.

 

Start the demodb database

Once the Python driver is installed, let’s make sure that the demodb database is started.

If you need to read more about CUBRID demodb database, please go here: http://www.cubrid.org/wiki_tutorials/entry/getting-started-with-demodb-cubrid-demo-database.

From the console let’s execute:

$ cubrid service status 

@ cubrid master status
++ cubrid master is running.
@ cubrid server status
 Server demodb (rel 9.0, pid 2413)
@ cubrid broker status
  NAME           PID  PORT  AS  JQ      REQ  TPS  QPS    LONG-T    LONG-Q  ERR-Q
================================================================================
* query_editor  2377 30000   5   0        6    2   11    0/0.0    0/0.0      0
* broker1       2388 33000   5   0        0    0    0    0/0.0    0/0.0      0
@ cubrid manager server status
++ cubrid manager server is running.

The above output shows that demodb database has been started. If the demodb database is not started, it can be started by executing the following command:

 $ cubrid server start demodb

 Let's also create a new table we will be using in our tutorial. Execute the following command:

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

  

Connect to the database

Next, let’s open a new Python console and see how we can connect to the demodb database.

First, we will “tell” Python to import the CUBRID Python Driver:

import CUBRIDdb

Next, we will establish a connection to the demodb database, located on 'localhost', with the user ‘dba’ (the default database administrator):

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

The parameters for the connect functions can be found here: http://www.cubrid.org/wiki_apis/entry/cubrid-python-api-documentation#connect

Please note that if the demodb database is not started and you try to connect to it, you will receive an error: 

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/local/lib/python2.7/dist-packages/CUBRIDdb/__init__.py", line 61, in Connect
    return Connection(*args, **kwargs)
  File "/usr/local/lib/python2.7/dist-packages/CUBRIDdb/connections.py", line 19, in __init__
    self._db = _cubrid.connect(*args, **kwargs)
_cubrid.NotSupportedError: (0, 'ERROR: DBMS, 0, Unknown DBMS Error')

And if you provide the wrong user credentials, you will receive an error like this one:

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/local/lib/python2.7/dist-packages/CUBRIDdb/__init__.py", line 61, in Connect
    return Connection(*args, **kwargs)
  File "/usr/local/lib/python2.7/dist-packages/CUBRIDdb/connections.py", line 19, in __init__
    self._db = _cubrid.connect(*args, **kwargs)
_cubrid.NotSupportedError: (0, 'ERROR: DBMS, 0, Unknown DBMS Error')
 

Execute an INSERT Statement

In this step, we will insert some data in the table we previously created. First we will have to obtain a Python database 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)")

In the previous versions of the driver, the auto-commit in the CUBRID Python Driver was disabled by default. Therefore after we executed any SQL statement we had to manually call the .commit() command.

But in the current driver version, this is not required anymore, as the auto-commit state is enabled by default.

As a side note, commit()is equivalent to executing cur.execute("COMMIT"). And the opposite to commit() is the rollback() method which aborts the current transaction, equivalent to cur.execute("ROOLBACK").

 Another way to insert the data is to execute the prepared INSERT statements; this is the recommended method of inserting data into a table. We can do this by defining a tuple that contains the parameters and pass it to the execute method, as follows:

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

  

SELECT data

Once we have some data in our table, let's see how we can execute SELECT statements, using the fetchall() Python command:

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

If you execute the script above, it will return the two rows inserted earlier in the following form:

[1, 'Title 1', 'Test body #1', '2013-1-20 14:34:46']
[2, 'Title 2', 'Test body #2', '2013-1-20 14:34:46']

In a scenario where a lot of data must be returned into the cursor, you have also the possibility to fetch just only one row at a time from the database, instead of getting all the result set into memory, by using fetchone().

 

Access records metadata

If you need to get information about column properties for the returned records, this is what you need to do – use description:

for description in cur.description:
    print description
In our scenario, this is what it returns:

('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 the tuples contains the following information set:

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

You can find more details on the description function here: http://www.cubrid.org/wiki_apis/entry/cubrid-python-api-documentation


Cleanup

 After you are done with using any cursor or connection to the database you should “release” the database resources – cursors and connections. This is done by calling object's close() method: 

cur.close()
conn.close()

The complete code example

Below is the complete example used in this 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)

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

# Cleanup
cur.close()
conn.close()
 

Stay tuned for the next part

Stay tuned for the next part of this tutorial, where we will show some more advanced features of the driver, like handling LOB objects, obtaining schema information and querying the last insert id.


See also…

CUBRID Python Driver installation

http://www.cubrid.org/wiki_apis/entry/cubrid-python-driver-installation-instructions

Driver download from cubrid.org

http://www.cubrid.org/?mid=downloads&item=python_driver&os=detect&python=detect

Online driver documentation

http://packages.python.org/CUBRID-Python/

Driver source code

http://cubridinterface.svn.sourceforge.net/viewvc/cubridinterface/python/

We strongly encourage you to try yourself the CUBRID Python driver and tell us your feedback and your suggestions for future improvements!

Thank you!

>> CUBRID Python Tutorial Part II




You are either using a very old browser or a browser that is not supported.
In order to browse cubrid.org you need to have one of the following browsers:



Internet Explorer: Mozilla Firefox: Google Chrome: