Connect with Python API
Connect to your server using python scripts
You can also connect to your database and execute queries through PostgreSQL's Python API. We'll be using the python package psycopg2 to connect to our database in the following steps:
Create a
database.ini
file containing sensitive information needed to connect to our databaseCreate a
config.py
script containing a config functionCreate and run a
connect_to_db.py
script to connect to the database
I relied heavily on this website for the information on this page:
Create "database.ini"
To connect to your database, you first need to provide Python your credentials. If you're sharing project code with team, or if your code is in a public repository, you don't want to share your host name, username, and (most importantly) password with everyone who sees the Python scripts that run queries.
Instead, we can write all of our login credentials in a file titled database.ini
and refer to it in our config.py
script. Additionally, if you're using a GitHub repository to share project code, we can add database.ini
to our .gitignore file so that our personal login information is not contained in the script.
In your folder where you will place all your Python scripts with queries (meaning the script will require you to connect to your database), write a database.ini
file that contains the following information:
[postgresql]
host=ec2-[your-public-IPv4-address].[region].compute.amazonaws.com"
database=db
user=username
password=password
Create config.py script
To set your configurations for connecting to your database in Python, create a config.py
file with a function called config, which you will be using whenever you want to connect to your database in your scripts. (2)
Place this code in a script titled config.py
:
from configparser import ConfigParser
def config(filename='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
Connect to your Database
Now, we will run a function called "connect()" to test whether we can connect to our database.
Place the following code in a script called connect_to_db.py
:
import psycopg2
from config import config
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 = psycopg2.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, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
print('Database connection closed.')
if __name__ == '__main__':
connect()
Run connect_to_db.py
in your terminal:
python connect_to_db.py
Your output should be a variation of the following
Connecting to the PostgreSQL database...
PostgreSQL database version:
(<PostgreSQL version you're using on AWS EC2 machine>)
Database connection closed.
If you see the above output, then you've successfully connected to your database!
Additional Resources
If you want to learn more about creating table and executing other queries using the Python API, the following documentation, which I also referenced throughout this section, has tutorials and examples on creating table and executing other queries using psycopg2.
Also, the following Medium post also discusses how to use pandas to create dataframes from tables in your database (towards the end of the post).
Last updated
Was this helpful?