Using the Python Driver for PostgreSQL (How to connect and operate with PostgreSQL in a Python application)

PostgreSQL is a very powerful open-source database system, and Python is a great choice for operating on PostgreSQL.

1. Install PostGIS

If you need to use PostGIS, this extension allows you to handle geospatial data types.

pip install psycopg2-binary pystyle

2. Connect to PostgreSQL

To connect to a PostgreSQL server, you will need the following information:

• 'postgresql' : is your database driver module.
• 'psycopg2': is the specific driver you are using.
• 'host': 'localhost'：is your hostname (localhost).
• 'database': 'my_database': is the name of the database you want to connect to.
• 'user': 'my_user': is the username you want to use to connect to the database.
• 'password': 'my_password': is your database password.

import psycopg2

# 1. Use psycopg2.connect()
def conectar_a_postgresql():
    try:
        # 1.1.Establish the connection.
        conn = psycopg2.connect(
            host='localhost',
            database='my_database',
            user='my_user',
            password='my_password'
        )

        return conn
    except (Exception, psycopg2.DatabaseError) as error:
        print(error)

# 2. Use cur.execute()
def conectar_a_postgresql_cur():
    try:
        # 1.1. Establish the connection.
        conn = conectar_a_postgresql()
        cur = conn.cursor()

        return cur
    except (Exception, psycopg2.DatabaseError) as error:
        print(error)

# 3. Execute SQL statements
def execute_query(cur):
    try:
        # 1.1. Execute a SQL command
        query = """
            SELECT * FROM users;
        """
        cur.execute(query)

        # Fetch all rows in the table
        rows = cur.fetchall()
        for row in rows:
            print(row)

# 4.close() Release resources of the connection
def close_connection(conn):
    if conn.is_connected():
        try:
            # Close the connection
            cur.close()
            conn.close()
            print("Disconnect from database.")
        except (Exception, psycopg2.DatabaseError) as error:
            print(error)

# Use connection and execute sql
def main():
    conn = conectar_a_postgresql_cur()

    execute_query(conn)

    close_connection(conn)

if __name__ == "__main__":
    main()

5. Error Handling

Use try-except statements to catch any exceptions, for example:

try:
    cur.execute("SELECT * FROM users")
except (Exception, psycopg2.DatabaseError) as error:
    print(error)

6. Questions and Tips

• For each table: you need to perform one INSERT, UPDATE or DELETE operation.
• Choose the type of SQL statement: INSERT INTO table_name VALUES [[value]]; UPDATE [table_name] SET [[column]=[[new_value]]];, DELETE FROM [table_name] WHERE [[condition]].