accessing postgresql database in python using functions

Question

Let me start off by saying I am extremely new to Python and Postgresql so I feel like I'm in way over my head. My end goal is to get connected to the dvdrental database in postgresql and be able to access/manipulate the data. So far I have:

created a .config folder and a database.ini is within there with my login credentials.
in my src i have a config.py folder and use config parser, see below:

    def config(filename='.config/database.ini', section='postgresql'):
    # create a parser
    parser = ConfigParser()
    # read config file
    parser.read(filename)

    # get section, default to postgresql
    db = {}
    if parser.has_section(section):
        params = parser.items(section)
        for param in params:
            db[param[0]] = param[1]
    else:
        raise Exception('Section {0} not found in the {1} file'.format(section, filename))

    return db

then also in my src I have a tasks.py file that has a basic connect function, see below:

    import pandas as pd
from clients.config import config
import psycopg

def connect():
    """ Connect to the PostgreSQL database server """
    conn = None
    try:
        # read connection parameters
        params = config()

        # connect to the PostgreSQL server
        print('Connecting to the PostgreSQL database...')
        conn = psycopg.connect(**params)
        
        # create a cursor
        cur = conn.cursor()
        
    # execute a statement
        print('PostgreSQL database version:')
        cur.execute('SELECT version()')

        # display the PostgreSQL database server version
        db_version = cur.fetchone()
        print(db_version)
        
    # close the communication with the PostgreSQL
        cur.close()
    except (Exception, psycopg.DatabaseError) as error:
        print(error)
    finally:
        if conn is not None:
            conn.close()
            print('Database connection closed.')


if __name__ == '__main__':
    connect()

Now this runs and prints out the Postgresql database version which is all well & great but I'm struggling to figure out how to change the code so that it's more generalized and maybe just creates a cursor?

I need the connect function to basically just connect to the dvdrental database and create a cursor so that I can then use my connection to select from the database in other needed "tasks" -- for example I'd like to be able to create another function like the below:

def select_from_table(cursor, table_name, schema):
cursor.execute(f"SET search_path TO {schema}, public;")
results= cursor.execute(f"SELECT * FROM {table_name};").fetchall()
return results

but I'm struggling with how to just create a connection to the dvdrental database & a cursor so that I'm able to actually fetch data and create pandas tables with it and whatnot.

so it would be like task 1 is connecting to the database task 2 is interacting with the database (selecting tables and whatnot) task 3 is converting the result from 2 into a pandas df

thanks so much for any help!! This is for a project in a class I am taking and I am extremely overwhelmed and have been googling-researching non-stop and seemingly end up nowhere fast.

Zak Bainazarov · Accepted Answer · 2022-11-02 17:47:53Z

1

The fact that you established the connection is honestly the hardest step. I know it can be overwhelming but you're on the right track.

Just copy these three lines from connect into the select_from_table method

params = config()
conn = psycopg.connect(**params)
cursor = conn.cursor()

It will look like this (also added conn.close() at the end):

def select_from_table(cursor, table_name, schema):
  params = config()
  conn = psycopg.connect(**params)
  cursor = conn.cursor()
  cursor.execute(f"SET search_path TO {schema}, public;")
  results= cursor.execute(f"SELECT * FROM {table_name};").fetchall()
  conn.close()
  return results

answered Nov 2, 2022 at 17:47

Zak Bainazarov

863 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Zak Bainazarov Over a year ago

Let me know if that works. Also do they grade on code quality? If so, there are some improvements that can be made like generalizing the connect function as you mentioned.

jenna Over a year ago

Hi! I believe this did work, thank you so much for your help!! I'm not 100% sure if they will be grading on code quality but if there's a way for me to generalize the connect function, I'd appreciate it! I was struggling to get connected so I ended up sticking with the first thing that worked because I'd deleted everything/started fresh numerous times. Thanks again!!!

Collectives™ on Stack Overflow

accessing postgresql database in python using functions

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related