Python MySQL – mysql-connector driver
Python MySQL –
We can use the pip command to install mysql-connector:
python -m pip install mysql-connector
Use the following code to test whether mysql-connector is installed successfully:
demo_mysql_test.py:
import mysql.connector
Execute the above code. If no errors are generated, the installation is successful.
Note: If your MySQL version is 8.0, the password plugin authentication method has changed. Earlier versions used mysql_native_password, while version 8.0 uses caching_sha2_password. Therefore, you need to make some changes:
First, modify the my.ini configuration:
[mysqld] default_authentication_plugin=mysql_native_passwordThen, execute the following command in MySQL to change the password:
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'new password';For more information, please refer to: Python MySQL 8.0 Linking 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
Use the “CREATE DATABASE” statement to create a database named geekdoc_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 geekdoc_db")
Before creating a database, we can also use the “SHOW DATABASES” statement to check whether the database exists:
demo_mysql_test.py:
Output a list of all databases:
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)
Or we can connect directly 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="geekdoc_db"
)
Creating a Table
Use the CREATE TABLE statement to create a table. Before creating a table, ensure that the database 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="geekdoc_db"
)
mycursor = mydb.cursor()
mycursor.execute("CREATE TABLE sites (name VARCHAR(255), url VARCHAR(255))")
After successful execution, we can see the database created with the data table sites, with the fields name and url.
We can also use the “SHOW TABLES” statement to check if the database table exists:
demo_mysql_test.py:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="geekdoc_db"
)
mycursor = mydb.cursor()
mycursor.execute("SHOW TABLES")
for x in mycursor:
print(x)
Primary Key Settings
When creating a table, we typically set a primary key. We can use the INT AUTO_INCREMENT PRIMARY KEY statement to create a primary key. The primary key starts at 1 and increments incrementally.
If the table has already been created, we need to use ALTER TABLE to add a primary key:
demo_mysql_test.py:
Add a primary key to the sites table.
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="geekdoc_db"
)
mycursor = mydb.cursor()
mycursor.execute("ALTER TABLE sites ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY")
If you haven’t created the sites table yet, you can create it directly using the following code.
demo_mysql_test.py:
Create a primary key for the table.
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="geekdoc_db"
)
mycursor = mydb.cursor()
mycursor.execute("CREATE TABLE sites (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), url VARCHAR(255))")
Inserting Data
To insert data, use the “INSERT INTO” statement:
demo_mysql_test.py:
Insert a record into the sites table.
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="geekdoc_db"
)
mycursor = mydb.cursor()
sql = "INSERT INTO sites (name, url) VALUES (%s, %s)"
val = ("GEEKDOC", "https://www.geek-docs.com")
mycursor.execute(sql, val)
mydb.commit() # The table contents have been updated; this statement is required.
print(mycursor.rowcount, "Record inserted successfully.")
Execute the code, and the output is:
1 record inserted successfully
Batch Insert
Batch Insert using executemany() method, whose second argument is a list of tuples containing the data to be inserted:
demo_mysql_test.py:
Insert multiple records into the sites table.
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="geekdoc_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'),
('stackoverflow', 'https://www.stackoverflow.com/')
]
mycursor.executemany(sql, val)
mydb.commit() # This statement is required to update the table contents.
print(mycursor.rowcount, "Record inserted successfully.")
Execute the code, and the output is:
4 records inserted successfully.
After executing the above code, we can view the records in the database table:
If we want to get the ID of the data record after it is inserted, we can use the following code:
demo_mysql_test.py:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="geekdoc_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)
Execute the code, the output is:
1 record inserted, ID: 6
Query Data
Query data using the SELECT statement:
demo_mysql_test.py:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="geekdoc_db"
)
mycursor = mydb.cursor()
mycursor.execute("SELECT * FROM sites")
myresult = mycursor.fetchall() # fetch all records
for x in myresult:
print(x)
Execute the code, the output is:
(1, 'GEEKDOC', 'https://www.geek-docs.com')
(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 data from a specific field:
demo_mysql_test.py:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="geekdoc_db"
)
mycursor = mydb.cursor()
mycursor.execute("SELECT name, url FROM sites")
myresult = mycursor.fetchall()
for x in myresult:
print(x)
Execute the code and the output is:
('GEEKDOC', 'https://www.geek-docs.com')
('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",
database="geekdoc_db"
)
mycursor = mydb.cursor()
mycursor.execute("SELECT * FROM sites")
myresult = mycursor.fetchone()
print(myresult)
Execute the code, the output is:
(1, 'GEEKDOC', 'https://www.geek-docs.com')
Where Statement
If we want to read data with a specified condition, we can use the where statement:
demo_mysql_test.py
Read the record where the name field is GEEKDOC:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="geekdoc_db"
mycursor = mydb.cursor()
sql = "SELECT * FROM sites WHERE name = 'GEEKDOC'"
mycursor.execute(sql)
myresult = mycursor.fetchall()
for x in myresult:
print(x)
Execute the code, the output is:
(1, 'GEEKDOC', 'https://www.geek-docs.com')
You can also use the wildcard %:
demo_mysql_test.py
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="geekdoc_db"
mycursor = mydb.cursor()
sql = "SELECT * FROM sites WHERE url LIKE '%oo%'"
mycursor.execute(sql)
myresult = mycursor.fetchall()
for x in myresult:
print(x)
Execute the code, and the output is:
(1, 'GEEKDOC', 'https://www.geek-docs.com')
(2, 'Google', 'https://www.google.com')
To prevent SQL injection attacks in database queries, we can use %s Placeholders are used to escape query conditions:
demo_mysql_test.py
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="geekdoc_db"
)
mycursor = mydb.cursor()
sql = "SELECT * FROM sites WHERE name = %s"
na = ("GEEKDOC", )
mycursor.execute(sql, na)
myresult = mycursor.fetchall()
for x in myresult:
print(x)
Sorting
Query results can be sorted using the ORDER BY clause. The default sorting order is ascending. The key is ASC, if you want to set descending order, you can set the keyword DESC.
demo_mysql_test.py
Sort in ascending alphabetical order by the name field:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="geekdoc_db"
)
mycursor = mydb.cursor()
sql = "SELECT * FROM sites ORDER BY name"
mycursor.execute(sql)
myresult = mycursor.fetchall()
for x in myresult:
print(x)
Execute the code, and the output is:
(3, 'Github', 'https://www.github.com')
(2, 'Google', 'https://www.google.com')
(1, 'GEEKDOC', 'https://www.geek-docs.com')
(5, 'stackoverflow', 'https://www.stackoverflow.com/')
(4, 'Taobao', 'https://www.taobao.com')
(6, 'Zhihu', 'https://www.zhihu.com')
Descending sort example:
demo_mysql_test.py
Sort by name field in descending alphabetical order:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="geekdoc_db"
)
mycursor = mydb.cursor()
sql = "SELECT * FROM sites ORDER BY name DESC"
mycursor.execute(sql)
myresult = mycursor.fetchall()
for x in myresult:
print(x)
Execute the code, the output is:
(6, 'Zhihu', 'https://www.zhihu.com')
(4, 'Taobao', 'https://www.taobao.com')
(5, 'stackoverflow', 'https://www.stackoverflow.com/')
(1, 'GEEKDOC', 'https://www.geek-docs.com')
(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 using the “LIMIT” statement.
demo_mysql_test.py
Read the first three records:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="geekdoc_db"
)
mycursor = mydb.cursor()
mycursor.execute("SELECT * FROM sites LIMIT 3")
myresult = mycursor.fetchall()
for x in myresult:
print(x)
Execute the code and the output is:
(1, 'GEEKDOC', 'https://www.geek-docs.com')
(2, 'Google', 'https://www.google.com')
(3, 'Github', 'https://www.github.com')
You can also specify the starting position using the keyword OFFSET:
demo_mysql_test.py
Starting from the second record, read the first three records:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="geekdoc_db"
)
mycursor = mydb.cursor()
mycursor.execute("SELECT * FROM sites LIMIT 3 OFFSET 1") # 0 is the first result, 1 is the second, and so on.
myresult = mycursor.fetchall()
for x in myresult:
print(x)
Execute the code and the output is:
(2, 'Google', 'https://www.google.com')
(3, 'Github', 'https://www.github.com')
(4, 'Taobao', 'https://www.taobao.com')
Delete Records
To delete records, use “DELETE FROM” Statement:
demo_mysql_test.py
Delete the record named stackoverflow:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="geekdoc_db"
)
mycursor = mydb.cursor()
sql = "DELETE FROM sites WHERE name = 'stackoverflow'"
mycursor.execute(sql)
mydb.commit()
print(mycursor.rowcount, " records deleted")
Execute the code, and the output is:
1 Delete records
Note: Use delete statements with caution. Ensure that the WHERE clause is specified; otherwise, the entire table will be deleted.
To prevent SQL injection attacks in database queries, we can use the %s placeholder to escape the delete statement condition:
demo_mysql_test.py
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="geekdoc_db"
)
mycursor = mydb.cursor()
sql = "DELETE FROM sites WHERE name = %s"
na = ("stackoverflow", )
mycursor.execute(sql, na)
mydb.commit()
print(mycursor.rowcount, " records deleted")
Execute the code, and the output is:
1 record modified
Note: The UPDATE statement must specify a WHERE clause; otherwise, the entire table will be updated.
To prevent SQL injection attacks in database queries, we can use the %s placeholder to escape the update statement condition:
demo_mysql_test.py
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="geekdoc_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, " records modified")
Execute the code, and the output is:
<h2>Deleting a table</h2>
<p>To delete a table, use the <strong>DROP TABLE</strong> statement. The IF EXISTS keyword is used to check whether the table exists and only delete it if it does:</p>
<p><strong>demo_mysql_test.py</strong></p>
<pre><code class="language-python line-numbers">import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="geekdoc_db"
)
mycursor = mydb.cursor()
sql = "DROP TABLE IF EXISTS sites" # Delete the sites table
mycursor.execute(sql)