Click here to view the next lesson.

Chapter 17: Python Meets SQL

17.4 Performing CRUD Operations

Before moving on, we should establish a test database and a table to play with. Below is the Python code to create a new SQLite database named 'test_db.sqlite' and a table named 'employees':

import sqlite3
conn = sqlite3.connect('test_db.sqlite')

c = conn.cursor()

# Create table
c.execute('''CREATE TABLE employees
             (id INTEGER PRIMARY KEY, name text, salary real, department text, position text, hireDate text)''')

# Commit the changes and close the connection
conn.commit()
conn.close()

17.4.1 Create Operation

The Create operation is used to add new records to a database. Here is an example of how you can add a new record to the 'employees' table:

conn = sqlite3.connect('test_db.sqlite')
c = conn.cursor()

# Insert a new employee record
c.execute("INSERT INTO employees VALUES (1, 'John Doe', 50000, 'HR', 'Manager', '2023-01-05')")

# Commit the changes
conn.commit()

# Close the connection
conn.close()

17.4.2 Read Operation

The Read operation is used to retrieve data from a database. Here is an example of how to retrieve all records from the 'employees' table:

conn = sqlite3.connect('test_db.sqlite')
c = conn.cursor()

# Select all rows from the employees table
c.execute('SELECT * FROM employees')

rows = c.fetchall()

for row in rows:
    print(row)

# Close the connection
conn.close()

17.4.3 Update Operation

The Update operation is used to modify existing records in a database. Here is an example of how to update a record in the 'employees' table:

conn = sqlite3.connect('test_db.sqlite')
c = conn.cursor()

# Update employee salary
c.execute("UPDATE employees SET salary = 60000 WHERE name = 'John Doe'")

# Commit the changes
conn.commit()

# Close the connection
conn.close()

17.4.4 Delete Operation

The Delete operation is used to remove records from a database. Here is an example of how to remove a record from the 'employees' table:

conn = sqlite3.connect('test_db.sqlite')
c = conn.cursor()

# Delete an employee record
c.execute("DELETE from employees WHERE name = 'John Doe'")

# Commit the changes
conn.commit()

# Close the connection
conn.close()

Note: Please ensure that the database operations are performed in a controlled manner and always verify your commands before executing, especially for Update and Delete operations, as they can modify or remove data permanently.

17.4.5 MySQL

To interact with a MySQL database, you will need a Python library called mysql-connector-python. You can install it via pip:

pip install mysql-connector-python

Let's assume we have a MySQL database called 'testdb', and we want to insert a record in the 'employees' table.

import mysql.connector

# establish the connection
cnx = mysql.connector.connect(user='<username>', password='<password>',
                              host='127.0.0.1',
                              database='testdb')

# Create a cursor object using the cursor() method
cursor = cnx.cursor()

# Prepare SQL query to INSERT a record into the database
sql = """INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME)
         VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""

try:
   # Execute the SQL command
   cursor.execute(sql)
   # Commit your changes in the database
   cnx.commit()
except:
   # Rollback in case there is any error
   cnx.rollback()

# disconnect from server
cnx.close()

17.4.6 PostgreSQL

For PostgreSQL, you can use the psycopg2 library, which you can install via pip:

pip install psycopg2

Here is an example of inserting a record in PostgreSQL:

import psycopg2

#establishing the connection
cnx = psycopg2.connect(
   database="testdb", user='<username>', password='<password>', host='127.0.0.1', port= '5432'
)

#Creating a cursor object using the cursor() method
cursor = cnx.cursor()

# Preparing SQL queries to INSERT a record into the database.
sql = '''INSERT into EMPLOYEE (FIRST_NAME, LAST_NAME, AGE, SEX, INCOME)
         VALUES ('Mac', 'Mohan', 20, 'M', 2000)'''

# execute the SQL query using execute() method.
cursor.execute(sql)

# Commit your changes in the database
cnx.commit()

# close the cursor and connection
cursor.close()
cnx.close()

In both these examples, if the SQL operations are successful, the data is committed to the database using cnx.commit(). If there is an error, the cnx.rollback() function is called to rollback any changes to the database.

Remember that you must always close the cursor and connection object once you're done with them to avoid database memory issues.

The main difference between using SQLite, MySQL and PostgreSQL in Python is the way you connect to each database using their respective Python libraries. The SQL syntax for the CRUD operations remains the same.

17.4 Performing CRUD Operations

Before moving on, we should establish a test database and a table to play with. Below is the Python code to create a new SQLite database named 'test_db.sqlite' and a table named 'employees':

import sqlite3
conn = sqlite3.connect('test_db.sqlite')

c = conn.cursor()

# Create table
c.execute('''CREATE TABLE employees
             (id INTEGER PRIMARY KEY, name text, salary real, department text, position text, hireDate text)''')

# Commit the changes and close the connection
conn.commit()
conn.close()

17.4.1 Create Operation

The Create operation is used to add new records to a database. Here is an example of how you can add a new record to the 'employees' table:

conn = sqlite3.connect('test_db.sqlite')
c = conn.cursor()

# Insert a new employee record
c.execute("INSERT INTO employees VALUES (1, 'John Doe', 50000, 'HR', 'Manager', '2023-01-05')")

# Commit the changes
conn.commit()

# Close the connection
conn.close()

17.4.2 Read Operation

The Read operation is used to retrieve data from a database. Here is an example of how to retrieve all records from the 'employees' table:

conn = sqlite3.connect('test_db.sqlite')
c = conn.cursor()

# Select all rows from the employees table
c.execute('SELECT * FROM employees')

rows = c.fetchall()

for row in rows:
    print(row)

# Close the connection
conn.close()

17.4.3 Update Operation

The Update operation is used to modify existing records in a database. Here is an example of how to update a record in the 'employees' table:

conn = sqlite3.connect('test_db.sqlite')
c = conn.cursor()

# Update employee salary
c.execute("UPDATE employees SET salary = 60000 WHERE name = 'John Doe'")

# Commit the changes
conn.commit()

# Close the connection
conn.close()

17.4.4 Delete Operation

The Delete operation is used to remove records from a database. Here is an example of how to remove a record from the 'employees' table:

conn = sqlite3.connect('test_db.sqlite')
c = conn.cursor()

# Delete an employee record
c.execute("DELETE from employees WHERE name = 'John Doe'")

# Commit the changes
conn.commit()

# Close the connection
conn.close()

17.4.5 MySQL

To interact with a MySQL database, you will need a Python library called mysql-connector-python. You can install it via pip:

pip install mysql-connector-python

Let's assume we have a MySQL database called 'testdb', and we want to insert a record in the 'employees' table.

import mysql.connector

# establish the connection
cnx = mysql.connector.connect(user='<username>', password='<password>',
                              host='127.0.0.1',
                              database='testdb')

# Create a cursor object using the cursor() method
cursor = cnx.cursor()

# Prepare SQL query to INSERT a record into the database
sql = """INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME)
         VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""

try:
   # Execute the SQL command
   cursor.execute(sql)
   # Commit your changes in the database
   cnx.commit()
except:
   # Rollback in case there is any error
   cnx.rollback()

# disconnect from server
cnx.close()

17.4.6 PostgreSQL

For PostgreSQL, you can use the psycopg2 library, which you can install via pip:

pip install psycopg2

Here is an example of inserting a record in PostgreSQL:

import psycopg2

#establishing the connection
cnx = psycopg2.connect(
   database="testdb", user='<username>', password='<password>', host='127.0.0.1', port= '5432'
)

#Creating a cursor object using the cursor() method
cursor = cnx.cursor()

# Preparing SQL queries to INSERT a record into the database.
sql = '''INSERT into EMPLOYEE (FIRST_NAME, LAST_NAME, AGE, SEX, INCOME)
         VALUES ('Mac', 'Mohan', 20, 'M', 2000)'''

# execute the SQL query using execute() method.
cursor.execute(sql)

# Commit your changes in the database
cnx.commit()

# close the cursor and connection
cursor.close()
cnx.close()

Remember that you must always close the cursor and connection object once you're done with them to avoid database memory issues.

17.4 Performing CRUD Operations

Before moving on, we should establish a test database and a table to play with. Below is the Python code to create a new SQLite database named 'test_db.sqlite' and a table named 'employees':

import sqlite3
conn = sqlite3.connect('test_db.sqlite')

c = conn.cursor()

# Create table
c.execute('''CREATE TABLE employees
             (id INTEGER PRIMARY KEY, name text, salary real, department text, position text, hireDate text)''')

# Commit the changes and close the connection
conn.commit()
conn.close()

17.4.1 Create Operation

The Create operation is used to add new records to a database. Here is an example of how you can add a new record to the 'employees' table:

conn = sqlite3.connect('test_db.sqlite')
c = conn.cursor()

# Insert a new employee record
c.execute("INSERT INTO employees VALUES (1, 'John Doe', 50000, 'HR', 'Manager', '2023-01-05')")

# Commit the changes
conn.commit()

# Close the connection
conn.close()

17.4.2 Read Operation

The Read operation is used to retrieve data from a database. Here is an example of how to retrieve all records from the 'employees' table:

conn = sqlite3.connect('test_db.sqlite')
c = conn.cursor()

# Select all rows from the employees table
c.execute('SELECT * FROM employees')

rows = c.fetchall()

for row in rows:
    print(row)

# Close the connection
conn.close()

17.4.3 Update Operation

The Update operation is used to modify existing records in a database. Here is an example of how to update a record in the 'employees' table:

conn = sqlite3.connect('test_db.sqlite')
c = conn.cursor()

# Update employee salary
c.execute("UPDATE employees SET salary = 60000 WHERE name = 'John Doe'")

# Commit the changes
conn.commit()

# Close the connection
conn.close()

17.4.4 Delete Operation

The Delete operation is used to remove records from a database. Here is an example of how to remove a record from the 'employees' table:

conn = sqlite3.connect('test_db.sqlite')
c = conn.cursor()

# Delete an employee record
c.execute("DELETE from employees WHERE name = 'John Doe'")

# Commit the changes
conn.commit()

# Close the connection
conn.close()

17.4.5 MySQL

To interact with a MySQL database, you will need a Python library called mysql-connector-python. You can install it via pip:

pip install mysql-connector-python

Let's assume we have a MySQL database called 'testdb', and we want to insert a record in the 'employees' table.

import mysql.connector

# establish the connection
cnx = mysql.connector.connect(user='<username>', password='<password>',
                              host='127.0.0.1',
                              database='testdb')

# Create a cursor object using the cursor() method
cursor = cnx.cursor()

# Prepare SQL query to INSERT a record into the database
sql = """INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME)
         VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""

try:
   # Execute the SQL command
   cursor.execute(sql)
   # Commit your changes in the database
   cnx.commit()
except:
   # Rollback in case there is any error
   cnx.rollback()

# disconnect from server
cnx.close()

17.4.6 PostgreSQL

For PostgreSQL, you can use the psycopg2 library, which you can install via pip:

pip install psycopg2

Here is an example of inserting a record in PostgreSQL:

import psycopg2

#establishing the connection
cnx = psycopg2.connect(
   database="testdb", user='<username>', password='<password>', host='127.0.0.1', port= '5432'
)

#Creating a cursor object using the cursor() method
cursor = cnx.cursor()

# Preparing SQL queries to INSERT a record into the database.
sql = '''INSERT into EMPLOYEE (FIRST_NAME, LAST_NAME, AGE, SEX, INCOME)
         VALUES ('Mac', 'Mohan', 20, 'M', 2000)'''

# execute the SQL query using execute() method.
cursor.execute(sql)

# Commit your changes in the database
cnx.commit()

# close the cursor and connection
cursor.close()
cnx.close()

Remember that you must always close the cursor and connection object once you're done with them to avoid database memory issues.

17.4 Performing CRUD Operations

Before moving on, we should establish a test database and a table to play with. Below is the Python code to create a new SQLite database named 'test_db.sqlite' and a table named 'employees':

import sqlite3
conn = sqlite3.connect('test_db.sqlite')

c = conn.cursor()

# Create table
c.execute('''CREATE TABLE employees
             (id INTEGER PRIMARY KEY, name text, salary real, department text, position text, hireDate text)''')

# Commit the changes and close the connection
conn.commit()
conn.close()

17.4.1 Create Operation

The Create operation is used to add new records to a database. Here is an example of how you can add a new record to the 'employees' table:

conn = sqlite3.connect('test_db.sqlite')
c = conn.cursor()

# Insert a new employee record
c.execute("INSERT INTO employees VALUES (1, 'John Doe', 50000, 'HR', 'Manager', '2023-01-05')")

# Commit the changes
conn.commit()

# Close the connection
conn.close()

17.4.2 Read Operation

The Read operation is used to retrieve data from a database. Here is an example of how to retrieve all records from the 'employees' table:

conn = sqlite3.connect('test_db.sqlite')
c = conn.cursor()

# Select all rows from the employees table
c.execute('SELECT * FROM employees')

rows = c.fetchall()

for row in rows:
    print(row)

# Close the connection
conn.close()

17.4.3 Update Operation

The Update operation is used to modify existing records in a database. Here is an example of how to update a record in the 'employees' table:

conn = sqlite3.connect('test_db.sqlite')
c = conn.cursor()

# Update employee salary
c.execute("UPDATE employees SET salary = 60000 WHERE name = 'John Doe'")

# Commit the changes
conn.commit()

# Close the connection
conn.close()

17.4.4 Delete Operation

The Delete operation is used to remove records from a database. Here is an example of how to remove a record from the 'employees' table:

conn = sqlite3.connect('test_db.sqlite')
c = conn.cursor()

# Delete an employee record
c.execute("DELETE from employees WHERE name = 'John Doe'")

# Commit the changes
conn.commit()

# Close the connection
conn.close()

17.4.5 MySQL

To interact with a MySQL database, you will need a Python library called mysql-connector-python. You can install it via pip:

pip install mysql-connector-python

Let's assume we have a MySQL database called 'testdb', and we want to insert a record in the 'employees' table.

import mysql.connector

# establish the connection
cnx = mysql.connector.connect(user='<username>', password='<password>',
                              host='127.0.0.1',
                              database='testdb')

# Create a cursor object using the cursor() method
cursor = cnx.cursor()

# Prepare SQL query to INSERT a record into the database
sql = """INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME)
         VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""

try:
   # Execute the SQL command
   cursor.execute(sql)
   # Commit your changes in the database
   cnx.commit()
except:
   # Rollback in case there is any error
   cnx.rollback()

# disconnect from server
cnx.close()

17.4.6 PostgreSQL

For PostgreSQL, you can use the psycopg2 library, which you can install via pip:

pip install psycopg2

Here is an example of inserting a record in PostgreSQL:

import psycopg2

#establishing the connection
cnx = psycopg2.connect(
   database="testdb", user='<username>', password='<password>', host='127.0.0.1', port= '5432'
)

#Creating a cursor object using the cursor() method
cursor = cnx.cursor()

# Preparing SQL queries to INSERT a record into the database.
sql = '''INSERT into EMPLOYEE (FIRST_NAME, LAST_NAME, AGE, SEX, INCOME)
         VALUES ('Mac', 'Mohan', 20, 'M', 2000)'''

# execute the SQL query using execute() method.
cursor.execute(sql)

# Commit your changes in the database
cnx.commit()

# close the cursor and connection
cursor.close()
cnx.close()

Remember that you must always close the cursor and connection object once you're done with them to avoid database memory issues.

Purchase this book