Interaction between MySQL database and python code

Article directory

1. Prepare data
- Create database
- Insert data
2. Review of SQL statements
- Strengthening of SQL statements
- Create the “Product Classification” table
- Synchronize table data
3. Steps to operate MySQL in Python
- Import module
- Connection object
- Cursor object
4. Python’s addition, deletion, modification and query operations on MySQL
5. Parameterization

1. Prepare data

The code is written in the MySQL database

Create database

--Create "JD.com" database
create database jing_dong charset=utf8;

-- Use "Jingdong" database
use jing_dong;

--Create a goods data table
create table goods(
    id int unsigned primary key auto_increment not null,
    name varchar(150) not null,
    cate_name varchar(40) not null,
    brand_name varchar(40) not null,
    price decimal(10,3) not null default 0,
    is_show bit not null default 1,
    is_saleoff bit not null default 0
);

Insert data

--Insert data into the goods table

insert into goods values(0,'r510vc 15.6-inch notebook','notebook','ASUS','3399',default,default);
insert into goods values(0,'y400n 14.0-inch laptop','notebook','Lenovo','4999',default,default);
insert into goods values(0,'g150th 15.6-inch gaming laptop','gaming laptop','Thor','8499',default,default);
insert into goods values(0,'x550cc 15.6-inch notebook','notebook','ASUS','2799',default,default);
insert into goods values(0,'x240 Ultrabook','Ultrabook','Lenovo','4880',default,default);
insert into goods values(0,'u330p 13.3-inch Ultrabook','Ultrabook','Lenovo','4299',default,default);
insert into goods values(0,'svp13226scb touch ultrabook','ultrabook','Sony','7999',default,default);
insert into goods values(0,'ipad mini 7.9-inch tablet','tablet','Apple','1998',default,default);
insert into goods values(0,'ipad air 9.7-inch tablet','tablet','Apple','3388',default,default);
insert into goods values(0,'ipad mini with retina display','tablet','Apple','2788',default,default);
insert into goods values(0,'ideacentre c340 20-inch all-in-one computer','desktop','Lenovo','3499',default,default);
insert into goods values(0,'vostro 3800-r1206 desktop computer','desktop computer','Dell','2899',default,default);
insert into goods values(0,'imac me086ch/a 21.5-inch all-in-one computer','desktop','Apple','9188',default,default);
insert into goods values(0,'at7-7414lp desktop computer linux )','desktop','Acer','3699',default,default);
insert into goods values(0,'z220sff f4f06pa workstation','server/workstation','HP','4288',default,default);
insert into goods values(0,'poweredge ii server','server/workstation','Dell','5388',default,default);
insert into goods values(0,'mac pro professional desktop computer','server/workstation','Apple','28888',default,default);
insert into goods values(0,'hmz-t3w head mounted display device','laptop accessories','Sony','6999',default,default);
insert into goods values(0,'Business backpack','Laptop accessories','Sony','99',default,default);
insert into goods values(0,'x3250 m4 rack server','server/workstation','ibm','6888',default,default);
insert into goods values(0,'Business backpack','Laptop accessories','Sony','99',default,default);

2. SQL statement review

Strengthening of SQL statements

The query type cate_name is the product name and price of Ultrabook’

select name,price from goods where cate_name = 'Ultrabook';

Show product types

select cate_name from goods group by cate_name;

Find the average price of all computer products, and keep it to two decimal places

select round(avg(price),2) as avg_price from goods;

Shows the average price of each item

select cate_name,avg(price) from goods group by cate_name;

Query the most expensive, cheapest, average price and quantity of each type of product

select cate_name,max(price),min(price),avg(price),count(*) from goods group by cate_name;

Query all products with a price greater than the average price and sort them in descending order of price

select id,name,price from goods
where price > (select round(avg(price),2) as avg_price from goods)
order by price desc;

Find information on the most expensive computers of each type

select * from goods
inner join
    (
        select
        cate_name,
        max(price) as max_price,
        min(price) as min_price,
        avg(price) as avg_price,
        count(*) from goods group by cate_name
    ) as goods_new_info
on goods.cate_name=goods_new_info.cate_name and goods.price=goods_new_info.max_price;

Create the “Product Classification” table

--Create product classification table
create table if not exists goods_cates(
    id int unsigned primary key auto_increment,
    name varchar(40) not null
);

Query the type of goods in the goods table

select cate_name from goods group by cate_name;

Write the grouping results to the goods_cates data table

insert into goods_cates (name) select cate_name from goods group by cate_name;

Synchronize table data

Update the goods table through the goods_cates data table

update goods as g inner join goods_cates as c on g.cate_name=c.name set g.cate_name=c.id;

3. Steps to operate MySQL in Python

Introduction module

Introduce the pymysql module into the py file

from pymysql import *

Connection object

Used to establish a connection to the database
Create object: call connect() method

conn=connect(parameter list)

Parameter host: connected mysql host, if the local machine is ‘localhost’
Parameter port: The port of the connected mysql host, the default is 3306
Parameter database: name of the database
Parameter user: user name of the connection
Parameter password: the password for the connection
Parameter charset: the encoding method used for communication, it is recommended to use utf8

object methods

close() closes the connection
commit()submit
cursor() returns a Cursor object, used to execute sql statements and obtain results

Cursor object

Used to execute sql statements. The most frequently used statements are select, insert, update, and delete.
Get the Cursor object: call the cursor() method of the Connection object

cs1=conn.cursor()

object methods

close()close
execute(operation [, parameters]) executes a statement and returns the number of affected rows. It is mainly used to execute insert, update, delete statements, and can also execute create, alter, drop and other statements.
When fetchone() executes a query statement, it obtains the first row of data in the query result set and returns a tuple.
When fetchall() executes a query, it obtains all the rows of the result set, each row constitutes a tuple, and then puts these tuples into a tuple and returns

Object properties

The rowcount read-only attribute indicates the number of rows affected after the latest execute() execution.
connection gets the current connection object

4. Python’s addition, deletion, modification and query operations on MySQL

addition, deletion, modification

from pymysql import *

def main():
    # Create Connection connection
    conn = connect(host='localhost',port=3306,database='jing_dong',user='root',password='mysql',charset='utf8')
    # Get Cursor object
    cs1 = conn.cursor()
    # Execute the insert statement and return the number of affected rows: add a piece of data
    # Increase
    count = cs1.execute('insert into goods_cates(name) values("hard disk")')
    #Print the number of affected lines
    print(count)

    count = cs1.execute('insert into goods_cates(name) values("CD")')
    print(count)

    # # renew
    # count = cs1.execute('update goods_cates set name="mechanical hard drive" where name="hard drive"')
    # # delete
    # count = cs1.execute('delete from goods_cates where id=6')

    # Submit the previous operation. If execute has been executed multiple times before, then submit them all.
    conn.commit()

    # Close the Cursor object
    cs1.close()
    # Close the Connection object
    conn.close()

if __name__ == '__main__':
    main()

Query a row of data

from pymysql import *

def main():
    # Create Connection connection
    conn = connect(host='localhost',port=3306,user='root',password='mysql',database='jing_dong',charset='utf8')
    # Get Cursor object
    cs1 = conn.cursor()
    # Execute the select statement and return the number of affected rows: query a piece of data
    count = cs1.execute('select id,name from goods where id>=4')
    #Print the number of affected lines
    print("Query %d pieces of data:" % count)

    for i in range(count):
        # Get the results of the query
        result = cs1.fetchone()
        #Print the results of the query
        print(result)
        # Get the results of the query

    # Close the Cursor object
    cs1.close()
    conn.close()

if __name__ == '__main__':
    main()

Query multiple rows of data

from pymysql import *

def main():
    # Create Connection connection
    conn = connect(host='localhost',port=3306,user='root',password='mysql',database='jing_dong',charset='utf8')
    # Get Cursor object
    cs1 = conn.cursor()
    # Execute the select statement and return the number of affected rows: query a piece of data
    count = cs1.execute('select id,name from goods where id>=4')
    #Print the number of affected lines
    print("Query %d pieces of data:" % count)

    # for i in range(count):
    # # Get the results of the query
    # result = cs1.fetchone()
    # # Print the results of the query
    # print(result)
    # # Get the results of the query

    result = cs1.fetchall()
    print(result)

    # Close the Cursor object
    cs1.close()
    conn.close()

if __name__ == '__main__':
    main()

5. Parameterization

Parameterization of sql statements can effectively prevent sql injection
Note: This is different from python’s string formatting, all using %s placeholder

from pymysql import *

def main():

    find_name = input("Please enter the item name:")

    # Create Connection connection
    conn = connect(host='localhost',port=3306,user='root',password='mysql',database='jing_dong',charset='utf8')
    # Get Cursor object
    cs1 = conn.cursor()


    # # Unsafe way
    # # Enter " or 1=1 or " (double quotes must also be entered)
    # sql = 'select * from goods where name="%s"' % find_name
    # print("""sql===>%s<====""" % sql)
    # # Execute the select statement and return the number of affected rows: query all data
    # count = cs1.execute(sql)

    # safe way
    # Construct parameter list
    params = [find_name]
    # Execute the select statement and return the number of affected rows: query all data
    count = cs1.execute('select * from goods where name=%s', params)
    # Notice:
    # If there are multiple parameters, they need to be parameterized.
    # Then params = [value 1, value 2....], then there are multiple %s in the sql statement.

    #Print the number of affected lines
    print(count)
    # Get the results of the query
    # result = cs1.fetchone()
    result = cs1.fetchall()
    #Print the results of the query
    print(result)
    # Close the Cursor object
    cs1.close()
    # Close the Connection object
    conn.close()

if __name__ == '__main__':
    main()