December 31, 2013
Create Pythonesque c-treeACE SQL Applications
Note: c-treeACE became FairCom DB in November 2020.
Fast, friendly and easy to use aren’t terms frequently used to describe most computer programming languages. However, they all apply to the dynamic Python language. Created by Guido van Rossum, Python supports Java, C#, C, C++ and integrates with COM, .NET and CORBA enabling a wide range of application potential.
With class libraries available for nearly any conceivable domain, Python is highly extensible and often used as a scripting language. The Python philosophy has been described as:
- Beautiful is better than ugly.
- Explicit is better than implicit.
- Simple is better than complex.
- Complex is better than complicated.
- Readability counts.
http://en.wikipedia.org/wiki/Python_(programming_language)
As Python is designed for extensibility, it was natural for c‑treeACE SQL to include Python as an additional interface technology, joining the over 15 others currently supported.
c‑treeACE SQL Python
c‑treeACE SQL V10 introduced a Python module. The c‑treeACE SQL Python module, pyctree, interfaces with c‑treeACE SQL through the well-known ctypes module. It is a DB-API 2.0 implementation conforming to Python PEP 249 standards.
c‑treeACE SQL Python Setup
c‑treeACE SQL data persistence is provided through the pyctree module. The included setup.py script is used to install the pyctree module. c‑treeACE SQL Python does not require a C compiler to install the extension.
# python setup.py install
Note: pyctree requires Python 2.5 or greater.
pyctsqlapi.py provides the core functionality and is required by pyctree.py. Both these files are installed by the setup script.
Use the pyctree import in your project much as you would sqlite, for example. After that, you can easily integrate c‑treeACE SQL into your Python applications and take advantage of many powerful c‑treeACE SQL features such as stored procedures and triggers, dynamic backups, and replication.
Examples
Simple SQL query
import pyctree
conn = pyctree.connect('tutorial1.db')
c = conn.cursor()
t = ('1',)
c.execute('SELECT * FROM custmaster WHERE cm_custratg=?', t)
print c.fetchone()
Multiple record inserts
purchases = [('1000', '92867', 'CA', '1', 'Bryan Williams', '2999 Regency', 'Orange'),
('1001', '61434', 'CT', '1', 'Michael Jordan', '13 Main', 'Harford'),
('1002', '73677', 'GA', '1', 'Joshua Brown', '4356 Cambridge', 'Atlanta'),
('1003', '10034', 'MO', '1', 'Keyon Dooling', '19771 Park Avenue', 'Columbia')
]
c.executemany('INSERT INTO custmaster VALUES (?,?,?,?,?,?,?)', purchases)