Baptism soul, cultivation of python (91) -- knowledge collection-python operations on the pymysql module mysql add, delete, modify, and query, pythonpymysql

Baptism soul, cultivation of python (91) -- knowledge collection-python operations on the pymysql module mysql add, delete, modify, and query, pythonpymysql
First, you must learn the basic mysql operation statement: mysql Learning

Second, if python wants to operate mysql, it cannot rely on python's built-in modules. However, if it is not convenient to call the cmd command through the OS module although it works in principle, this problem occurs, some people thought about it for a long time and made it again,It is the third-party modules pymysql and mysqldb.

However, python3 currently does not support mysqldb, but pymysql supports both python2 and 3.

So in this blog post, we will talk about pymysql and learn about pymysql and mysqldb.

 

Install pymysql1.

Install using pip:

 

 

2. Use Add:

1) fixed data insertion:

#! Usr/bin/env python #-*-coding: UTF-8-*-# author: yangvaimport pymysql # create a database connection conn = pymysql. connect (host = '2017. 0.0.1 ', port = 3306, user = 'root', passwd = '', db = 'test') # create a cursor, the cursor here can be understood as the walk pointer cursor = conn in a text operation. cursor () # SQL sentence cursor.exe cute ('insert into user (name, age, part_id) VALUES ("ling",) ') # submit data, you can understand that the flush () Refresh cache in text operations is stored as data conn. commit () # Close the connection to cursor. close () conn. close ()

　　

Before running, check the data in the database:

 

Okay. Start running:

Running result:

 

If no error is reported, it is good news. Check whether the data changes:

 

Okay, it's inserted.

 

First look at the figure:

 

 

In fact, have you found that these are all fixed formats, which are similar to socket, and the two sentences marked are changed? The first sentence is database connection and other settings, the second is our SQL statement, so the problem is simple, right?

 

Then,If you want to insert data in Chinese, you can add the charset parameter when creating a database connection in the first sentence.:

# Create a database connection conn = pymysql. connect (host = '2017. 0.0.1 ', port = 3306, user = 'root', passwd = '', db = 'test', charset = 'utf8 ')

　　

 

2) string splicing activity insert:

Let's look at it again. What if we ask the user to input the data to be inserted:

Others remain unchanged. Only modify this part.

A: insert multiple columns of data

 

Note that no quotation marks are added in the VALUES () above, because the input already contains quotation marks.

 

Running result:

 

 

 

B: If you just insert a single data entry:

 

C: Why not concatenate strings like this:

 

This method is also feasible, but it is explicitly forbidden, becauseThe SQL injection vulnerability may occur.The execute () method can be used to input two parameters and automatically concatenate the strings.

 

D: insert an iteratable object:

#! Usr/bin/env python #-*-coding: UTF-8-*-# author: yangvaimport pymysql # create a database connection conn = pymysql. connect (host = '2017. 0.0.1 ', port = 3306, user = 'root', passwd = '', db = 'test', charset = 'utf8') # create a cursor = conn. cursor () user1 = input ('Enter the name to be inserted: ') age1 = input ('Enter the age to be inserted :') id1 = input ('Enter the id of the Department to be inserted: ') print ('recorded') user2 = input ('Enter the name to be inserted :') age2 = input ('Enter the age to be inserted: ') id2 = input ('Enter the id of the Department to be inserted:') print ('recorded ') li = [(user1, age1, id1), (user2, age2, id2)] # SQL sentence cursor.exe cute.pdf ('insert into user (name, age, part_id) VALUES (% s, % s, % s) ', li) # submit data conn. commit () # Close the connection to cursor. close () conn. close ()

Note that the method for inserting the SQL statement has been changed to execute.pdf. Otherwise, an error will be reported when the execute method is used.

 

Running result:

 

 

Change:

 

The data in the table is as follows:

 

Or the above statement, only this one is modified, and the others remain unchanged.

 

Running result:

 

Full OjbK

 

Delete:

 

Let's take a look at the current data:

 

 

Similarly, the rest remains unchanged, and the following section changes.

 

Running result:

 

Same OjbK

 

Query:

#! Usr/bin/env python #-*-coding: UTF-8-*-# author: yangvaimport pymysql # create a database connection conn = pymysql. connect (host = '2017. 0.0.1 ', port = 3306, user = 'root', passwd = '', db = 'test', charset = 'utf8') # create a cursor = conn. cursor () # SQL sentence cursor.exe cute ('select * from user') print (cursor. fetchone () # Close the connection to cursor. close () conn. close ()

Note: you do not need to use the commit () method when using the query statement. Because no data is modified, you only need to take out the data. addition, deletion, modification, and modification operations are performed on the data. Therefore, you need to use commit.

Can you understand?

You must use fetchone (), fetchextract ([int]), and fetchall () to obtain the query results.

 

Fetchone running result:

 

Fetchmany running result:

 

Fetchall () running result:

 

If you want to use fetchone, fetchweight, and fetchall at the same time, it will be as follows:

 

I think you should be able to understand it. I don't need to talk about it. Anyway, you know that it is the same as text read operations, and there are pointers.

 

So you said, I just want to use the above three fetch methods at the same time, and I want it to get the expected data correctly. What should I do? In text operations, we can use seek () to set the pointer position. What method is used here?

 

Cursor. scroll (0, mode = 'absolute ') # move cursor. scroll (0, mode = 'relative') relative to the current position # Move relative to the absolute position

　

That's right. You can use this scroll method to modify the pointer position.

 

Okay. Now let's take a look:

 

Perfect! O, this blog is over