Connecting to a Polypheny instance and executing queries is straightforward. This page details the creation of a connection and the utilization of a cursor object, and elaborates on executing queries, fetching results, managing transactions, and the proper closure of cursor and connection resources within the Python environment.
Establishing a Connection
To establish a connection to your instance of Polypheny, you need to following parameters. These parameters are passed to the Connection class constructor when initializing a connection.
-
address: The hostname or IP address of the machine where the Polypheny instance is running. For local development, this is typically ‘localhost’. -
port: The port number on which the Prism Interface is listening. The default port is 20590, but this can be configured for every Prism Interface individually. -
username: The username for authenticating with the Polypheny database. Ensure that the user exists and has the necessary permissions for the operations you intend to perform. -
password: The password associated with the username for authentication. -
transport: The transport method used for communication between the driver and the Polypheny server. The choice of transport can affect the performance and security of your database interactions. Polypheny supports the following transport protocols:plain, TODO
Below is an example of how to configure and establish a connection to Polypheny using the Python driver:
from polypheny import Connection
# Connection parameters
address = 'localhost'
port = 20590
username = 'pa'
password = 'pa'
transport = 'plain'
# Establishing a connection
conn = Connection(address=address, port=port, username=username, password=password, transport=transport)
# Now you can use `conn` to create cursors, execute queries, etc.
TODO: Encryption, Socket connections
Creating a Cursor
Once the connection is established, you can create a cursor object using the cursor() method. The cursor is used to execute queries and fetch data.
cursor = conn.cursor()
Executing Queries
You can execute SQL queries using the execute() method of the cursor object. Optionally, you can pass parameters to your SQL query to prevent SQL injection.
cursor.execute("SELECT * FROM your_table WHERE id = %s", (your_id,))
Fetching Data
After executing a SELECT statement, you can fetch the data using fetchone(), fetchmany(size), or fetchall() methods.
# Fetch a single row
row = cursor.fetchone()
# Fetch multiple rows
rows = cursor.fetchmany(10)
# Fetch all rows
all_rows = cursor.fetchall()
Committing and Rolling Back Transactions
To commit the current transaction, use the commit() method. If you need to roll back the transaction, you can use the rollback() method.
try:
# Execute queries
# ...
conn.commit()
except Exception as e:
conn.rollback()
Closing the Connection
It’s important to close the cursor and connection when you’re done with them to free up database resources.
cursor.close()
conn.close()
