Python topic-Mysql database (python3. _ + PyMysql),-mysqlpymysql

Python topic-Mysql database (python3. _ + PyMysql),-mysqlpymysql

I have previously written a blog about using the MySQL database in Python, mainly using the Python2 and MySQLdb drivers.

Use mysql Databases in python

• Python2 ---> Python3
• MySQLdb --> PyMySQL

 

1. Install PyMySQL

Python is a programming language, MySQL is a database, and they are two different technologies. To use Python to operate MySQL databases, you need to use a driver. The PyMySQL driver is used here. :

Https://pypi.python.org/pypi/PyMySQL

Https://github.com/PyMySQL/PyMySQL

Of course, the simplest installation method is to use the pip command.

> Pip install PyMySQL

Replace install with the show command to check whether PyMySQL is successfully installed.

 

 

2. Create a MySQL table

Execute the following SQL statement to create a users table.

CREATE TABLE `users` (    `id` INT(11) NOT NULL AUTO_INCREMENT,    `email` VARCHAR(255) COLLATE utf8_bin NOT NULL,    `password` VARCHAR(255) COLLATE utf8_bin NOT NULL,    PRIMARY KEY (`id`)) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_binAUTO_INCREMENT=1 ;

 

 

Iii. Python for MySQL operations

Next, we will focus on how to operate MySQL databases using Python.

4.1 Insert data:

Import pymysql. cursors # connect to the MySQL database connection = pymysql. connect (host = '2017. 0.0.1 ', port = 3306, user = 'root', password = '000000', db = 'guest ',
Charset = 'utf8mb4', cursorclass = pymysql. cursors. dictCursor) # Use cursor to create cursor = connection. cursor () # create an SQL statement and execute SQL = "INSERT INTO 'users' ('email ', 'Password') VALUES ('huzhiheng @ itest.info', '123 ') "cursor.exe cute (SQL) # submit SQLconnection. commit ()

No matter what tool or database you are using, it is essential to connect to the database. Host is the IP address of the Database host, port is the default port number of MySQL, user is the data user name, password is the database login password, db is the database name.

Cursor () method to create a database cursor.

Execute () method to execute the SQL statement.

Commit () submits database operations to Data.

 

4.2. query data

Import pymysql. cursors # connect to the MySQL database connection = pymysql. connect (host = '2017. 0.0.1 ', port = 3306, user = 'root', password = '000000', db = 'guest', charset = 'utf8mb4', cursorclass = pymysql. cursors. dictCursor) # Use cursor to create cursor = connection. cursor () # Run the Data Query SQL = "SELECT 'id', 'Password' FROM 'users' WHERE 'email '= 'huzhiheng @ itest.info'" cursor.exe cute (SQL) # query a single data record in the database result = cursor. fetchone () print (result) print ("----------- gorgeous split line ------------") # Run the Data Query SQL = "SELECT 'id ', 'Password' FROM 'users' "cursor.exe cute (SQL) # query multiple data entries in the database result = cursor. fetchall () for data in result: print (data) # disable the data connection. close ()

The next operation is the database query.

Fetchone () is used to query a single piece of data.

Fetchall () is used to query multiple data entries.

Close (). Do not forget to close the data connection.

Running result:

{'Password': '000000', 'id': 1} ----------- gorgeous split line ------------ {'Password': '000000', 'id': 1} {'Password ': '123', 'id': 2}