Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://pythonspot.com/python-database-postgresql/ below:

python postgresql - Python Tutorial

Learn how to effectively utilize Python with the powerful PostgreSQL database. PostgreSQL, a robust relational database management system (RDBMS), boasts of features like foreign keys, joins, views, triggers, stored procedures, and much more. By the end of this guide, you’ll be familiar with the essentials of integrating PostgreSQL with Python.

Relevant Course: Master SQL Databases with Python

To commence, ensure you have both the PostgreSQL DBMS and the psycopg2 Python module. For those on an Ubuntu system, you can swiftly install the PostgreSQL using the following:

1

sudo apt-get install postgresql postgresql-contrib

To confirm the successful installation and running status of PostgreSQL, employ:

1

sudo /etc/init.d/postgresql status

If you’re not greeted with the expected screen, the commands below might come in handy:

1
2

sudo service postgresql start
sudo service postgresql restart

Psycopg serves as the bridge connecting Python to PostgreSQL. To get it on board, use:

1

sudo apt-get install python-psycopg2

Next, set up a database and its user, also referred to as a role, with these commands:

1
2

sudo -u postgres createuser -D -A -P pythonspot
sudo -u postgres createdb -O pythonspot testdb

A quick reload ensures everything is in order:

1

sudo /etc/init.d/postgresql reload

Run the below script to create a database table and fill it with some initial data:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

import psycopg2
import sys

con = None

try:
    con = psycopg2.connect("host='localhost' dbname='testdb' user='pythonspot' password='password'")   
    cur = con.cursor()
    cur.execute("CREATE TABLE Products(Id INTEGER PRIMARY KEY, Name VARCHAR(20), Price INT)")
    cur.execute("INSERT INTO Products VALUES(1,'Milk',5)")
    cur.execute("INSERT INTO Products VALUES(2,'Sugar',7)")
    cur.execute("INSERT INTO Products VALUES(3,'Coffee',3)")
    cur.execute("INSERT INTO Products VALUES(4,'Bread',5)")
    cur.execute("INSERT INTO Products VALUES(5,'Oranges',3)")
    con.commit()
except psycopg2.DatabaseError as e:
    if con:
        con.rollback()
    print(f'Error: {e}')
    sys.exit(1)
finally:   
    if con:
        con.close()

It’s vital to execute all SQL queries for changes to take effect:

1

con.commit()

Fetch data using the SELECT SQL command. The method returns a list for every data row:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

import psycopg2
import sys

con = None

try:
    con = psycopg2.connect("host='localhost' dbname='testdb' user='pythonspot' password='password'")   
    cur = con.cursor()
    cur.execute("SELECT * FROM Products")
    while True:
        row = cur.fetchone()
        if row is None:
            break
        print(f"Product: {row[1]}\t\tPrice: {row[2]}")
except psycopg2.DatabaseError as e:
    if con:
        con.rollback()
    print(f'Error: {e}')
    sys.exit(1)
finally:   
    if con:
        con.close()

Modify data within a PostgreSQL table using the following:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

import psycopg2
import sys

con = None

try:
    con = psycopg2.connect("host='localhost' dbname='testdb' user='pythonspot' password='password'")   
    cur = con.cursor()
    cur.execute("UPDATE Products SET Price=%s WHERE Id=%s", (10, 4))      
    con.commit()
except psycopg2.DatabaseError as e:
    if con:
        con.rollback()
    print(f'Error: {e}')
    sys.exit(1)
finally:   
    if con:
        con.close()

Erase data from your PostgreSQL table using:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

import psycopg2
import sys

con = None

try:
    con = psycopg2.connect("host='localhost' dbname='testdb' user='pythonspot' password='password'")   
    cur = con.cursor()
    cur.execute("DELETE FROM Products WHERE Id=" + str(4))      
    con.commit()
except psycopg2.DatabaseError as e:
    if con:
        con.rollback()
    print(f'Error: {e}')
    sys.exit(1)
finally:   
    if con:
        con.close()

Navigate through more Python database interactions: Back | Next

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4