Accessing a PSQL Zen Database With Python and ODBC

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 makes data easy. We deliver cloud, hybrid, and on-premises data solutions that simplify how people connect, manage, and analyze data. We transform business by enabling customers to make confident, data-driven decisions that accelerate their organization’s growth. Our data platform integrates seamlessly, performs reliably, and delivers at industry-leading speeds.