Connecting to a Polypheny instance and executing queries is straightforward. This guide covers how to establish a connection, create a cursor, execute queries, fetch results, manage transactions, and properly close resources.
Establishing a Connection
To connect to a Polypheny instance, use the connect() function from the Polypheny driver. This function allows you to specify connection parameters, such as authentication credentials and transport options.
Connection Parameters
address(tuple[str, int]|str, optional):- A Unix socket path for
unixtransport or a(hostname, port)tuple forplaintransport. - Defaults to
~/.polypheny/polypheny-prism.sockif not provided.
- A Unix socket path for
username(str, optional):- The username for authentication.
password(str, optional):- The password for authentication.
transport(str, optional):- The transport type, either
plainorunix. Defaults tounix.
- The transport type, either
Example:
from polypheny import connect
# Establishing a connection using plain transport
conn = connect(address=('localhost', 20591), username='admin', password='admin', transport='plain')
# Establishing a connection using unix transport (default)
conn = connect()
Creating a Cursor
Once the connection is established, create a cursor object using the cursor() method. The cursor is used to execute queries and fetch data.
cur = 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.
cur.execute("SELECT * FROM users WHERE id = ?", (user_id,))
To execute multiple queries with different parameter sets, use executemany():
cur.executemany("INSERT INTO users (id, name) VALUES (?, ?)", [(1, 'Alice'), (2, 'Bob')])
To execute queries in different languages, use executeany():
cur.executeany('mongo', 'db.users.find({"id": 1})')
Fetching Data
After executing a SELECT statement, fetch the data using fetchone(), fetchmany(size), or fetchall().
# Fetch a single row
row = cur.fetchone()
# Fetch multiple rows
rows = cur.fetchmany(10)
# Fetch all rows
all_rows = cur.fetchall()
Committing and Rolling Back Transactions
To commit changes made within a transaction, use commit(). If an error occurs, you can revert changes using rollback().
try:
cur.execute("UPDATE users SET name = ? WHERE id = ?", ("John", 1))
conn.commit()
except Exception as e:
conn.rollback()
Note: DDL statements (e.g., CREATE TABLE) automatically commit the transaction and cannot be rolled back.
Closing the Connection
Always close the cursor and connection when done to free up resources.
cur.close()
conn.close()
