Python PostgreSQL Where

Select With a Filter

When selecting records from a table, you can filter the selection by using the "WHERE" statement:

FILE: where.py

import psycopg2
conn = psycopg2.connect(database = "kwikl3arn", user = "postgres", password = "post123", host = "localhost", port = "5432")
conn.autocommit = True
print ("Opened database successfully")

cur = conn.cursor()

cur.execute("select * from customers WHERE address='LA'")
rows = cur.fetchall()

print(rows)

conn.close()

RUN:

C:\Users\Your Name>python where.py

Opened database successfully
[('morning star', 'LA', 4)]

Like With a Filter

You can also select the records that starts, includes, or ends with a given letter or phrase.

Use the %  to represent wildcard characters:

 FILE: where_like.py

import psycopg2
conn = psycopg2.connect(database = "kwikl3arn", user = "postgres", password = "post123", host = "localhost", port = "5432")
conn.autocommit = True
print ("Opened database successfully")

cur = conn.cursor()

cur.execute("select * from customers WHERE address like '%l%'")
rows = cur.fetchall()

print(rows)

conn.close()

RUN:

C:\Users\Your Name>python where_like.py

Opened database successfully
[('Amy', 'apple', 5)]

Prevent SQL Injection

When query values are provided by the user, you should escape the values.

This is to prevent SQL injections, which is a common web hacking technique to destroy or misuse your database.

  FILE: sql_injection.py

import psycopg2
conn = psycopg2.connect(database = "kwikl3arn", user = "postgres", password = "post123", host = "localhost", port = "5432")
conn.autocommit = True
print ("Opened database successfully")

cur = conn.cursor()

addr_list=['apple']
sql = "SELECT * FROM customers WHERE address = '{}'". format(*addr_list)


cur.execute(sql)

rows = cur.fetchall()

print(rows)

conn.close()

RUN:

C:\Users\Your Name>python sql_injection.py

Opened database successfully
[('Amy', 'apple', 5)]