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);" |
Comments
0 comments
Please sign in to leave a comment.