Step by step how you can use Python to manipulate MySQL

Article Source: http://www.51testing.com/html/84/n-3724984.html

工欲善其事, its prerequisite. So the first step is to download the third-party library first. Here, I use the Pymysql library.

Download library: At the command line input

Pip Install Pymysql

After downloading, you can check if the download is successful. Go directly to Python on the command line and guide the library.

C:\users\june>python

Python 3.6.3 | Anaconda, inc.| (Default, Oct 15 2017, 03:27:45) [MSC v.1900-bit (AMD64)] on Win32

Type "Help", "copyright", "credits" or "license" for more information.

>>> Import Pymysql

>>>

See this screen to show that the download was successful, and then learn how to operate the database!!!

connecting to a database

Import Pymysql

# Connect to Database

db = Pymysql.connect (host= ' 127.0.0.1 ', user= ' root ', passwd= ' your password ', db= ' news ', port=3306,charset= ' UTF8 ')

The above parameters are required.

· Host: This is the IP address, because I am here local, so fill 127.0.0.1, you can also fill localhost.

· User: Username, if you are local, please fill in root.

· PASSWD: This is the password, just fill in your own password.

· DB: This is the database name, and I'm choosing the news database here.

· Port: This is the ports, the local is generally 3306

· CharSet: This is the encoding method, and your database is encoded in the same way, to not fail the connection

Connected, how to verify it? Here we can choose to look up a piece of data

Code interpretation: Because in the connection database, sometimes there will be a connection failure and other exceptions, so here to catch the exception, where the exception is in Pymsql. Error inside. It doesn't matter if the above code is not understood, as I will say next, if there is a result after running that the connection is successful.

When you're done, be sure to close the database connection to prevent resource leaks.

Querying the data

Code interpretation:

· Cursor (): This is the cursor, used to execute the MySQL statement, after the end of the need to close

· Excute (): This is the MySQL statement that executes the statement and executes the arguments

· Fetchone (): This is a piece of data after viewing the execution statement

· Fetchall (): This is to see all the data

After querying the data, the return is an entire piece of data, can you query it in dictionary form? Let's try it!

After checking, the compiler did not want to give me this error, said this is a tuple, can not do so.

Although Python is not provided, but we can manually turn into a dictionary to query ah

Cursor here is a property: description. Get the database for each column situation, as follows:

So, we use this property to generate the dictionary manually

DeMarcus Achilles tendon tear season reimbursement eyebrows Jan Blessing

However, the above is just a piece of data, if it is more than one? It won't work if you press the method above. Then we need to use the map function.

This is a clever use of the map function, because multiple data can be iterated, you need to manipulate each piece of data, so you can think of the map function

Then we'll use the object-oriented approach to query the database with Python.

This allows you to query the database by using an instance method.

We can also query the number of specified data by page count

The use of the MySQL limit keyword, there are other, such as the sort of group of interested can try their own

adding data to the database

Because the data is added, the transaction needs to be committed, which requires the Conn. commit () to commit, and if the data is added, the database will not be displayed if it is not committed.

There are changes to the data and delete the data will not be posted, but the above SQL variable to change the statement to modify or delete the statement on it, if you do not, suggest practice under ~

Step by step how you can use Python to manipulate MySQL