Use python to import the pymysql database to operate mysql (add, delete, modify, and query) and pythonpymysql

Use python to import the pymysql database to operate mysql (add, delete, modify, and query) and pythonpymysql

Install pymysql first. Run pip install pymysql on the command line.

After installation, connect to the mysql database:

Import pymysql. cursors

'''
Connect to database
'''

# Use the pymysql command to connect to the database

Connection = pymysql. connect (host = '192. 0.0.1 ', # IP address of the database to be connected

User = 'root', # name of the account to log on to. If you log on to an account with the highest permissions, It is root.
Password = '000000', # password
Db = 'iris ', # the database to connect
Charset = 'utf8mb4', # Set the encoding format
# How to store the returned results to Python, for example, Dict. Cursor is stored in a dictionary.
# If this row of data is not added, it is returned as a tuple.
Cursorclass = pymysql. cursors. DictCursor
)

In this way, we have established a connection between python and mysql.

 

Create a table
Try:

'''
Create a table
'''
# Use the cursor () method to obtain the operation cursor
With connection. cursor () as cursor:
# If the data table already exists, use the execute () method to delete the table.
Cursor.exe cute ("drop table if EXISTS users ")
SQL = ''' 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_bin
Auto_increment = 1;
'''
Cursor.exe cute (SQL)

Except t:
# Rollback when an error occurs
Connection. rollback ()

Finally:
# Closing a connection
Connection. close ()
Insert data
# Obtain the data structure of the cursor from the Database Link
With connection. cursor () as cursor:
SQL = "insert into users (email, password) VALUES (% s, % s )"
Cursor.exe cute (SQL, ('webmaster @ python.org ', 'Very _ secret '))
# When this command is executed, the database is actually changed. Previously, it was only cached in the memory.
Connection. commit ()
Batch insert data
'''
Batch insert data
'''
# Reading files
With open('iris.csv ', 'R', encoding = 'utf-8', newline = '') as f:
I = 0 # used to record the number of times the data in the file is read
Z = 0 # used to record the number of times the data in the file is read
List = []
For line in f:
Strs = line. split (',')
Data = (strs [0], strs [1], strs [2], strs [3], strs [4]. replace ("\ n", "") # The last line of the data contains a \ n line break, replaced with replace
List. append (data)
I + = 1
Z + = 1
If I> = 10: # used to insert 10 records at a time.
With connection. cursor () as cursor:
SQL = "insert into iris (sepal_length, sepal_width, petal_length, petal_width, uspecies) VALUES (% s, % s )"
Cursor.exe cutemany (SQL, list) # Use executemany to add data in batches
Connection. commit ()
Print ("insert {} data records, {} data records inserted". format (I, z ))
I = 0 # return to zero
List. clear () # clear the data list

If I> 0: # Insert the remaining data after the cyclic file is completed (for example, the remaining data smaller than 10, for example, your file has 55 rows, insert the remaining 5 rows)
Cursor.exe cute.pdf (SQL, list)
Connection. commit ()
Query data
'''
Query data
'''
# Single data
With connection. cursor () as cursor:
SQL = "select id, email, password from users where email = % s"
Cursor.exe cute (SQL, ('sfs1100 @ 126.com '))
Result = cursor. fetchone () # fetchone () Only retrieves the first result
Print (result)
Print (type (result ))
# Multiple data entries
With connection. cursor () as cursor:
SQL = "select * from iris where sepal_length> % s"
Cursor.exe cute (SQL, (3 ))
Result = cursor. fetchall () # fetchone () Retrieves all the queried data
Print (result)
Print (type (result ))
For each in result:
Print (each ['species '])
Update Data
'''
Update Data
'''
With connection. cursor () as cursor:
SQL = "update users set email = % s where password = % s"
Cursor.exe cute (SQL, ('sfs1100 @ 126.com ', 'Very _ secret '))
Connection. commit ()
Delete data
'''
Delete data
'''
With connection. cursor () as cursor:
SQL = "delete from users where email = % s"
Cursor.exe cute (SQL, ('sfs1100 @ 126.com '))
Connection. commit ()