Database Operations and Indexing

Table of Contents- Using Python to Interact with MySQL

SQL Injection Issues in pymysql
Other Operations: Insert, Update, Delete
Indexes
- Types of Indexes
- Primary Key Index
- Unique Index
- Regular Index
- Situations Where Indexes Are Not Used
Slow Query Logs

Using Python to Interact with MySQL

Install the library:

pip install pymysql

import pymysql

# Database connection parameters
connection = pymysql.connect(
    host='localhost', user='root', password='Cql123456',
    database='test1', charset='utf8'
)

cursor = connection.cursor(cursor=pymysql.cursors.DictCursor)  # Controls return type as dictionary

query = 'SELECT * FROM boy_to_girl'

cursor.execute(query)  # Execute the query

# print(cursor.fetchone())  # Returns a dictionary, note cursor position for multiple reads

# print(cursor.fetchall())  # Returns a list of dictionaries, note cursor position for multiple reads

print(cursor.fetchmany(3))  # Returns a specified number of records, returns a list of dictionaries

# Close resources to free memory
cursor.close()
connection.close()

SQL Injection Issues in pymysql

Cause:

No validation of user input
Subsequent code may act as comments

import pymysql

connection = pymysql.connect(
    host='localhost', user='root', password='Cql123456',
    database='test1', charset='utf8'
)
cursor = connection.cursor(cursor=pymysql.cursors.DictCursor)

username = input('Enter username:')
password = input('Enter password:')

query = "SELECT * FROM nick WHERE name = '%s' AND pwd = '%s'" % (username, password)
print(query)

cursor.execute(query)

print(cursor.fetchall())

'''
Enter username: tank'#
Enter password: 456
SELECT * FROM nick WHERE name = 'tank'#' AND pwd = '456'
[{'id': 1, 'name': 'tank', 'pwd': 'tank123'}]
'''

Solution: Pass user input along with the SQL statement containing % placeholders to cursor.execute. This method automatically validates and escapes the input.

import pymysql

connection = pymysql.connect(
    host='localhost', user='root', password='Cql123456',
    database='test1', charset='utf8'
)
cursor = connection.cursor(cursor=pymysql.cursors.DictCursor)

username = input('Enter username:')
password = input('Enter password:')

query = "SELECT * FROM nick WHERE name = %s AND pwd = %s"  # Note: %s without quotes
print(query)

cursor.execute(query, (username, password))

print(cursor.fetchall())

'''
Enter username: tank
Enter password: tank123
SELECT * FROM nick WHERE name = %s AND pwd = %s
[{'id': 1, 'name': 'tank', 'pwd': 'tank123'}]
'''

Other Operations: Insert, Update, Delete

Inserting Records

import pymysql

connection = pymysql.connect(
    host='localhost', user='root', password='Cql123456',
    database='test1', charset='utf8'
)
cursor = connection.cursor(cursor=pymysql.cursors.DictCursor)

# Single record information
# username = input('Enter username:')
# password = input('Enter password:')

# Multiple records information
data = [
    ('jason1', 'pwd1'),
    ('jason2', 'pwd2'),
    ('jason3', 'pwd3')
]

query = "INSERT INTO nick (name, pwd) VALUES (%s, %s)"
print(query)

# cursor.execute(query, (username, password))  # Insert one record
cursor.executemany(query, data)  # Insert multiple records

connection.commit()  # Commit changes to disk

# Close resources to free memory
cursor.close()
connection.close()

Updating a Record

import pymysql

connection = pymysql.connect(
    host='localhost', user='root', password='Cql123456',
    database='test1', charset='utf8'
)
cursor = connection.cursor(cursor=pymysql.cursors.DictCursor)

query = "UPDATE nick SET name = %s, pwd = %s WHERE id = 4"
print(query)

cursor.execute(query, ('dragon', 'cql123456'))

connection.commit()  # Commit changes to disk

# Close resources to free memory
cursor.close()
connection.close()

Deleting a Record

import pymysql

connection = pymysql.connect(
    host='localhost', user='root', password='Cql123456',
    database='test1', charset='utf8'
)
cursor = connection.cursor(cursor=pymysql.cursors.DictCursor)

query = "DELETE FROM nick WHERE name = %s"
print(query)

cursor.execute(query, ('tank',))

connection.commit()  # Commit changes to disk

# Close resources to free memory
cursor.close()
connection.close()

Indexes

Key has two meanings and functions:
- Constraint (ensuring database structure integrity)---constraint
- Index (assisting queries)
Index is at the implementation level and does not constrain the behavior of the indexed field---index

Function: Improves query efficiency
Analogy: The index of a dictionary
Essence: A special file
Basic principle: B+ tree

Types of Indexes

Primary Key Index: Accelerates search + no duplicates + not null
Unique Index: Accelerates search + no duplicates

Combined unique index
Regular Index: Accelerates search

Combined index

Primary Key Index

# Add primary key index after table creation
ALTER TABLE table_name 
ADD PRIMARY KEY (id);

# Add primary key index during table creation
CREATE TABLE table_name (
    id INT AUTO_INCREMENT,
    PRIMARY KEY (id)
) CHARSET utf8;
    
# Remove primary key index, if auto-increment is present, remove it first
ALTER TABLE table_name 
DROP PRIMARY KEY;

Unique Index

SELECT COUNT(real_name) FROM test_primary_key;
+------------------+
| COUNT(real_name) |
+------------------+
|          4000001 |
+------------------+
1 row in set (2.26 sec)  # Query time before adding unique index

# Add unique index after table creation
ALTER TABLE test_primary_key
ADD CONSTRAINT uqe_real_name UNIQUE INDEX (real_name);

SELECT COUNT(real_name) FROM test_primary_key;
+------------------+
| COUNT(real_name) |
+------------------+
|          4000001 |
+------------------+
1 row in set (0.77 sec)  # Query time after adding unique index

# Add unique index during table creation
CREATE TABLE unique_index (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(32),
    CONSTRAINT uqe_name UNIQUE KEY (name)
) CHARSET utf8;
# At this point, unique key, unique index, and unique are synonymous

# Remove unique index using the index keyword
ALTER TABLE test_primary_key
DROP INDEX uqe_real_name;

Regular Index

# Add regular index after table creation
ALTER TABLE test_primary_key
ADD INDEX ix_real_name (real_name);  # Declare index name and field

# Add regular index during table creation
CREATE TABLE ordinary_index (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(32),
    INDEX ix_name (name) 
) CHARSET utf8;

# Remove regular index
ALTER TABLE test_primary_key
DROP INDEX ix_real_name;

Index advantages and disadvantages: improves query speed but increases disk space usage.

Situations Where Indexes Are Not Used

Too many indexes can affect performance
Some SQL statements may not use indexes

Avoid arithmetic operations in WHERE clauses, which reduce query efficiency
- SELECT COUNT(id) FROM test_primary_key WHERE id*2 = 1000;
Use of functions:
- SELECT * FROM t WHERE REVERSE(email) = "zekai";
Type mismatch: If the field type is string, the WHERE condition must also be string. Large or fuzzy ranges reduce search efficiency
- SELECT * FROM t WHERE email = 999;
Sorting by indexed fields: SELECT fields must also be indexed, otherwise indexes won't be used
- SELECT name FROM t ORDER BY email DESC;
Replace count(column) with count(*)

Leftmost Prefix of Composite (Combined) Index

# index (a, b, c)
# WHERE a = 2 ...  # Hits
# WHERE (b...) OR (c...)  # Misses

Use explain to get query reports

EXPLAIN SELECT COUNT(real_name) FROM test_primary_key\G
'''
         id: 1
  select_type: SIMPLE  # Index type, all means full table scan
        table: test_primary_key
   partitions: NULL  # Partitions
         type: index  # Index type
possible_keys: NULL  # Possible indexes
          key: ix_real_name  # Actual indexes used
      key_len: 98  # Length of index
          ref: NULL
         rows: 3992630  # Number of rows scanned
     filtered: 100.00
        Extra: Using index  # Index used
1 row in set, 1 warning (0.00 sec)
'''

Index coverage: SELECT * FROM test_primary_key WHERE id = 2000;

Slow Query Logs

Check slow SQL variables: SHOW VARIABLES LIKE "%slow%"

+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| log_slow_admin_statements | OFF   |
| log_slow_slave_statements | OFF   |
| slow_launch_time          | 2     |
| slow_query_log            | OFF   |  # Default is off, set to on to enable
| slow_query_log_file       | G:\   |  # Location of slow query log
+---------------------------+-------+

Check the longest query time variable: SHOW VARIABLES LIKE "%long%"

+----------------------------------------------------------+-----------+
| Variable_name                                            | Value     |
+----------------------------------------------------------+-----------+
| long_query_time                                          | 10.000000 |
| performance_schema_events_stages_history_long_size       | 10000     |
| performance_schema_events_statements_history_long_size   | 10000     |
| performance_schema_events_transactions_history_long_size | 10000     |
| performance_schema_events_waits_history_long_size        | 10000     |
+----------------------------------------------------------+-----------+
# long_query_time indicates the maximum acceptable query time

Configure slow query logs: SET GLOBAL variable_name = value

SET GLOBAL slow_query_log = ON;

SET GLOBAL slow_query_log_file = "G:/software/compressed/mysql-5.7.28/slow_sql_log";  # Use / for Windows file paths

SET GLOBAL long_query_time = 1;

SELECT COUNT(real_name) FROM test_primary_key;  # (3.23 sec), After configuration, exit client and reconnect before querying to record slow SQL

Tags: python MySQL pymysql sql-injection Index

Posted on Wed, 01 Jul 2026 16:52:40 +0000 by MadnessRed

Freaks City