Connect to postgresql using python script (database URI) but detects 'conn' as string

Question

I am using a simple python script to connect the postgresql and future will create the table into the postgresql just using the script.

My code is:

try:
    conn =  "postgresql://postgres:<password>@localhost:5432/<database_name>"
    print('connected')

except:
    print('not connected')

conn.close() 

when I run python connect.py (my file name), it throws this error :

Instance of 'str' has no 'commit' member

pretty sure is because it detects 'conn' as a string instead of database connection. I've followed this documentation (33.1.1.2) but now sure if Im doing it right. How to correct this code so it will connect the script to my postgresql server instead of just detects it as a string?

p/s: Im quite new to this.

Answer 1

You are trying to call a method on a string object. Instead you should establish a connection to your db at first.

I don't know a driver which allows the use of a full connection string but you can use psycopg2 which is a common python driver for PostgreSQL.

After installing psycopg2 you can do the following to establish a connection and request your database

import psycopg2
try:
    connection = psycopg2.connect(user = "yourUser",
                                  password = "yourPassword",
                                  host = "serverHost",
                                  port = "serverPort",
                                  database = "databaseName")

    cursor = connection.cursor()
except (Exception, psycopg2.Error) as error :
    print ("Error while connecting", error)
finally:
        if(connection):
            cursor.close()
            connection.close()

You can follow this tutorial