Examples of MySQL and Python interaction

Examples of MySQL and Python interaction

1. Prepare data

Create a data table

-- Create the "Jingdong" database create database jing_dong charset=utf8;

-- Use the "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
);

Inserting 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','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 Drill

1. Strengthening SQL statements

Query the product name and price of the type cate_name as 'Ultrabook'

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

Display product types

select cate_name from goods group by cate_name;

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

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

Displays the average price of each product

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 whose prices are greater than the average price and sort them in descending order by price

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

Find 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;

2. Create a "Product Category" table

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

Query the types 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;

3. 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;

4. Create the "Product Brand Table" table

Use create...select to create a data table and write records at the same time, one step at a time

-- select brand_name from goods group by brand_name;

-- Insert data together when creating the data table -- Note: You need to use as to alias brand_name, otherwise the name field will have no value create table goods_brands (
 id int unsigned primary key auto_increment,
 name varchar(40) not null) select brand_name as name from goods group by brand_name;

5. Synchronize Data

Update the goods data table through the goods_brands data table

update goods as g inner join goods_brands as b on g.brand_name=b.name set g.brand_name=b.id;

6. Modify the table structure

Looking at the data table structure of goods, you will find that the corresponding types of cate_name and brand_name are varchar, but they are all numbers.

desc goods;

Modify the table structure through the alter table statement

alter table goods 
change cate_name cate_id int unsigned not null,
change brand_name brand_id int unsigned not null;

7. Foreign Keys

Insert records into the goods_cates and goods_brands tables respectively

insert into goods_cates(name) values ​​('Router'),('Switch'),('Network Card');
insert into goods_brands(name) values ​​('Haier'),('Tsinghua Tongfang'),('Shenzhou');

Write any record in the goods table

insert into goods (name,cate_id,brand_id,price)
values('LaserJet Pro P1606dn Black and White Laser Printer', 12, 4,'1849');

Query detailed information of all products (through inner connection)

select g.id,g.name,c.name,b.name,g.price from goods as g
inner join goods_cates as c on g.cate_id=c.id
inner join goods_brands as b on g.brand_id=b.id;

Query the detailed information of all products (through left join)

select g.id,g.name,c.name,b.name,g.price from goods as g
left join goods_cates as c on g.cate_id=c.id
left join goods_brands as b on g.brand_id=b.id;
  • How can we prevent the insertion of invalid information, that is, to determine whether the type or brand name exists before inserting it? We can use the foreign key mentioned earlier to solve this problem.
  • Foreign key constraint: verify the validity of data
  • Keywords: foreign key, only the innodb database engine supports foreign key constraints
  • How to update foreign key constraints for existing data tables
-- Successfully added foreign key constraint to brand_id alter table goods add foreign key (brand_id) references goods_brands(id);
-- Failed to add foreign key to cate_id -- Error 1452 will appear -- Cause of error: A non-existent cate_id value 12 has been added, so it needs to be deleted first alter table goods add foreign key (cate_id) references goods_cates(id);
  • How to set foreign key constraints when creating a data table?
  • Note: The type of cate_id in goods must be consistent with the id type in the goods_cates table
create table goods(
 id int primary key auto_increment not null,
 name varchar(40) default '',
 price decimal(5,2),
 cate_id int unsigned,
 brand_id int unsigned,
 is_show bit default 1,
 is_saleoff bit default 0,
 foreign key(cate_id) references goods_cates(id),
 foreign key(brand_id) references goods_brand(id)
);

How to remove foreign key constraints

-- You need to get the foreign key constraint name first. The name is automatically generated by the system. You can get the name by viewing the table creation statement show create table goods;
-- After getting the name, you can delete the foreign key constraint according to the name alter table goods drop foreign key foreign key name;

In actual development, foreign key constraints are rarely used, which will greatly reduce the efficiency of table updates.

3. Database design

Create a "Product Category" table (it has been created before, no need to create it again)

create table goods_cates(
 id int unsigned primary key auto_increment not null,
 name varchar(40) not null
);

Create a "Product Brand" table (it has already been created, no need to create it again)

create table goods_brands (
 id int unsigned primary key auto_increment not null,
 name varchar(40) not null
);

Create a "Products" table (it has already been created, no need to create it again)

create table goods(
 id int unsigned primary key auto_increment not null,
 name varchar(40) default '',
 price decimal(5,2),
 cate_id int unsigned,
 brand_id int unsigned,
 is_show bit default 1,
 is_saleoff bit default 0,
 foreign key(cate_id) references goods_cates(id),
 foreign key(brand_id) references goods_brand(id)
);

Create a "Customer" table

create table customer(
 id int unsigned auto_increment primary key not null,
 name varchar(30) not null,
 addr varchar(100),
 tel varchar(11) not null
);

Create an "Orders" table

create table orders(
 id int unsigned auto_increment primary key not null,
 order_date_time datetime not null,
 customer_id int unsigned,
 foreign key(customer_id) references customer(id)
);

Create an "Order Details" table

create table order_detail(
 id int unsigned auto_increment primary key not null,
 order_id int unsigned not null,
 goods_id int unsigned not null,
 quantity tinyint unsigned not null,
 foreign key(order_id) references orders(id),
 foreign key(goods_id) references goods(id)
);

illustrate

  • The order of creating the tables above is required. That is, if the foreign key constraint in the goods table uses goods_cates or goods_brands, then these two tables should be created first, otherwise the creation of goods will fail.
  • When creating a foreign key, be sure to make sure the type is the same, otherwise it will fail.

4. Steps to operate MySQL in Python

Importing modules

Import the pymysql module into the py file

from pymysql import *

Connection Object

  • Used to establish a connection with the database
  • Create an object: call the connect() method
conn=connect(parameter list)
  • Parameter host: the mysql host to connect to, if the local machine is 'localhost'
  • Parameter port: the port of the mysql host to connect to, the default is 3306
  • Parameter database: the name of the database
  • Parameter user: the user name of the connection
  • Parameter password: connection password
  • Parameter charset: The encoding method used for communication. UTF8 is recommended.

Object methods

  • close() closes the connection
  • commit()
  • cursor() returns a Cursor object, which is 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()
  • execute(operation [, parameters ]) executes the statement and returns the number of rows affected. It is mainly used to execute insert, update, and delete statements. It can also execute create, alter, and drop statements.
  • fetchone() executes a query statement, gets the first row of data in the query result set, and returns a tuple
  • When fetchall() executes a query, it gets all the rows of the result set, each row constitutes a tuple, and then these tuples are put into a tuple and returned.

Object Properties

  • rowcount read-only attribute, indicating the number of rows affected by the most recent execute() execution
  • connectionGet the current connection object

5. Add, delete, modify and check

from pymysql import *

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

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

 # # Update # count = cs1.execute('update goods_cates set name="Mechanical hard disk" where name="Hard disk"')
 # # Delete # count = cs1.execute('delete from goods_cates where id=6')

 # Submit the previous operation. If you have 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 conn = connect(host='localhost',port=3306,user='root',password='mysql',database='jing_dong',charset='utf8')
 # Get the Cursor object cs1 = conn.cursor()
 # Execute the select statement and return the number of affected rows: Query a data count = cs1.execute('select id,name from goods where id>=4')
 # Print the number of affected rows print("%d data found: " % count)

 for i in range(count):
  # Get the query result result = cs1.fetchone()
  # Print the query result print(result)
  # Get the query results # Close the Cursor object cs1.close()
 conn.close()

if __name__ == '__main__':
 main()

Query multiple rows of data

from pymysql import *

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

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

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

if __name__ == '__main__':
 main()

6. Parameterization

  • Parameterization of SQL statements can effectively prevent SQL injection
  • Note: This is different from Python string formatting, all use %s placeholders
from pymysql import *

def main():

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

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


 # # Unsafe method # # Enter " or 1=1 or " (also enter double quotes)
 # 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, parameterization is required. # Then params = [value 1, value 2....], then there are multiple %s in the sql statement. # Print the number of affected rows print(count)
 # Get the query result# result = cs1.fetchone()
 result = cs1.fetchall()
 # Print the query result print(result)
 # Close the Cursor object cs1.close()
 # Close the Connection object conn.close()

if __name__ == '__main__':
 main()

The above is the details of the example of MySQL and Python interaction. For more information about MySQL and Python interaction, please pay attention to other related articles on 123WORDPRESS.COM!

You may also be interested in:
  • Python mysql to realize student performance management system
  • Python+MySQL to implement personal paper management system
  • Python implements student management system based on mysql
  • Implementation of interaction between Python and MySQL database
  • Example code for Python management system to implement MySQL interaction

<<:  Solution to Docker pull timeout

>>:  VUE implements timeline playback component

Recommend

Understanding and using callback functions in JavaScript

Table of contents Overview What are callbacks or ...

VMware installation of Ubuntu 20.04 operating system tutorial diagram

Memo: Just experience it. Record: NO.209 This exa...

Enable sshd operation in docker

First, install openssh-server in docker. After th...

What is Software 404 and 404 Error and what is the difference between them

First of all, what is 404 and soft 404? 404: Simpl...

Time zone issues with Django deployed in Docker container

Table of contents Time zone configuration in Djan...

Summary of the differences between MySQL and Oracle (comparison of functional performance, selection, SQL when using them, etc.)

1. Concurrency Concurrency is the most important ...

Uniapp uses Baidu Voice to realize the function of converting recording to text

After three days of encountering various difficul...

Detailed explanation of the steps to build a Vue project with Vue-cli

First you need to install Vue-cli: npm install -g...

How to configure ssh to log in to Linux using git bash

1. First, generate the public key and private key...

Summary of MySQL's commonly used concatenation statements

Preface: In MySQL, the CONCAT() function is used ...

Sql query MySql database table name and description table field (column) information

The following is an introduction to using SQL que...

Quickly get started with VUE 3 teleport components and usage syntax

Table of contents 1. Introduction to teleport 1.1...

js to achieve the pop-up effect

This article example shares the specific code of ...

Two ways to implement HTML to randomly drag content positions

Test: Chrome v80.0.3987.122 is normal There are t...

JS thoroughly understands GMT and UTC time zones

Table of contents Preface 1. GMT What is GMT Hist...