Before you can execute SQL queries using Psycopg2-Wrapper, you need to configure the connection to the PostgreSQL server. You can do this by creating a configuration dictionary with the following fields:

config = {
    "host": "localhost",
    "port": "5432", # if not specified, default port 5432 will be used
    "database": "mydatabase",
    "user": "myusername",
    "password": "mypassword"
}

• host: The hostname of the PostgreSQL server.
• port: The port number of the PostgreSQL server.
• database: The name of the PostgreSQL database to connect to.
• user: The username to use for authentication.
• password: The password to use for authentication.

Check out the Psycopg2 documentation for more information about the configuration options.

The NativeQueryExecutor class allows you to execute native SQL queries using Psycopg2. You can create an instance of the class and use its execute_query method to execute SQL queries:

# create a NativeQueryExecutor instance
query_executor = NativeQueryExecutor(config)

The NativeQueryExecutor class takes a configuration dictionary as described here.

This class implements the following methods for executing SQL queries:

Query to read data from the database:

def execute_and_fetchone(self, sql: str, params: tuple = None) -> tuple:
def execute_and_fetchmany(self, sql: str, params: tuple = None, size: int = 2) -> list:
def execute_and_fetchall(self, sql: str, params: tuple = None) -> list:

And query to write/modify data to the database:

def execute_and_commit(self, sql: str, params: tuple = None) -> None:
def execute_many_and_commit(self, sql: str, params: list) -> None:

# the sql query
query_data_query = "SELECT * FROM example_table WHERE id = %s"
# the parameters of the query
param = (1,)
# execute the query and fetch the results
result = query_executor.execute_and_fetchone(query_data_query, param)

# the sql query
query_data_query = "SELECT * FROM example_table WHERE id = %s"
# the parameters of the query
param = (1,)
# execute the query and fetch the results
result = query_executor.execute_and_fetchmany(query_data_query, param, 4)

# the sql query
query_data_query = "SELECT * FROM example_table WHERE id = %s"
# the parameters of the query
param = (1,)
# execute the query and fetch the results
result = query_executor.execute_and_fetchall(query_data_query)

# the sql query
query_data_query = "INSERT INTO example_table (id, name) VALUES (%s, %s)"
# the parameters of the query
param = (1, 'John')
# execute the query and commit the changes
query_executor.execute_and_commit(query_data_query, param)

# the sql query
query_data_query = "INSERT INTO example_table (id, name) VALUES (%s, %s)"
# the parameters of the query
params = [(1, 'John'), (2, 'Jane'), (3, 'Jack')]
# execute the query and commit the changes
query_executor.execute_many_and_commit(query_data_query, params)

The SimpleQueryExecutor class extends the NativeQueryExecutor class and provides methods for executing simple SQL queries. Here are some usage examples:

First we start by instantiating the SimpleQueryExecutor class:

# Define database configuration
config = {
    'host': 'localhost',
    'port': 5432,
    'database': 'my_database',
    'user': 'my_user',
    'password': 'my_password'
}
# Create an instance of SimpleQueryExecutor
query_executor = SimpleQueryExecutor(config)

The SimpleQueryExecutor class takes a configuration dictionary as described here.

Creating a table

# Define the columns for the new table
columns = {
    'id': 'SERIAL PRIMARY KEY',
    'name': 'VARCHAR(255)',
    'age': 'INTEGER'
}

# Create the new table
query_executor.create_table('my_table', columns)

The create_table method takes two parameters: the name of the table to create, and a dictionary of column names and their data types.

Selecting data from a table

# Select all columns from the 'my_table' table
results = query_executor.select_data('my_table')
print(results)

# Select only the 'name' and 'age' columns from the 'my_table' table
results = query_executor.select_data('my_table', columns=['name', 'age'])
print(results)

# Select only the 'name' column from the 'my_table' table where age is greater than or equal to 18
results = query_executor.select_data('my_table', columns=['name'], where='age >= 18')
print(results)

The select_data method takes three parameters: the name of the table to select data from, a list of column names to select, and an optional where_clause parameter to filter the results.

Inserting data into a table

# Define the data to insert
data = {
    'name': 'John',
    'age': 25
}

# Insert the data into the 'my_table' table
query_executor.insert_data('my_table', data)

The insert_data method takes two parameters: the name of the table to insert data into, and a dictionary of column names and their corresponding values.

Dropping a table

# Drop the 'my_table' table
query_executor.drop_table('my_table')

The drop_table method takes one parameter: the name of the table to drop.

For more examples of how to use the SimpleQueryExecutor class, check out the SimpleQueryExecutor example.