Actian Blog / Programming the Easy Way: Accessing a PSQL Zen Database with Python and ODBC

Programming the Easy Way: Accessing a PSQL Zen Database with Python and ODBC

Connecting ServiceNow To Other Applications Data Doesnt Have To Be Difficult

As an old-school programmer from a few decades ago, I decided to see how hard it would be to access a Zen (formerly known as PSQL) database from my newly acquired knowledge of Python.  In case you’re not familiar with it, Zen is Actian’s embedded zero-admin, nano-footprint hybrid NoSQL and SQL database software, and Python is a high-level programming language frequently used in data science and internet applications (among many other things).

Already armed with some knowledge of Actian Zen, basically all I had to do is complete an EdX introductory course in Python programming, then download and install Python 3.6 along with the corresponding pyodbc library.

The simple program to connect Zen with Python shown below is about 30 lines of code!  It connects to the “demodata” database, allocates a cursor, and executes a series of SQL statements to DROP TABLE, CREATE TABLE, INSERT, and SELECT.  How easy is that? I even managed to prompt for input and use a parameter in the INSERT for the user-provided value.  Here is the code:

import os
import sys
import pyodbc

def main():
   conn_str = 'Driver={Pervasive ODBC Interface};server=localhost;DBQ=demodata'
   db = pyodbc.connect(conn_str)
   c = db.cursor()
   c.execute("DROP TABLE IF EXISTS test_table")
   c.execute("CREATE TABLE test_table (id identity, name char(32), create_date date)")

   isql_dml = """INSERT INTO test_table VALUES (0, ?, CURRENT_DATE())"""
   iinserting = True
   while iinserting:
      new_name = input('Enter name to insert, Q to quit: ' )
      if new_name.lower() == 'q':
         iinserting = False
         if new_name == '':
            print(' Please enter a non-empty string.')
            print(' Inserting:', new_name)
            c.execute(isql_dml, (new_name,))

   c.execute("SELECT COUNT(*) FROM test_table")
   row = c.fetchone()
   if row:
      print('You inserted', row, 'name(s)')
   return 0

if __name__ == "__main__":

It’s easy to see how simple yet powerful this access is, and how easily it could be used for testing and customer support scenarios.

Download the Actian Zen trial today and write your first Python ODBC app!  30-day trial versions are available right here. If you have any questions about Zen or other Actian products please feel free to ask in our community forums.

About Linda Anderson

Linda Anderson is the Sustaining Engineering Manager for the Actian Zen products, heading the team that provides updates and improvements to the current product line. She has a 25+ year history working with Actian Zen, PSQL, and Btrieve developers and users. Linda is a long-time Cubs fan, grateful for the World Series win in 2016.