Data Management

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

Actian Corporation

January 4, 2018

Connecting ServiceNow to other applications' data doesn’t 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 all I had to do was 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
      else:
         if new_name == '':
            print(' Please enter a non-empty string.')
         else:
            print(' Inserting:', new_name)
            c.execute(isql_dml, (new_name,))
            c.commit()

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

if __name__ == "__main__":
   sys.exit(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 Actian Corporation

Actian is helping businesses build a bridge to a data-defined future. We’re doing this by delivering scalable cloud technologies while protecting customers’ investments in existing platforms. Our patented technology has enabled us to maintain a 10-20X performance edge against competitors large and small in the mission-critical data management market. The most data-intensive enterprises in financial services, retail, telecommunications, media, healthcare and manufacturing trust Actian to solve their toughest data challenges.