PostgreSQL

PostgreSQL, also known as Postgres, is a free and open-source relational database management system emphasizing extensibility and SQL compliance.   Database table contents and sizes are substantial, as shown in the limits page.   Installations are available for Windows OS (install tutorial), Linux, macOS, and others.   Online tutorials are available, as well as online documentation.  

Windows Installation

Follow the Windows OS install tutorial.   It will instruct you to download a installer for Windows OS.   Choose the latest version and then click the download icon for Windows x86-64.  

Using PostgreSQL with Python

PostgreSQL like most SQL databases are organized such that they contain default databases for administration ('postgres', 'template0', or 'template1' in the case of PostgreSQL), and then you create a new database for your project.   Your project database will then have one or more tables.   Each table has columns (fields) and rows containing data (by field/column and row).   A administrator level user named 'postgre' is created by default.   You should create other database users with appropriate database access permissions.  

IMPORTANT: Do not modify or add tables to the default 'postgres', 'template0', or 'template1' databases.  

 

Create a new database named 'pytestdb' for your project from psql:

# create database pytestdb;
# \list

Make sure you include the semicolon ";" and the end of the SQL command 'create database'.  

Begin with this beginner Python to PostgreSQL

" target="_blank">connection tutorial.   Keep the password you created for the 'postgres' user in an eWallet.  

Stack Builder will run after the installation.   Choose the installation from the dropdown list ('PostgreSQL ##(x64) on port 5432').   A list of applications will download and then be displayed.   You don't need any additional applications, so just click 'Cancel' and continue to click through to exit.  

A new Windows menu folder named 'PostgreSQL' will be created from the installation that will include the 'pgAdmin #' app and a 'SQL Shell (psql)' app.   Running the 'psql' app as described in the installation tutorial and clicking through the initial prompts will show that a default database named 'postgres' is created on port 5432.   Close the command prompt.  

Create a Python virtual environment and then use pip to install the package named 'psycopg2' also called psycopg.  

py -m pip install psycopg2
py -m pip install requests

Create the following script named 'db.py' in the Python virtual environment folder 'PostgreSQL' to connect to the PostgreSQL database, updating 'password=' with the pasword for user 'postgres':

#   Written by:  Mark W Kiehl
#   http://mechatronicsolutionsllc.com/
#   http://www.savvysolutions.info/savvycodesolutions/
#
#   Demonstrate using psycopg to create a table and then populate it with
#   data using the psycopg function psycopg2.extras.execute_batch.
#
#   Portions inspired and derived from the work of Haki Benita https://hakibenita.medium.com/fastest-way-to-load-data-into-postgresql-using-python-d2e6de8b2aaa
#
#   https://www.postgresqltutorial.com/postgresql-python/
#   https://www.psycopg.org/docs/index.html

# pip install psycopg2
import psycopg2
import psycopg2.extras  # for execute_batch

conn = psycopg2.connect(
    host="localhost",
    database="pytestdb",
    user="postgres",
    password="[your db password]",
)
conn.autocommit = True  #set autocommit=True so every command we execute will take effect immediately


def create_db_table(cur):
    """ Connect to the local PostgreSQL database server """
    try:
        # connect to the PostgreSQL server
        print('Connecting to the PostgreSQL database...')
        
        # create a cursor
        cur = conn.cursor()
        
        # execute a SQL statement to get the PostgreSQL version
        cur.execute('SELECT version()')
        # display the PostgreSQL database server version
        db_version = cur.fetchone()
        print(db_version)
        
        # Create a new table 'test_a'
        cur.execute("""
            DROP TABLE IF EXISTS test_a;
            CREATE UNLOGGED TABLE test_a (
                id                  INTEGER,
                description         TEXT,
                first_brewed        DATE,
                num_float           DECIMAL,
                num_int             INTEGER
            );
        """)

    except (Exception, psycopg2.DatabaseError) as error:
        print(error)
    finally:
            print('Created db table staging_bears and closed the db connection')


def add_data_to_table(cur, list_data):
    # Psycopg2 provides a way to insert many rows at once using .execute_batch (FYI much faster than .executebatch). 
    #print('type(list_data) {}'.format(type(list_data)))
    print('Inserting {} records into the db table test_a'.format(len(list_data)))    
    
    try:
        # connect to the PostgreSQL server
        print('Connecting to the PostgreSQL database...')
                
        psycopg2.extras.execute_batch(cur, """
            INSERT INTO test_a VALUES (
                %(id)s,
                %(description)s,
                %(first_brewed)s,
                %(num_float)s,
                %(num_int)s
            );
        """, list_data)    
        
        # execute a SQL statement to get the number of records in table test_a
        cur.execute('select count(*) from test_a;')
        rows = cur.fetchone()
        print('{} rows added to table test_a'.format(rows))
        
    except (Exception, psycopg2.DatabaseError) as error:
        print('SQL ERROR: {}'.format(error))
    finally:
        print('add_data_to_table() done')    


from datetime import datetime


if __name__ == '__main__':
    # Create table test_a
    with conn.cursor() as cur:
        create_db_table(cur)
        # create some data as a list of dictionaries.
        # NOTE:  The key of the dictionary must match the table field (column) names.
        dic = { 'id': 1,
                'description': 'my first description',
                'first_brewed': datetime.now(),
                'num_float':    1.2345,
                'num_int':      32767
        }
        li = []
        li.append(dic)
        dic = { 'id': 2,
                'description': 'my second description',
                'first_brewed': datetime.now(),
                'num_float':    5432.1,
                'num_int':      -32767
        }
        li.append(dic)
        print(li)
        add_data_to_table(cur, li)
        if conn is not None:
            conn.close()

 

Insert Table Row Error

Exception: ('ERROR: ', UniqueViolation('duplicate key value violates unique constraint "tablename_pkey"

The above occurred after deleting some table rows, and then attempting to insert a new row.   See the next section 'Rebuild Table Index' for the solution.  

 

Rebuild Table Index

I had trouble inserting new rows into a table after deleting some rows.   I spent hours trying to figure out how to rebuild / renumber the primary key 'id' for the table.   The SQL query below from this stackoverflow post for table 'scenic' and column 'id' that was the primary key worked.

BEGIN;

LOCK scenic;

-- remove all FK constraints to the column

ALTER TABLE scenic DROP CONSTRAINT scenic_pkey;  -- remove PK

-- for the simple case without FK references - or see below:    
UPDATE scenic t  -- intermediate unique violations are ignored now
SET    id = t1.new_id
FROM  (SELECT id, row_number() OVER (ORDER BY id) AS new_id FROM scenic) t1
WHERE  t.id = t1.id;

-- Update referencing value in FK columns at the same time (if any)

SELECT setval('scenic_id_seq', max(id)) FROM scenic;  -- reset sequence

ALTER TABLE scenic ADD CONSTRAINT scenic_pkey PRIMARY KEY(id); -- add PK back

-- add all FK constraints to the column back

COMMIT;