Chapter 7: Database Operations ​

This chapter covers the operations of SQL, so you need to learn about database installation and basic knowledge of database clients in advance.

Basic Concepts and Syntax of SQL Language ​

SQL (Structured Query Language) is a programming language used to operate relational databases, and it is one of the standard languages for databases. In SQL, a series of commands and syntax are used to query, insert, update, and delete data in a database. Below, we will introduce the basic concepts and syntax of SQL, as well as some commonly used SQL commands.

1. Databases and Tables

In SQL, data is organized into databases, with each database containing one or more tables. A table consists of columns and rows. Columns define the data types in the table, while rows represent individual records in the table.

For example, here is the definition of a table named users:

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    email VARCHAR(50)
);

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    email VARCHAR(50)
);

In this example, we create a table named users with four columns: id, name, age, and email. The id column serves as the primary key, uniquely identifying each record.

2. Querying Data

In SQL, the SELECT command is used to query data from the database. Here is an example of retrieving all records from the users table:

SELECT * FROM users;

SELECT * FROM users;

In this example, we use the SELECT command to query all records from the users table. The * symbol represents selecting all columns. If we only want to query specific columns, we can use the column names instead of *. For example:

SELECT name, age FROM users;

SELECT name, age FROM users;

In this example, we only query the name and age columns from the users table.

In addition to querying all records and specific columns, we can also perform operations such as sorting, filtering, and grouping on the query results. For example, here is an example of querying records from the users table where the age is greater than 25 and sorting them in ascending order by age:

SELECT * FROM users WHERE age > 25 ORDER BY age ASC;

SELECT * FROM users WHERE age > 25 ORDER BY age ASC;

In this example, we use the WHERE clause to filter records where the age is greater than 25 and the ORDER BY clause to sort the records in ascending order by age.

3. Inserting Data

In SQL, the INSERT INTO command is used to insert data into the database. Here is an example of inserting a record into the users table:

INSERT INTO users (id, name, age, email) VALUES (1, 'John', 25, '[email protected]');

INSERT INTO users (id, name, age, email) VALUES (1, 'John', 25, '[email protected]');

In this example, we use the INSERT INTO command to insert a record into the users table with values for the id, name, age, and email columns.

If we want to insert multiple records, we can combine the INSERT INTO command with the VALUES clause. For example, here is an example of inserting two records into the users table:

INSERT INTO users (id, name, age, email) VALUES
    (1, 'John', 25, '[email protected]'),
    (2, 'Mary', 30, '[email protected]');

INSERT INTO users (id, name, age, email) VALUES
    (1, 'John', 25, '[email protected]'),
    (2, 'Mary', 30, '[email protected]');

In this example, we use multiple values to insert two records.

4. Updating Data

In SQL, the UPDATE command is used to update data in the database. Here is an example of updating a record in the users table with the id of 1:

UPDATE users SET age = 26 WHERE id = 1;

UPDATE users SET age = 26 WHERE id = 1;

In this example, we use the UPDATE command to update the age column of the record with an id of 1 in the users table to 26.

If we want to update multiple records, we can combine the UPDATE command with the WHERE clause. For example, here is an example of updating the age column of records in the users table where the age is greater than 25:

UPDATE users SET age = age + 1 WHERE age > 25;

UPDATE users SET age = age + 1 WHERE age > 25;

In this example, we use the UPDATE command to update the age column of records in the users table where the age is greater than 25 by adding 1.

5. Deleting Data

In SQL, the DELETE FROM command is used to delete data from the database. Here is an example of deleting a record with an id of 1 from the users table:

DELETE FROM users WHERE id = 1;

DELETE FROM users WHERE id = 1;

In this example, we use the DELETE FROM command to delete the record with anid of 1 from the users table.

If we want to delete multiple records, we can combine the DELETE FROM command with the WHERE clause. For example, here is an example of deleting records from the users table where the age is greater than 30:

DELETE FROM users WHERE age > 30;

DELETE FROM users WHERE age > 30;

In this example, we use the DELETE FROM command to delete records from the users table where the age is greater than 30.

These are some of the basic SQL commands and concepts that you can use to interact with databases and perform operations such as querying, inserting, updating, and deleting data.

Make sure to familiarize yourself with the specific database management system you are using as there might be slight variations in syntax and additional features specific to the database system.

Updating data is another common operation in database operations. Here is an example of updating records in a MySQL database:

import pymysql

# Establish a database connection
conn = pymysql.connect(
    host='localhost',
    user='root',
    password='password',
    database='test'
)

# Create a cursor object
cursor = conn.cursor()

# Execute SQL statement
sql = "UPDATE users SET age = %s WHERE name = %s"
values = (26, 'John')
cursor.execute(sql, values)

# Commit the changes
conn.commit()

# Close the cursor object and database connection
cursor.close()
conn.close()

import pymysql

# Establish a database connection
conn = pymysql.connect(
    host='localhost',
    user='root',
    password='password',
    database='test'
)

# Create a cursor object
cursor = conn.cursor()

# Execute SQL statement
sql = "UPDATE users SET age = %s WHERE name = %s"
values = (26, 'John')
cursor.execute(sql, values)

# Commit the changes
conn.commit()

# Close the cursor object and database connection
cursor.close()
conn.close()

In this example, we update the age of a record named John in a table called users in a MySQL database. First, we establish a database connection and create a cursor object. Then, we define an SQL statement and parameter values. We execute the SQL statement using the cursor object and commit the changes. Finally, we close the cursor object and database connection.

4. Deleting Data

Deleting data is also a common operation in database operations. Here is an example of deleting records from a MySQL database:

import pymysql

# Establish a database connection
conn = pymysql.connect(
    host='localhost',
    user='root',
    password='password',
    database='test'
)

# Create a cursor object
cursor = conn.cursor()

# Execute SQL statement
sql = "DELETE FROM users WHERE name = %s"
values = ('John',)
cursor.execute(sql, values)

# Commit the changes
conn.commit()

# Close the cursor object and database connection
cursor.close()
conn.close()

import pymysql

# Establish a database connection
conn = pymysql.connect(
    host='localhost',
    user='root',
    password='password',
    database='test'
)

# Create a cursor object
cursor = conn.cursor()

# Execute SQL statement
sql = "DELETE FROM users WHERE name = %s"
values = ('John',)
cursor.execute(sql, values)

# Commit the changes
conn.commit()

# Close the cursor object and database connection
cursor.close()
conn.close()

In this example, we delete records with the name John from a table called users in a MySQL database. First, we establish a database connection and create a cursor object. Then, we define an SQL statement and parameter values. We execute the SQL statement using the cursor object and commit the changes. Finally, we close the cursor object and database connection.

These examples demonstrate how to connect to and operate on a database and provide some common database operations, including inserting data, updating data, and deleting data. By learning from these examples, we can better understand how to connect to and operate on databases in Python and apply them to real-world projects.

Updating data is another common operation in database operations. Here is an example of updating records in a MySQL database:

import pymysql

# Establish a database connection
conn = pymysql.connect(
    host='localhost',
    user='root',
    password='password',
    database='test'
)

# Create a cursor object
cursor = conn.cursor()

# Execute SQL statement
sql = "UPDATE users SET age = %s WHERE name = %s"
values = (26, 'John')
cursor.execute(sql, values)

# Commit the changes
conn.commit()

# Close the cursor object and database connection
cursor.close()
conn.close()

import pymysql

# Establish a database connection
conn = pymysql.connect(
    host='localhost',
    user='root',
    password='password',
    database='test'
)

# Create a cursor object
cursor = conn.cursor()

# Execute SQL statement
sql = "UPDATE users SET age = %s WHERE name = %s"
values = (26, 'John')
cursor.execute(sql, values)

# Commit the changes
conn.commit()

# Close the cursor object and database connection
cursor.close()
conn.close()

In this example, we update the age of a record named John in a table called users in a MySQL database. First, we establish a database connection and create a cursor object. Then, we define an SQL statement and parameter values. We execute the SQL statement using the cursor object and commit the changes. Finally, we close the cursor object and database connection.

4. Deleting Data

Deleting data is also a common operation in database operations. Here is an example of deleting records from a MySQL database:

import pymysql

# Establish a database connection
conn = pymysql.connect(
    host='localhost',
    user='root',
    password='password',
    database='test'
)

# Create a cursor object
cursor = conn.cursor()

# Execute SQL statement
sql = "DELETE FROM users WHERE name = %s"
values = ('John',)
cursor.execute(sql, values)

# Commit the changes
conn.commit()

# Close the cursor object and database connection
cursor.close()
conn.close()

import pymysql

# Establish a database connection
conn = pymysql.connect(
    host='localhost',
    user='root',
    password='password',
    database='test'
)

# Create a cursor object
cursor = conn.cursor()

# Execute SQL statement
sql = "DELETE FROM users WHERE name = %s"
values = ('John',)
cursor.execute(sql, values)

# Commit the changes
conn.commit()

# Close the cursor object and database connection
cursor.close()
conn.close()

In this example, we delete records with the name John from a table called users in a MySQL database. First, we establish a database connection and create a cursor object. Then, we define an SQL statement and parameter values. We execute the SQL statement using the cursor object and commit the changes. Finally, we close the cursor object and database connection.

These examples demonstrate how to connect to and operate on a database and provide some common database operations, including inserting data, updating data, and deleting data. By learning from these examples, we can better understand how to connect to and operate on databases in Python and apply them to real-world projects.