This guide introduces you to the installation and usage of the Python Connector for Polypheny. We’ll go through a series of examples illustrating common database operations such as user login, table creation, data manipulation, and data retrieval. Finally, we’ll unite these individual pieces into a coherent, comprehensive Python program.
Installing the Python Connector
The Python Connector for Polypheny is essential for running Python applications with Polypheny. This connector can be installed on Linux, macOS, and Windows operating systems. The complete developer notes, along with the source code, are available on GitHub.
Prerequisites
Before installing the Python Connector, you need to ensure you have the right software packages:
Python
The Python Connector requires Python 3.6 or later. To verify your Python version, execute the following command in your terminal:
python --version
Python Package Installer and Setup Tools
The Python Connector is installed via pip, a popular Python package installer and manager. You should have pip version 19.0 or later. You can upgrade pip using the following command:
python -m pip install --upgrade pip
If you have both Python 2.7.x and Python 3.x installed, use pip3 to install the connector with Python 3.x. Also, we recommend creating a virtual environment using anaconda.
Installing the Connector
The Python Connector for Polypheny is available on PyPI. A detailed change log is also provided, so you can explore the updates in each release.
To install the connector, execute the following command, replacing <version> with the specific version you want to install:
pip install polypheny==<version>
For the latest version, simply run:
pip install polypheny
Verifying Your Installation
After installation, verify the Python Connector by creating a Python file (e.g., validate.py) containing the following sample code. This script establishes a connection to Polypheny and retrieves some dummy information:
#!/usr/bin/env python
import polypheny
# Connect to Polypheny
connection = polypheny.connect('<polypheny_host>', 20591, user='pa', password='')
# Get a cursor
cursor = connection.cursor()
# Execute a query
cursor.execute("SELECT * FROM emps")
result = cursor.fetchall()
print("Result Set: ", result)
# Close the connection
connection.close()
Before running this script, replace <polypheny_host> with the DNS entry of your Polypheny installation (localhost if installed locally) and update user and password with your Polypheny credentials.
To run the file, execute the command:
python validate.py
If the connection is successful, you will see output similar to:
Result Set: [[100, 10, 'Bill', 10000, 1000], [110, 10, 'Theodore', 11500, 250], [150, 20, 'Sebastian', 7000, 400], [200, 30, 'Eric', 8000, 500]]
Congratulations! You’ve now connected your Python application to Polypheny. Now let’s dive into using the Python Connector to interact with Polypheny.
Establishing a Connection with Polypheny
To interact with Polypheny from Python, we first import the polypheny module:
import polypheny
Then, we establish a connection to Polypheny. In the below example, we connect to a Polypheny instance running on the local machine (localhost) on port 20591 using the user credentials for ‘pa’ with an empty password:
connection = polypheny.connect('localhost', 20591, user='pa', password='')
Executing Operations in Polypheny
Once connected, we create a cursor object from the connection to enable interaction with the database. This cursor object serves as a mediator, facilitating both data read and write operations with Polypheny:
cursor = connection.cursor()
Reading Data from Polypheny
To retrieve data from a table in the database, we use the execute method of the cursor object, passing an SQL SELECT statement as a string argument. The fetchall method then retrieves all the records resulting from the executed query:
cursor.execute("SELECT * FROM emps")
result = cursor.fetchall()
print(result)
Writing Data to Polypheny
Writing data to Polypheny involves using an SQL INSERT statement passed to the execute method. The changes are then committed to the database with the commit method of the connection object:
cursor.execute("INSERT INTO dummy VALUES (407 , 'de', 93)")
connection.commit()
Terminating the Connection
Upon completion of all necessary database operations, it’s best practice to close the connection object. This operation automatically closes the associated cursor object as well, freeing up resources:
connection.close()
Utilizing Logging Capabilities
The Polypheny Connector for Python incorporates the standard Python logging module to record its activity, aiding in tracing and debugging application behavior. The basicConfig method of the logging module sets up the most basic logging configuration:
import logging
logging.basicConfig(format='%(levelname)s:%(message)s', level=logging.DEBUG)
Illustrative Python Program
Below is an integrative Python program that demonstrates the usage of the Python Connector for Polypheny, encompassing connection setup, table creation, data insertion, data retrieval, and connection termination:
import polypheny
import logging
# Setup logging
logging.basicConfig(format='%(levelname)s:%(message)s', level=logging.DEBUG)
# Connect to Polypheny
connection = polypheny.connect('localhost', 20591, user='pa', password='')
cursor = connection.cursor()
# Create a new table
cursor.execute("CREATE TABLE dummy (id INT NOT NULL, text VARCHAR(2), num INT, PRIMARY KEY(id))")
# Insert data into the table
cursor.execute("INSERT INTO dummy VALUES (407 , 'de', 93)")
connection.commit()
# Query data from the table
cursor.execute("SELECT * from dummy")
result = cursor.fetchall()
print("Result Set: ", result)
# Close the connection
connection.close()
This code provides a great starting point for further exploration of the Python Connector and its capabilities within the context of Polypheny.
