[Python] Using PostgreSQL

In this posting, we will check out how to use PostgreSQL with Python on MAC.

Installation

We can simply install PostgreSQL using Homebrew.

$ brew install postgresql

Then, the default DB named postgres is initialized.

You can restart the service at any time with the below command.

This is the official guide.

• https://www.postgresqltutorial.com/postgresql-getting-started/install-postgresql-macos/
• https://www.postgresqltutorial.com/postgresql-getting-started/install-postgresql-linux/
• https://www.postgresqltutorial.com/postgresql-getting-started/install-postgresql/

And, I recommend using Postico.

$ brew install --cask postico

Connect DB through Postico

Postico helps us to manage DB in a GUI environment.

Run the Postico.

Input postgres to the Database field.

Let's create a new database.

Click the +Database button, and give a name to your DB.

The preparation to use finishes now.

Connect DB through Python

We need the psycopg2 module.

$ pip install psycopg2

Now, connect to the DB previously made.

import psycopg2

conn = None
try:
    conn = psycopg2.connect(user="hoya", database="testDB")
    print(conn.get_dsn_parameters())
    cursor = conn.cursor()
    # Work with DB here
except psycopg2.DatabaseError as error:
    print(error)
finally:
    if conn
    	cursor.close()
        conn.close()

It was way too easy.

We're going to learn about the CRUD operation following sections.

It will be achieved with an SQL query.

Create Table

cursor.execute('CREATE TABLE test_table(ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL);')
conn.commit()

Write and Read Data

cursor.execute("INSERT INTO test_table(ID, NAME, AGE) VALUES (1, 'HoYa', 18)")
conn.commit()
cursor.execute("SELECT age FROM test_table WHERE name='HoYa'")
age = cursor.fetchone()
print(age[0])

Update Data

cursor.execute("UPDATE test_table SET age=27 WHERE name='HoYa'")
conn.commit()

Delete Data

cursor.execute("DELETE FROM test_table WHERE name='HoYa'")
print(f"{cursor.rowcount} deleted")
conn.commit()

Conclusion

It is easy to use PostgreSQL with Python.

All you want to know is SQL query syntax.