1. Data Virtuality
  2. How-To Guides
  3. Connecting Frontends

Articles in this section

Connecting Data Virtuality Server from Python using psycopg2

Avatar
Anna Litvinskaya
  • Updated
Follow

The easiest and quickest way to connect from Python code to Data Virtuality Server is the psycopg2 module. It uses the PostgreSQL protocol and has been specially designed for heavily multi-threaded applications.

For development and testing purposes, you can obtain a stand-alone package not requiring a compiler or external libraries by installing the psycopg2-binary package from PyPI:

$ pip install psycopg2-binary

In production, it is advised to use the package built from sources, as described in detail in the Installation section of the psycopg2 documentation.

Connect to Data Virtuality Server

As Data Virtuality Server and PostgreSQL share some part in their language, psycopg2 can be used for easy-to-set-up access to Data Virtuality Server. It can be used with plain SQL statements to be sent to Data Virtuality Server.

psycopg2.connect()

import psycopg2
 
con = psycopg2.connect("dbname=datavirtuality user=admin host=localhost password=admin port=35433 sslmode=require")
cur = con.cursor()
cur.execute("select * from <schema>.<table>")
cur.fetchall()

 

Full example:

import psycopg2
 
# Data Virtuality Server Details
host = "localhost"
port = "35433"
database = "datavirtuality"
sslmode = "require"
uid = "admin"
pwd = "admin"
 
# psycopg2 connection string
con_string = "dbname={} user={} host={} password={} port={} sslmode={}".format(database, uid, host, pwd, port, sslmode)
 
# Establish connection
con = psycopg2.connect(con_string)
cur = con.cursor()
cur.execute("select * from <schema>.<table>")
cur.fetchall()

 

Limitations

Using psycopg2 has some limitations. Let's consider the following example where something has gone wrong - note that we're employing the very helpful mogrify method for it. Basically, all you need to do is replace execute with mogrify, and it'll return the exact string that would be sent to the database by execute.

In: import datetime
   ...: import psycopg2
   ...:
   ...: host = "localhost"
   ...: port = "35433"
   ...: database = "datavirtuality"
   ...: sslmode = "require"
   ...: uid = "admin"
   ...: pwd = "admin"
   ...:
   ...: dt = datetime.datetime.now()
   ...:
   ...:
   ...: conn_string = "dbname={} user={} host={} password={} port={} sslmode={}".format(database, uid, host, pwd, port, sslmode)
   ...:
   ...: conn = psycopg2.connect(conn_string)
   ...: cur = conn.cursor()
   ...:
   ...: cur.mogrify("SELECT %s, %s, %s;", (dt, dt.date(), dt.time()))
Out: b"SELECT '2019-11-08T17:58:59.910677'::timestamp, '2019-11-08'::date, '17:58:59.910677'::time;"

In the statement "SELECT %s, %s, %s;", (dt, dt.date(), dt.time()), the placeholders are filled by Python via (dt, dt.date(), dt.time()). As a result, ::date and ::time are sent to the Data Virtuality Server (or, in our example, printed out by mogrify) and prevent the statement from being parsed and understood. Thus, such statements cannot be used with psycopg2. But the following statement will work all right:

cur.mogrify("SELECT cast(now() as timestamp), cast(now() as date), cast(now() as time);"

 

Related articles

Comments

0 comments

Please sign in to leave a comment.

Powered by Zendesk