Python MySQL - mysql-connector Driver
MySQL is the most popular relational database management system. If you are not familiar with MySQL, you can read our MySQL Tutorial.
In this section, we will introduce how to connect to MySQL using mysql-connector, which is a driver provided by MySQL itself.
We can install mysql-connector using the pip command:
python -m pip install mysql-connector
Use the following code to test if mysql-connector is installed successfully:
demo_mysql_test.py:
import mysql.connector
Executing the above code, if no errors occur, indicates a successful installation.
Note: If your MySQL is version 8.0, the password plugin verification method has changed. The earlier version used mysql_native_password, while version 8.0 uses caching_sha2_password. Therefore, some changes are needed:
First, modify the my.ini configuration:
[mysqld]
default_authentication_plugin=mysql_native_password
Then, execute the following command under mysql to change the password:
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'new_password';
For more details, refer to: Python MySQL 8.0 Connection Issues.
Creating a Database Connection
You can use the following code to connect to the database:
demo_mysql_test.py:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost", # Database host address
user="yourusername", # Database username
passwd="yourpassword" # Database password
)
print(mydb)
Creating a Database
To create a database, use the "CREATE DATABASE" statement. The following creates a database named tutorialpro_db:
demo_mysql_test.py:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456"
)
mycursor = mydb.cursor()
mycursor.execute("CREATE DATABASE tutorialpro_db")
Before creating a database, you can also use the "SHOW DATABASES" statement to check if the database exists:
demo_mysql_test.py:
Output all database lists:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456"
)
mycursor = mydb.cursor()
mycursor.execute("SHOW DATABASES")
for x in mycursor:
print(x)
Alternatively, you can directly connect to the database. If the database does not exist, an error message will be output:
demo_mysql_test.py:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="tutorialpro_db"
)
Creating a Data Table
To create a data table, use the "CREATE TABLE" statement. Before creating a data table, ensure that the database already exists. The following creates a table named sites:
demo_mysql_test.py:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="tutorialpro_db"
)
mycursor = mydb.cursor()
mycursor.execute("CREATE TABLE sites (name VARCHAR(255), url VARCHAR(255))")
mycursor.execute("CREATE TABLE sites (name VARCHAR(255), url VARCHAR(255))")
demo_mysql_test.py:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="tutorialpro_db"
)
mycursor = mydb.cursor()
mycursor.execute("SHOW TABLES")
for x in mycursor:
print(x)
Primary Key Setup
When creating a table, we generally set a primary key (PRIMARY KEY). We can use the "INT AUTO_INCREMENT PRIMARY KEY" statement to create a primary key, with the starting value of 1, incrementing by one each time.
If our table has already been created, we need to use ALTER TABLE to add a primary key to the table:
demo_mysql_test.py:
Adding a primary key to the sites table.
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="tutorialpro_db"
)
mycursor = mydb.cursor()
mycursor.execute("ALTER TABLE sites ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY")
If you have not yet created the sites table, you can directly use the following code to create it.
demo_mysql_test.py:
Creating a table with a primary key.
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="tutorialpro_db"
)
mycursor = mydb.cursor()
mycursor.execute("CREATE TABLE sites (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), url VARCHAR(255))")
Inserting Data
Insert data using the "INSERT INTO" statement:
demo_mysql_test.py:
Inserting a record into the sites table.
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="tutorialpro_db"
)
mycursor = mydb.cursor()
sql = "INSERT INTO sites (name, url) VALUES (%s, %s)"
val = ("tutorialpro", "https://www.tutorialpro.org")
mycursor.execute(sql, val)
mydb.commit() # The contents of the data table are updated, and this statement must be used
print(mycursor.rowcount, "record inserted successfully.")
Executing the code, the output is:
1 record inserted successfully
Batch Insert
Batch insert uses the executemany() method, where the second parameter is a list of tuples containing the data we want to insert:
demo_mysql_test.py:
Inserting multiple records into the sites table.
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="tutorialpro_db"
)
mycursor = mydb.cursor()
sql = "INSERT INTO sites (name, url) VALUES (%s, %s)"
val = [
('Google', 'https://www.google.com'),
('Github', 'https://www.github.com'),
('Taobao', 'https://www.taobao.com'),
mycursor.executemany(sql, val)
mydb.commit() # The statement is necessary if the contents of the data table are updated
print(mycursor.rowcount, "records inserted successfully.")
Executing the code, the output is:
4 records inserted successfully.
After executing the above code, let's take a look at the records in the data table:
If we want to get the ID of the record after inserting it, we can use the following code:
demo_mysql_test.py:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="tutorialpro_db"
)
mycursor = mydb.cursor()
sql = "INSERT INTO sites (name, url) VALUES (%s, %s)"
val = ("Zhihu", "https://www.zhihu.com")
mycursor.execute(sql, val)
mydb.commit()
print("1 record inserted, ID:", mycursor.lastrowid)
Executing the code, the output is:
1 record inserted, ID: 6
Querying Data
To query data, use the SELECT statement:
demo_mysql_test.py:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="tutorialpro_db"
)
mycursor = mydb.cursor()
mycursor.execute("SELECT * FROM sites")
myresult = mycursor.fetchall() # fetchall() fetches all records
for x in myresult:
print(x)
Executing the code, the output is:
(1, 'tutorialpro', 'https://www.tutorialpro.org')
(2, 'Google', 'https://www.google.com')
(3, 'Github', 'https://www.github.com')
(4, 'Taobao', 'https://www.taobao.com')
(5, 'stackoverflow', 'https://www.stackoverflow.com/')
(6, 'Zhihu', 'https://www.zhihu.com')
You can also read specified field data:
demo_mysql_test.py:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="tutorialpro_db"
)
mycursor = mydb.cursor()
mycursor.execute("SELECT name, url FROM sites")
myresult = mycursor.fetchall()
for x in myresult:
print(x)
Executing the code, the output is:
('tutorialpro', 'https://www.tutorialpro.org')
('Google', 'https://www.google.com')
('Github', 'https://www.github.com')
('Taobao', 'https://www.taobao.com')
('stackoverflow', 'https://www.stackoverflow.com/')
('Zhihu', 'https://www.zhihu.com')
If we only want to read one piece of data, we can use the fetchone() method:
demo_mysql_test.py:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="tutorialpro_db"
)
mycursor = mydb.cursor()
mycursor.execute("SELECT * FROM sites")
myresult = mycursor.fetchone()
print(myresult)
Executing the code, the output is:
(1, 'tutorialpro', 'https://www.tutorialpro.org')
WHERE Condition Statement
To read data with specific conditions, you can use the WHERE statement:
demo_mysql_test.py
Read records where the name field is 'tutorialpro':
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="tutorialpro_db"
)
mycursor = mydb.cursor()
sql = "SELECT * FROM sites WHERE name ='tutorialpro'"
mycursor.execute(sql)
myresult = mycursor.fetchall()
for x in myresult:
print(x)
Executing the code, the output is:
(1, 'tutorialpro', 'https://www.tutorialpro.org')
You can also use the wildcard %:
demo_mysql_test.py
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="tutorialpro_db"
)
mycursor = mydb.cursor()
sql = "SELECT * FROM sites WHERE url LIKE '%oo%'"
mycursor.execute(sql)
myresult = mycursor.fetchall()
for x in myresult:
print(x)
Executing the code, the output is:
(1, 'tutorialpro', 'https://www.tutorialpro.org')
(2, 'Google', 'https://www.google.com')
To prevent SQL injection attacks, we can use the %s placeholder to escape query conditions:
demo_mysql_test.py
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="tutorialpro_db"
)
mycursor = mydb.cursor()
sql = "SELECT * FROM sites WHERE name = %s"
na = ("tutorialpro", )
mycursor.execute(sql, na)
myresult = mycursor.fetchall()
for x in myresult:
print(x)
Sorting
To sort query results, you can use the ORDER BY statement. The default sorting method is ascending, with the keyword ASC. To set descending order, use the keyword DESC.
demo_mysql_test.py
Sort by the name field in ascending alphabetical order:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="tutorialpro_db"
)
mycursor = mydb.cursor()
sql = "SELECT * FROM sites ORDER BY name"
mycursor.execute(sql)
myresult = mycursor.fetchall()
for x in myresult:
print(x)
Executing the code, the output is:
(3, 'Github', 'https://www.github.com')
(2, 'Google', 'https://www.google.com')
(1, 'tutorialpro', 'https://www.tutorialpro.org') (5, 'stackoverflow', 'https://www.stackoverflow.com/') (4, 'Taobao', 'https://www.taobao.com') (6, 'Zhihu', 'https://www.zhihu.com')
Descending Order Sorting Example:
demo_mysql_test.py
Sorting by the name field in descending alphabetical order:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="tutorialpro_db"
)
mycursor = mydb.cursor()
sql = "SELECT * FROM sites ORDER BY name DESC"
mycursor.execute(sql)
myresult = mycursor.fetchall()
for x in myresult:
print(x)
Executing the code, the output is:
(6, 'Zhihu', 'https://www.zhihu.com')
(4, 'Taobao', 'https://www.taobao.com')
(5, 'stackoverflow', 'https://www.stackoverflow.com/')
(1, 'tutorialpro', 'https://www.tutorialpro.org')
(2, 'Google', 'https://www.google.com')
(3, 'Github', 'https://www.github.com')
Limit
If we want to set the amount of data to query, we can specify it with the "LIMIT" statement.
demo_mysql_test.py
Reading the first 3 records:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="tutorialpro_db"
)
mycursor = mydb.cursor()
mycursor.execute("SELECT * FROM sites LIMIT 3")
myresult = mycursor.fetchall()
for x in myresult:
print(x)
Executing the code, the output is:
(1, 'tutorialpro', 'https://www.tutorialpro.org')
(2, 'Google', 'https://www.google.com')
(3, 'Github', 'https://www.github.com')
You can also specify the starting position using the OFFSET keyword:
demo_mysql_test.py
Reading the first 3 records starting from the second record:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="tutorialpro_db"
)
mycursor = mydb.cursor()
mycursor.execute("SELECT * FROM sites LIMIT 3 OFFSET 1") # 0 is the first record, 1 is the second, and so on
myresult = mycursor.fetchall()
for x in myresult:
print(x)
Executing the code, the output is:
(2, 'Google', 'https://www.google.com')
(3, 'Github', 'https://www.github.com')
(4, 'Taobao', 'https://www.taobao.com')
Deleting Records
To delete records, use the "DELETE FROM" statement:
demo_mysql_test.py
Deleting the record where name is stackoverflow:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="tutorialpro_db"
)
mycursor = mydb.cursor()
sql = "DELETE FROM sites WHERE name = 'stackoverflow'"
mycursor.execute(sql)
mydb.commit()
print(mycursor.rowcount, " record(s) deleted")
Executing the code, the output is:
1 record(s) deleted
Note: Use the DELETE statement with caution. Ensure that the WHERE clause is specified; otherwise, the entire table's data will be deleted.
To prevent SQL injection attacks, we can use the %s placeholder to escape the conditions in the DELETE statement:
demo_mysql_test.py
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="tutorialpro_db"
)
mycursor = mydb.cursor()
sql = "DELETE FROM sites WHERE name = %s"
na = ("stackoverflow", )
mycursor.execute(sql, na)
mydb.commit()
print(mycursor.rowcount, " record(s) deleted")
Executing the code, the output is:
1 record(s) deleted
Updating Table Data
To update data in a table, use the "UPDATE" statement:
demo_mysql_test.py
Change the field data for name "Zhihu" to "ZH":
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="tutorialpro_db"
)
mycursor = mydb.cursor()
sql = "UPDATE sites SET name = 'ZH' WHERE name = 'Zhihu'"
mycursor.execute(sql)
mydb.commit()
print(mycursor.rowcount, " record(s) modified")
Executing the code, the output is:
1 record(s) modified
Note: Ensure that the WHERE clause is specified in the UPDATE statement; otherwise, the entire table's data will be updated.
To prevent SQL injection attacks, we can use the %s placeholder to escape the conditions in the UPDATE statement:
demo_mysql_test.py
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="tutorialpro_db"
)
mycursor = mydb.cursor()
sql = "UPDATE sites SET name = %s WHERE name = %s"
val = ("Zhihu", "ZH")
mycursor.execute(sql, val)
mydb.commit()
print(mycursor.rowcount, " record(s) modified")
Executing the code, the output is:
1 record(s) modified
Deleting a Table
To delete a table, use the "DROP TABLE" statement. The IF EXISTS keyword is used to check if the table exists before deleting it:
demo_mysql_test.py
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="tutorialpro_db"
)
mycursor = mydb.cursor()
sql = "DROP TABLE IF EXISTS sites" # Deletes the sites table
mycursor.execute(sql)