Web_Dev_Grad

PostgreSQL is an open source relational database system that uses SQL. It is an ORM (object relational database) and is case sensitive. It supports multiple programming languages including JavaScript and Python. It uses capital letters for SQL, double quotes for table names and ends with semi colons.

When working with relational databases in Python, one of the most popular libraries for PostgreSQL is psycopg2. This powerful and lightweight adapter allows you to connect to your PostgreSQL database and run SQL queries just like you would inside a SQL terminal, but using Python code instead.

Install it with pip:

pip3 install psycopg2

To write a psycopg2 script:

• Create a Python file—for example, sql_psycopg2.py
• Import the Library
• Connect to the Database
• Create a Cursor
  • The cursor acts like a command centre for your queries. It's similar to how a list or array is used to hold and manipulate data and allows you to send commands to the database and retrieve results.

# Import the library
import psycopg2
# Connect to the Database
connection = psycopg2.connect(
    database="your_database_name",
    user="your_username",
    password="your_password",
    host="localhost",
    port="5432"
)

# Create a Cursor
cursor = connection.cursor()

You can now start querying your database using SQL wrapped in Python strings. Use single quotes around the query, and double quotes around table and column names if needed (especially when using capitalized identifiers in PostgreSQL).

cursor.execute('SELECT * FROM "Artist"')
cursor.execute('SELECT "Name" FROM "Artist"')

When you want to safely insert variables into a query (e.g., user input), use placeholders to avoid SQL injection.

cursor.execute('SELECT * FROM "Artist" WHERE "Name" = %s', ['Queen'])
cursor.execute('SELECT * FROM "Album" WHERE "ArtistId" = %s AND "Title" = %s', [51, "Greatest Hits"])

• Use %s as the placeholder (regardless of the data type).
• Pass the actual value(s) as a list or tuple in the second argument.

After running a query, you can fetch the data returned:

# Fetch All Results
results = cursor.fetchall()

# Fetch One Result
result = cursor.fetchone()

# Looping Through Results
for row in results:
    print(row)

After you're done, always close your connection. This ensures your program doesn't leave open connections that can cause memory issues or errors.

connection.close()