How to use Python to play with Mysql

Premise:By default, everyone has installed mysql.

01 Introduction to Mysql

Mysql is a relational database that supports large databases and can handle large databases with tens of millions of records. After the data set collected by the crawler is stored in mysql, the relevant data can be retrieved in one step with the help of mysql's associated query. The specific functions will not be described in detail here. Let’s start with the actual operation.

1. Install pymysql

Install through the following command

pip install pymysql

pymysql library: Python3 link mysql

Remarks:

ps: MYSQLdb is only applicable to python2.x

python3 does not support MYSQLdb, instead pymysql

will report when running: ImportError: No module named 'MYSQLdb'

2 .python connects to mysql

import pymysql as pmq

localhost is the local IP. Localhost is used here to indicate the current local computer. Otherwise, change localhost to the corresponding database IP.

root is the database user name, 123456 is the database password, and python_chenge is the database name.

The database python_chenge in the picture has been created (after it is built, you can use the above code to connect). After it is built, there is currently no table. Now start using Python Perform table creation, insertion, query, modification, deletion and other operations (Combined with crawler to explain)

02 Create table

Before storing, create the table through python. There are four fields (a primary key movie name, link, rating )

# 创建 movie 表

Create the table movie, the fields are (id, title, url, rate), CHARACTER SET utf8 COLLATE utf8_general_ci is The string encoding is set to utf8 format

id is the primary key, int type, AUTO_INCREMENT auto-increment, non-empty not null

title, url is the string type varchar(100), also non-null Empty

The rating rate is a number with decimals, so it is a float, and it is also not empty

03 Insert data

The crawler has collected it For data, python has already built the table, and then you can insert the collected data into the database. Here are two ways to introduce it

### 插入数据

The id is auto-incremented, so there is no need to pass a value in.

After defining the method of inserting into the database, start storing it in the database

for i in json_data['subjects']:

04 Query

1. Query all

Query all the data in the table

# 查询

2. Query the specified data

For example, the query title is: Chinatown 3this one All fields of the data

#查询单条

##05 Update and modify

Update the data, still take the above:

Chinatown 3 as an example, the id is 7 , change the Chinatown 3 rating from 5.5 to 6

### 更新

Also look at the database

##06 Delete

Again, take Chinatown as an example. Its id is 7. If we delete it, we can update the id to delete it.

def delete(Id):

After deletion, there will be no 7th piece of data. Description Deletion successful

The above is the detailed content of How to use Python to play with Mysql. For more information, please follow other related articles on the PHP Chinese website!