This is the fifth day of my participation in the August More text Challenge. For details, see: August More Text Challenge

Hello everyone, I am Brother Chen.

In addition to the data collected by the crawler stored in text files and Excel, it can also be stored in data sets, such as Mysql, Redis, mongodb, etc. Today, Chen Is going to teach you how to use Python to connect Mysql, and explain to you combined with the crawler.

Premise: Mysql is installed 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 retrieved in one step by using associated query of mysql. The specific role here is not repeated, the following began to enter the actual operation.

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')
# Operate cursor
cur = con.cursor()
Copy the code

Localhost is the IP address of the current host. Otherwise, change localhost to the IP address of the corresponding database.

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

The python_chenge database has been set up. The python_chenge database has been set up. The python_chenge database has been set up.

02 built table

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

Create a 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 the SQL statement
cur.execute(movie_sql)
# Commit to database execution
con.commit()
Copy the code

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

Id is primary key, int type, AUTO_INCREMENT, not NULL

Title, URL is a string of type varchar(100), again non-empty

The score rate is a decimal number, so it is float, again non-empty

03 Inserting Data

The crawler has collected the data, Python has built the table, and then the collected data can be inserted into the database. There are two ways to do this

### 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 execution
    con.commit()    
Copy the code

The ID is self-incrementing, so you don’t need to pass it in.

With the database insert method defined, 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

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 with the title: Chinatown 3

# Query a single item
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 Update Modification

Update the data, again using the above example: Chinatown 3, id 7, change Chinatown 3’s score 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

Also take Chinatown as an example, its ID is 7, delete if we can update the ID to delete

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 seventh data, indicating that the deletion is successful

07 summary

This is the end of today’s technical tutorial, which focuses on how to connect to mysql using Python, create tables, insert data, query, update, modify, and delete data. (Dry articles, recommended collection)