1

I am running this python code to read in quotes from a file and then insert each quote into a table in my database. When I run the script in my terminal there are no errors but when I go to the database table I tried to insert to it tells me there are 0 rows.

#!/usr/bin/python
import psycopg2
import re
from config import config

conn = psycopg2.connect(
    host="localhost",
    database="favorite_quotes",
    user="postgres",
    password="???")

data = None

with open('file.txt', 'r') as file:
    data = file.read()

list_of_strings = re.findall('“(.+?)” \(.+?\)', data, re.DOTALL)

def insert_quotes():
    """ 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 = psycopg2.connect(**params)
        
        # create a cursor
        cur = conn.cursor()
        
        for str in list_of_strings:
            cur.execute("INSERT INTO the_courage_to_be_disliked (quote) VALUES (%s)", [str])


    # execute a statement
        # dblist = list(cur.fetchone())
        # quotes = []
        # for row in cur:
        #     quotes.append(row[1])
        # return quotes

    # close the communication with the PostgreSQL
        cur.close()
    except (Exception, psycopg2.DatabaseError) as error:
        print(error)
    finally:
        if conn is not None:
            conn.close()
            print('Database connection closed.')


if __name__ == '__main__':
    insert_quotes()
gtrman97
  • 65
  • 4

1 Answers1

0

You aren't committing your inserts, so they won't be visible from outside the current connection, and will be implicitly rolled back when the connection is closed.

TL;DR, add a call to conn.comimt() before calling conn.close().

Mureinik
  • 297,002
  • 52
  • 306
  • 350