This is the fourth day of my participation in Gwen Challenge

The data collected by crawler can be stored in data sets besides text files and Excel, such as Mysql, Redis, mongodb, etc. Today, Brother Chen will teach you how to use Python to connect to Mysql, and explain to you with crawler.

Prerequisite: You have installed mysql by default.

01 Mysql profile

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 crawler is stored in mysql, related data can be taken out in one step with the help of mysql’s associated query. The specific functions are not described here, and the actual operation begins below.

1. Install pymysql

Install with the following command

pip install pymysql

Copy the code

Pymysql library: Python3 links to mysql

Remark:

Ps :MYSQLdb only works with python2.x

Python3 does not support MYSQLdb, but pymysql instead

ImportError: No module named ‘MYSQLdb’

2. The python mysql connection

import pymysql as pmq
#connect(ip.user,password,dbname)
con = pmq.connect('localhost'.'root'.'123456'.'python_chenge')
# operation cursor
cur = con.cursor()

Copy the code

Localhost is the local IP address. In this case, localhost indicates the current host. Otherwise, change localhost to the corresponding database IP address.

Root is the database user name, 123456 is the database password, and PYTHon_chenge is the database name.

Python_chenge mysql > create table python_chenge mysql > create table python_chenge mysql > create table python_chenge mysql > create table python_chenge mysql > create table python_chenge

02 built table

Before storing, create a table in Python with four fields (a primary key + movie name, link, rating)

Create the movie table
movie_sql= ''' create table movie( id int AUTO_INCREMENT primary key not null, title varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci not null, url varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci not null, rate float not null ) '''
Execute SQL statement
cur.execute(movie_sql)
Commit to database for execution
con.commit()


Copy the code

Create table movie with fields (ID,title, URL,rate), CHARACTER SET UTF8 COLLATE UTF8_general_ci is a string encoding SET to UTF8 format

Id is the primary key whose type is int, AUTO_INCREMENT, and not null

Title, url is a string of type varchar(100), also non-empty

The rate is a number with a decimal, so it is a float

03 Inserting Data

The crawler has collected the data, Python has built the table, and you can then insert the collected data into the database in two ways

Insert data
def insert(title,url,rate) :
    Insert data 1
    #cur.execute("INSERT INTO movie(title,url,rate) VALUES('"+str(title)+"','"+str(url)+"',"+str(rate)+")")
    Insert data 2
    sql = "INSERT INTO movie(title,url,rate) VALUES('"+str(title)+"', '"+str(url)+"',"+str(rate)+")"
    cur.execute(sql)
    Commit to database for execution
    con.commit()    

Copy the code

The ID is incremented, so you don’t need to pass it in.

Once you have defined the insert method, start storing to the database

for i in json_data['subjects']:
    insert(i['title'],i['url'],i['rate'])

Copy the code

04 query

1. Query all information

Query all data in the table

# query
cur.execute('select * from movie')
results = cur.fetchall()
for row in results:
    Id = row[0]
    title = row[1]
    print("id=%s,title=%s" % (Id, title))

Copy the code

2. Query the specified data

For example, query all fields of the data titled: Chinatown 3

# query a single entry
cur.execute('select * from movie where title=" "')
results = cur.fetchall()
for row in results:
    Id = row[0]
    title = row[1]
    url = row[2]
    rate = row[3]
    print("id=%s,title=%s,url=%s,rate=%s" % (Id, title,url,rate))

Copy the code

05 Updates and Modifications

Update the data, again using the example above: Chinatown 3 with id 7, change the score of Chinatown 3 from 5.5 to 6

Update # # #
def update() :
    sql = "update movie set rate='6' where Id = {0}".format(7)
    cur.execute(sql)
    con.commit()


Copy the code

Also look at the database

06 delete

Similarly, take Chinatown as an example. Its ID is 7. If it is deleted, we can update the ID to delete it

def delete(Id) :
    sql = "delete from movie where Id = {0}".format(Id)
    cur.execute(sql)
    con.commit()

Copy the code

After the deletion, there is no item 7 data, indicating that the deletion is successful

07 summary

This is the end of today’s technical explanation article, mainly will be how to use Python to connect to mysql, and to create tables, insert data, query, update modify and delete. (Dry articles, recommended collection)