YouTipPython Mysql Connector
## Python3.x 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 (#).
In this chapter, we will introduce you to using **mysql-connector** to connect to MySQL. **mysql-connector** is the official driver provided by **MySQL**.
We can use the **pip** command to install **mysql-connector**:
```python
python -m pip install mysql-connector
Use the following code to test if mysql-connector is installed successfully:
## demo_mysql_test.py:
```python
import mysql.connector
Execute the above code, if no error occurs, the installation is successful.
> **Note:** If your MySQL is version 8.0, the password plugin verification method has changed. The early version uses mysql_native_password, and version 8.0 uses caching_sha2_password, so some changes are needed:
>
> First, modify the my.ini configuration:
>
> default_authentication_plugin=mysql_native_password
>
> Then execute the following command in mysql to modify the password:
>
> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'newpassword';
> For more information, refer to: [Python MySQL8.0 Connection Issue](#).
* * *
## Create Database Connection
You can use the following code to connect to the database:
## demo_mysql_test.py:
```python
import mysql.connector
mydb = mysql.connector.connect(host="localhost", user="yourusername", passwd="yourpassword")
print(mydb)
### Create Database
To create a database, use the "CREATE DATABASE" statement. The following creates a database named tutorial_db:
## demo_mysql_test.py:
```python
import mysql.connector
mydb = mysql.connector.connect(host="localhost", user="root", passwd="123456")
mycursor = mydb.cursor()
mycursor.execute("CREATE DATABASE tutorial_db")
Before creating a database, we can also use the "SHOW DATABASES" statement to check if the database exists:
## demo_mysql_test.py:
Output all database lists:
```python
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 directly connect to the database. If the database does not exist, an error will be output:
## demo_mysql_test.py:
```python
import mysql.connector
mydb = mysql.connector.connect(host="localhost", user="root", passwd="123456", database="tutorial_db")
* * *
## Create Table
To create a table, use the **"CREATE TABLE"** statement. Before creating a table, you need to ensure the database exists. The following creates a table named **sites**:
## demo_mysql_test.py:
```python
import mysql.connector
mydb = mysql.connector.connect(host="localhost", user="root", passwd="123456", database="tutorial_db")
mycursor = mydb.cursor()
mycursor.execute("CREATE TABLE sites (name VARCHAR(255), url VARCHAR(255))")
After successful execution, we can see the table sites created in the database, with fields name and url.
!(#)
**We can also use the **"SHOW TABLES"** statement to check if the table already exists:**
## demo_mysql_test.py:
```python
import mysql.connector
mydb = mysql.connector.connect(host="localhost", user="root", passwd="123456", database="tutorial_db")
mycursor = mydb.cursor()
mycursor.execute("SHOW TABLES")
for x in mycursor:
print(x)
### Set Primary Key
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. The primary key starts at 1 and increments automatically.
If our table is already created, we need to use **ALTER TABLE** to add a primary key to the table:
## demo_mysql_test.py:
Add primary key to sites table.
```python
import mysql.connector
mydb = mysql.connector.connect(host="localhost", user="root", passwd="123456", database="tutorial_db")
mycursor = mydb.cursor()
mycursor.execute("ALTER TABLE sites ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY")
If you have not created the sites table yet, you can directly use the following code to create it.
## demo_mysql_test.py:
Create primary key for the table.
```python
import mysql.connector
mydb = mysql.connector.connect(host="localhost", user="root", passwd="123456", database="tutorial_db")
mycursor = mydb.cursor()
mycursor.execute("CREATE TABLE sites (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), url VARCHAR(255))")
* * *
## Insert Data