Python operates MySQL
The origin of SQL:
MySQL itself is a C/S architecture, with a server and a client. It comes with a client: mysql.exe
The python language has become the client of MySQL (for a server, there can be many clients)
Steps:
1. Connect to MySQL first
host, port, username, password, charset, library, etc.2. Write SQL statements in Python
3. Start executing the SQL statement and get the result
4. Processing in Python (further processing of data)
# Need to use a third-party module: pymysql mysqldb mysqlclient
pip install pymysql
importpymysql
# 1. Connect to MySQL first
conn=pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
password='1234',
db='db10',
charset='utf8',
autocommit=True
)
# 2. Get the cursor
cur=conn.cursor(cursor=pymysql.cursors.DictCursor)
# 3. Write SQL statements
# sql='select * from student'
sql='insert into teacher(tid, tname) values (7, "ly1")'
# 4. Start executing the SQL statement
affect_rows=cur.execute(sql) # 16 is the number of affected rows
### Need to perform a second confirmation: In addition to the query, a second confirmation is required to submit.
# conn.commit()
print(affect_rows)
# 5. Want to get the results:
# res=cur.fetchone()
res=cur.fetchall()
# res=cur.fetchmany(5)
# {'sid': 1, 'gender': 'male', 'class_id': 1, 'sname': 'understanding'}
print(res) # (1, 'male', 1, 'understanding') Tuple type
# for i in res:
# print(i.get("sid"))
SQL injection problem
import pymysql
# Connect to MySQL server
conn = pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
password='123',
database='db8_3',
charset='utf8',
autocommit=True # Automatic secondary confirmation for additions, modifications and deletions
)
# Generate a cursor object
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
#Write SQL statements
username = input('username>>>:').strip()
password = input('password>>>:').strip()
# sql = "select * from userinfo where name='%s' and pwd='%s'" % (name,pwd)
sql = "select * from userinfo where name=%s and pwd=%s"
cursor.execute(sql,(username,password))
data = cursor.fetchall()
if data:
print(data)
print('Login successful')
else:
print('Username or password is incorrect')
Causes of SQL injection:
Because the combination of special symbols will produce special effects
In real life, especially when registering a username, you will be very clearly reminded that many special symbols cannot be used.
The reason is the sameConclusion: When it comes to designing sensitive data, don’t splice it yourself. Just leave it to ready-made methods.
When using code to perform data operations, the levels of different operations are different.
Doesn’t matter to Cha
Additions, modifications, and deletions require a second confirmation.
conn.commit()
View
A view is a virtual table (not a real one). Its essence is to [acquire a dynamic data set based on SQL statements and name it]. When using it, users only need to use [name] to obtain the result set. The result set can be Use it as a table.
Using views, we can extract the temporary table during the query process and use views to implement it. In this way, when we want to operate the data of the temporary table in the future, we do not need to rewrite complex SQL. We can directly search in the view, but the view has There is an obvious efficiency problem, and the view is stored in the database. If the SQL used in our program relies too much on the view in the database, that is, strong coupling, it means that it is extremely inconvenient to extend SQL, so it is not recommended.
Temporary table application examples
# Two related tables mysql> select * from course; + ----- + -------- + ------------ + | cid | cname | teacher_id | + ----- + -------- + ------------ + | 1 | Bio | 1 | | 2 | Physics | 2 | | 3 | Sports | 3 | | 4 | Fine Arts | 2 | + ----- + -------- + ------------ + 4 rows in set (0.00 sec) mysql> select * from teacher; + ----- + ----------------- + | tid | tname | + ----- + ----------------- + | 1 | Teacher Zhang Lei | | 2 | Teacher Li Ping | | 3 | Teacher Liu Haiyan | | 4 | Teacher Zhu Yunhai | | 5 | Teacher Li Jie | + ----- + ----------------- + 5 rows in set (0.00 sec) # Query the course name taught by Mr. Li Ping mysql> select cname from course where teacher_id = (select tid from teacher where tname='Teacher Li Ping'); +--------+ | cname | +--------+ | Physics | | Fine Art | +--------+ 2 rows in set (0.00 sec) # Subquery out the temporary table as the basis for judgment such as teacher_id select tid from teacher where tname='Teacher Li Ping'
1. Create a view
# Syntax: CREATE VIEW view name AS SQL statement create view teacher_view as select tid from teacher where tname='Teacher Li Ping'; # So the sql for querying the course name taught by Li Ping can be rewritten as mysql> select cname from course where teacher_id = (select tid from teacher_view); +--------+ | cname | +--------+ | Physics | | Fine Art | +--------+ 2 rows in set (0.00 sec) #! ! ! Note note note: #1. After using the view, there is no need to rewrite the sql of the subquery every time, but this is not very efficient and is not as efficient as writing subqueries. #2. And there is a fatal problem: the view is stored in the database. If the SQL in our program relies too much on the view stored in the database, it means that once the SQL needs to be modified and involves the view, it must Go to the database to make modifications, and usually there is a dedicated DBA responsible for the database in the company. If you want to complete the modification, you must pay a lot of communication costs before the DBA can help you complete the modification, which is extremely inconvenient
2. Use views
#Modify the view, and the original table will also be modified. mysql> select * from course; + ----- + -------- + ------------ + | cid | cname | teacher_id | + ----- + -------- + ------------ + | 1 | Bio | 1 | | 2 | Physics | 2 | | 3 | Sports | 3 | | 4 | Fine Arts | 2 | + ----- + -------- + ------------ + 4 rows in set (0.00 sec) mysql> create view course_view as select * from course; #Create a view of the table course Query OK, 0 rows affected (0.52 sec) mysql> select * from course_view; + ----- + -------- + ------------ + | cid | cname | teacher_id | + ----- + -------- + ------------ + | 1 | Bio | 1 | | 2 | Physics | 2 | | 3 | Sports | 3 | | 4 | Fine Arts | 2 | + ----- + -------- + ------------ + 4 rows in set (0.00 sec) mysql> update course_view set cname='xxx'; #Update the data in the view Query OK, 4 rows affected (0.04 sec) Rows matched: 4 Changed: 4 Warnings: 0 mysql> insert into course_view values(5,'yyy',2); #Insert data into the view Query OK, 1 row affected (0.03 sec) mysql> select * from course; #It is found that the records of the original table have also been modified. + ----- + ------- + ------------ + | cid | cname | teacher_id | + ----- + ------- + ------------ + | 1 | xxx | 1 | | 2 | xxx | 2 | | 3 | xxx | 3 | | 4 | xxx | 2 | | 5 | yyy | 2 | + ----- + ------- + ------------ + 5 rows in set (0.00 sec)
We should not modify the records in the view, and it is impossible to modify the records in the view when multiple tables are involved, as shown below
3. Modify the view
Syntax: ALTER VIEW view name AS SQL statement mysql> alter view teacher_view as select * from course where cid>3; Query OK, 0 rows affected (0.04 sec) mysql> select * from teacher_view; + ----- + ------- + ------------ + | cid | cname | teacher_id | + ----- + ------- + ------------ + | 4 | xxx | 2 | | 5 | yyy | 2 | + ----- + ------- + ------------ + 2 rows in set (0.00 sec)
4. Delete view
Syntax: DROP VIEW view name DROP VIEW teacher_view
Trigger
Triggers can be used to customize the behavior before and after the user performs [add, delete, modify] operations on the table. Note: There is no query.
1. Create a trigger
# Before inserting
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
BEGIN
...
END
# After inserting
CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
BEGIN
...
END
# Before deletion
CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW
BEGIN
...
END
# After deletion
CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW
BEGIN
...
END
# Before update
CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW
BEGIN
...
END
# Updated
CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW
BEGIN
...
END
Fire trigger after insertion
#preparation table
CREATE TABLE cmd (
id INT PRIMARY KEY auto_increment,
USER CHAR (32),
priv CHAR (10),
cmd CHAR (64),
sub_time datetime, #Submission time
success enum ('yes', 'no') #0 represents execution failure
);
CREATE TABLE errlog (
id INT PRIMARY KEY auto_increment,
err_cmd CHAR (64),
err_time datetime
);
#Create trigger
delimiter //
CREATE TRIGGER tri_after_insert_cmd AFTER INSERT ON cmd FOR EACH ROW
BEGIN
IF NEW.success = 'no' THEN #Equality judgment only has one equal sign
INSERT INTO errlog(err_cmd, err_time) VALUES(NEW.cmd, NEW.sub_time); #Must add semicolon
END IF; #must add semicolon
END//
delimiter;
#Insert records into table cmd, trigger triggers, and decide whether to insert error logs based on IF conditions
INSERT INTO cmd (
USER,
private,
cmd,
sub_time,
success
)
VALUES
('ly','0755','ls -l /etc',NOW(),'yes'),
('ly','0755','cat /etc/passwd',NOW(),'no'),
('ly','0755','useradd xxx',NOW(),'no'),
('ly','0755','ps aux',NOW(),'yes');
#Query the error log and found two
mysql> select * from errlog;
+ ---- + ------------------ + -------------------------- +
| id | err_cmd | err_time |
+ ---- + ------------------ + -------------------------- +
| 1 | cat /etc/passwd | 2017-09-14 22:18:48 |
| 2 | useradd xxx | 2017-09-14 22:18:48 |
+ ---- + ------------------ + -------------------------- +
2 rows in set (0.00 sec)
Special: NEW represents the data row to be inserted, and OLD represents the data row to be deleted.
2. Use triggers
Triggers cannot be called directly by users, but are passively triggered by the [add/delete/modify] operations on the table.
3. Delete trigger
drop trigger tri_after_insert_cmd;
Transactions
1. What is a transaction
Opening a transaction can contain some sql statements, and these sql statements will either succeed at the same time
Either none of them can succeed, call it atomicity of transactions
2. The role of transactions
Guaranteed data security for data operations
Case: Use a Bank of Communications card to operate a CCB ATM machine to transfer money to an industrial and commercial account
Transactions should have 4 attributes: Atomicity, consistency, isolation, and durability. These four properties are often called ACID properties.
Atomicity. A transaction is an indivisible unit of work. All operations included in the transaction are either done or none.
Consistency. A transaction must change the database from one consistency state to another. Consistency and atomicity are closely related.
Isolation. The execution of a transaction cannot be interfered with by other transactions. That is, the operations and data used within a transaction are isolated from other concurrent transactions, and transactions executed concurrently cannot interfere with each other.
Durability. Durability, also known as permanence, means that once a transaction is committed, its changes to the data in the database should be permanent. Subsequent operations or failures should not have any impact on it.
3. How to use
# First introduce the three keywords of the transaction, and then use the table to actually show the effect.
start transaction;
commit;
rollback;
create table user(
id int primary key auto_increment,
name char(32),
balance int
);
insert into user(name,balance)
values
('jason',1000),
('egon',1000),
('tank',1000);
# Enable transaction operations before modifying data
start transaction;
# Modification operation
update user set balance=900 where name='jason'; #Buy and pay 100 yuan
update user set balance=1010 where name='egon'; #The intermediary took away 10 yuan
update user set balance=1090 where name='tank'; #The seller gets 90 yuan
# Roll back to the previous state
rollback;
# After starting the transaction, as long as no commit operation is performed, the data is not actually refreshed to the hard disk.
commit;
"""Enable transaction detection to see if the operation is complete. If it is incomplete, it will automatically roll back to the previous state. If it is complete, the commit operation should be performed"""
# From the perspective of python code, the pseudo code logic that should be implemented,
try:
# Less open transactions...
update user set balance=900 where name='jason'; #Buy and pay 100 yuan
update user set balance=1010 where name='egon'; #The intermediary took away 10 yuan
update user set balance=1090 where name='tank'; #The seller gets 90 yuan
except Exception:
rollback;
else:
commit;
Stored procedure
1. Introduction
The stored procedure contains a series of executable SQL statements. The stored procedure is stored in MySQL. By calling its name, a bunch of SQL inside it can be executed.
Advantages of using stored procedures:
1. Used to replace SQL statements written by programs to achieve decoupling of programs and SQL
2. Based on network transmission, the amount of data transferred by alias is small, but the amount of data transferred directly by SQL is large.
Disadvantages of using stored procedures:
1. It is inconvenient for programmers to expand functions
2. Basic use
delimiter $$ create procedure p1() begin select * from user; end $$ delimiter; # transfer call p1()
3. Three development models
The first type
Application: Only the logic of the application needs to be developed
mysql: Write stored procedures to be called by applications
Advantages: high development efficiency and execution efficiency
Disadvantages: Considering human factors, cross-department communication and other issues, it will lead to poor scalability
Second type
Application: In addition to developing the logic of the application, you also need to write native sql
Advantages: higher scalability than method 1 (non-technical)
shortcoming:
1. Development efficiency and execution efficiency are not as good as method 1
2. Writing native sql is too complicated, and the optimization of sql statements needs to be considered.
The third type
Application: Develop the logic of the application without writing native sql. Process data based on a framework written by others, ORM
Advantages: There is no need to write pure SQL, which means that the development efficiency is higher than that of method 2, and it is also compatible with the high scalability of method 2.
Disadvantages: The execution efficiency is not even comparable to method 2.
4. Create stored procedures
# Introduce the characteristics of formal parameters and then write the specific functions
delimiter $$
create procedure p2(
in m int, # in means that this parameter must only be passed in and cannot be returned.
in n int,
out res int # out means that this parameter can be returned
)
begin
select tname from teacher where tid > m and tid < n;
set res=0; # Used to mark whether the stored procedure is executed
end $$
delimiter;
#Res needs to be defined in advance
set @res=10; Definition
select @res; View
call p1(1,5,@res) call
select @res view
5. How to use stored procedures
# Major premise: The stored procedure is created under which library and can only be used under the corresponding library! ! !
# 1. Call directly in mysql
set @res=10 # The value of res is used to determine whether the stored procedure is executed successfully, so you need to first define a variable @res to store 10
call p1(2,4,10); # Report error
call p1(2,4,@res);
# View Results
select @res; # Execution is successful, @res variable value has changed
# 2. Call in python program
pymysql link mysql
The generated travel table cursor.callproc('p1',(2,4,10)) # Internal principle: @_p1_0=2,@_p1_1=4,@_p1_2=10;
cursor.execute('select @_p1_2;')
# 3. Examples of using stored procedures and transactions (understand)
delimiter //
create PROCEDURE p5(
OUT p_return_code tinyint
)
BEGIN
DECLARE exit handler for sqexception
BEGIN
--ERROR
set p_return_code = 1;
rollback;
END;
DECLARE exit handler for sqlwarning
BEGIN
-- WARNING
set p_return_code = 2;
rollback;
END;
START TRANSACTION;
update user set balance=900 where id =1;
update user123 set balance=1010 where id = 2;
update user set balance=1090 where id =3;
COMMIT;
--SUCCESS
set p_return_code = 0; #0 represents successful execution
END //
delimiter ;
Function
Note the difference from stored procedures. MySQL built-in functions can only be used in SQL statements!
CREATE TABLE blog (
id INT PRIMARY KEY auto_increment,
NAME CHAR (32),
sub_time datetime
);
INSERT INTO blog (NAME, sub_time)
VALUES
('Part 1','2015-03-01 11:31:21'),
('Part 2','2015-03-11 16:31:21'),
('Part 3','2016-07-01 10:21:31'),
('Article 4','2016-07-22 09:23:21'),
('Part 5','2016-07-23 10:11:11'),
('Article 6','2016-07-25 11:21:31'),
('Article 7','2017-03-01 15:33:21'),
('Article 8','2017-03-01 17:32:21'),
('Article 9','2017-03-01 18:31:21');
+ ---- + -------------------------------------- + ----- ---------------- +
| id | NAME | sub_time | month
+ ---- + -------------------------------------- + ----- ---------------- +
| 1 | Article 1 | 2015-03-01 11:31:21 | 2015-03
| 2 | Article 2 | 2015-03-11 16:31:21 | 2015-03
| 3 | Article 3 | 2016-07-01 10:21:31 | 2016-07
| 4 | Article 4 | 2016-07-22 09:23:21 | 2016-07
| 5 | Article 5 | 2016-07-23 10:11:11 | 2016-07
| 6 | Article 6 | 2016-07-25 11:21:31 | 2016-07
| 7 | Chapter 7 | 2017-03-01 15:33:21 | 2017-03
| 8 | Article 8 | 2017-03-01 17:32:21 | 2017-03
| 9 | Article 9 | 2017-03-01 18:31:21 | 2017-03
+ ---- + -------------------------------------- + ----- ---------------- +
select count(*) from blog group by month;
select date_format(sub_time,'%Y-%m'),count(id) from blog group by date_format(sub_time,'%Y-%m');
Process control
if conditional statement
# if conditional statement
delimiter //
CREATE PROCEDURE proc_if ()
BEGIN
declare i int default 0;
if i = 1 THEN
SELECT 1;
ELSEIF i = 2 THEN
SELECT 2;
ELSE
SELECT 7;
END IF;
END //
delimiter ;
while loop
# while loop
delimiter //
CREATE PROCEDURE proc_while ()
BEGIN
DECLARE num INT;
SET num = 0;
WHILE num < 10 DO
SELECT
num;
SET num = num + 1;
END WHILE ;
END //
delimiter ;
Index
Knowledge review: Data is stored on the hard disk, so querying data inevitably requires IO operations.
An index is a data structure, similar to the table of contents of a book. This means that when looking for data in the future, you should first look for the directory and then look for the data, rather than querying the data by turning pages.
Index, also called “key” in MySQL, is a data structure used by the storage engine to quickly find records.
-
primary key
-
unique key
-
index key
Note: In addition to the effect of accelerating queries, the first two of the above three keys also have additional constraints (primary key: non-empty and unique, unique key: unique), while the index key does not have any constraint function and will only help you speed up. Inquire
The essence is: filter out the final desired results by continuously narrowing the scope of the data you want to obtain, and at the same time turn random events into sequential events. In other words, with this indexing mechanism, we can Always use the same search method to lock data.
Impact of indexing:
If there is a large amount of data in the table, index creation will be very slow (when creating the table, if you obviously need an index, add it in advance)
# When actually adding indexes in the future, try to add them when the table is empty, and add indexes when creating the table. Adding indexes at this time is the fastest.
# If the data in the table already exists, you still need to add an index. This is OK, but the speed of creating the index will be very slow. This is not recommended.
After the index is created, the query performance of the table will be greatly improved, but the write performance will be reduced.
# However, the performance impact of writing is not great, because in practice, the frequency of writing is very small, and most operations are queries.
# How to add an index? Which fields should be indexed?
”’There is no fixed answer. Which field to index depends on your actual query conditions”’
select * from user where name=” and password=”;
# The use of index actually requires a lot of work experience to correctly judge
”’Don’t add indexes as soon as you create a table. In a table, there should be no more than 15 indexes at most. The more indexes, the performance will decrease”’
# How to reduce the amount of data and do not need to add indexes. Generally, there is no need to add indexes if the data is less than 10 million. Mysql is suitable for data of less than 10 million, and the performance will not drop too much.
b + tree
Tree——>Binary tree Balanced tree b-tree b + tree b-tree…
Only leaf nodes store real data, and root and branch nodes only store virtual data.
The number of queries is determined by the level of the tree. The lower the level, the fewer the times.
The size of a disk block is certain, which means that the amount of data that can be stored is certain. How to ensure that the level of the tree is the lowest? A disk block stores data items that occupy relatively small space.
# When adding indexes in the future, try to add columns that store numbers in the fields. We use the primary key to query quickly. select * from user where name = '' select * from user where id = '' # Primary key query is faster
Thinking about what fields in a table we should index can reduce the height of the tree >>> Primary key id field
Clustered index (primary key)
Clustered index actually refers to the primary key of the table. The InnoDB engine stipulates that a table must have a primary key. Let’s first review the storage engine.
When myisam creates a table, how many files (three) are corresponding to the hard disk?
When innodb creates a table, how many files (two) are there corresponding to the hard disk? The frm file only stores the table structure and cannot contain indexes, which means that the indexes and data of innodb are placed in the idb table data file.
Features: Complete records placed in leaf nodes
Auxiliary index (unique, index)
Auxiliary index: When querying data, it is not possible to use ID as a filtering condition, but also to use field information such as name, password, etc. Then at this time, the accelerated query effect of the clustered index cannot be used. You need to create indexes for other fields. These indexes are called auxiliary indexes.
Features: The leaf node stores the value of the primary key of the record corresponding to the auxiliary index field (for example, if you create an index based on the name field, then the leaf node stores: {the value corresponding to name: name The primary key value of the record where it is located})
select name from user where name='jack';
The above statement is called a covering index: all the data we want has been found only in the leaf nodes of the auxiliary index.
select age from user where name='jack';
The above statement is called a non-covering index. Although the index field name is hit during the query, the age field is being searched, so the primary key is still needed to search.