Python learning log
Home page of RBHGO Welcome to pay attention
Warm tip: creation is not easy. If reprinted, indicate the source. Thank you for your cooperation~
catalogue
Python learning log Lesson 17 - Python database programming
DCL (data control language)
DCL - > grant or recall user permission - > grant / revoke
In the first two articles, SQL DDL (data definition language), DML (data operation language) and DQL (data query language) are described in turn. This article will share the last DCL with you first. You can think about it. When we build a complete database, we certainly don't let ourselves use it alone. Developers should use it, data analysts should use it, front-end personnel may use it, and super administrators should also use it; So it's impossible for everyone to log in to the database with an administrator account. That's not a mess. Although we can manually configure it in the graphical tool, the operation of each database is different; However, SQL statement access is the same, so if you want to do this line, you'd better study and understand all SQL statements. Of course, the focus is to learn your own professional direction of SQL. The databases used in this article are from RBHGO's learning log 16
Next, I'll share DCL with you
-- Create a user (an account that can log in remotely and assign a password to it)---> @The number is preceded by the account name and followed by IP address
-- IP The address can be detailed or available%It means fuzzy, because most of the time there will be more than one at the same time guest The account is connected to the database at the same time
-- for example % / 114.120.138.% / 127.0.0.1
create user 'guest'@'%' identified by '123456789';
-- delete user
drop user 'guest'@'%';
-- Change password (will be named guest Change your account password to guest.123)
alter user 'guest'@'%' identified by 'guest.123';
-- to grant authorization
-- grant guest yes hrs Operation permissions of the library database_name.table_name (Database name English point two-dimensional table name)
-- For example, here hrs.* ---> express hrs All tables in the library
grant select，insert, delete, update on hrs.* to 'guest'@'%';
-- Full authorization
-- Authorization, except that it cannot be granted to others
grant all privileges on *.* to 'guest'@'%';
-- Full authorization
grant all privileges on *.* to 'guest'@'%' with grant option;
-- Recall authority(recall guest yes hrs Of all objects in the database select/insert/delete/update jurisdiction)
revoke select，insert, delete, update on hrs.* from 'guest'@'%';
-- Let the authority take effect immediately
flush privileges;
Get to the point
Python connection to MySQL
In Python 3, we usually use PyMySQL, a pure Python third-party library, to access MySQL databases. It should be the best choice for Python to operate MySQL databases at present.
Install PyMySQL.
At the terminal or Terminal Medium input pip install pymysql After the download is complete, in the project file import pymysql
Key tips:
Because of the SQL injection attack, you cannot use any form of formatted (spliced) string when writing SQL statements in python code
"""
Python connect MySQL,insert data
Install a third-party library:
- pymysql ---> pure python Write, and the installation will be successful
- mysqlclient ---> Bottom layer C Writing, the installation may not succeed
Transaction: treat several (add, delete and modify) operations as an indivisible atomic operation, either all succeed or all fail
- If the operation is successful, we can connect the object through the commit Method manual submission
- If the operation fails, we can connect the object through the rollback Method rollback, delete operation
Author: RBHGO
Declaration: Mia San Mia ~~~
"""
import pymysql
no = input('Please enter the department number:')
name = input('Please enter Department Name:')
area = input('Please enter Department Address:')
# Step 1: create a connection
# Creating a connection by default is equivalent to opening a business environment
# Transaction: treat several (add, delete and modify) operations as an indivisible atomic operation, either all succeed or all fail
# -If the operation is successful, we can submit it manually through the commit method of the connection object
# -If the operation fails, we can roll back the transaction and delete the operation through the rollback method of the connection object
conn = pymysql.connect(host='localhost', port=3306,
user='User name you set', password='Your password',
database='hrs', charset='utf8mb4')
# The default is manual submission. You want to automatically connect() plus the parameter autocommit=True
try:
# Step 2: get cursor object
with conn.cursor() as cursor:
# Step 3: issue SQL statement to the database through cursor object to obtain execution result (SQL uses% s as placeholder, followed by tuple input)
affected_rows = cursor.execute(
'insert into tb_dept values(%s, %s, %s)',
(no, name, area))
print(affected_rows)
# Step 4: submit manually
conn.commit()
except pymysql.MySQLError:
# Step 4 failure: manual rollback
conn.rollback()
finally:
# Step 5: close the connection to release resources
conn.close()
"""
Python connect MySQL,Delete data
Author: RBHGO
"""
import pymysql
no = input('Please enter the department number to delete:')
conn = pymysql.connect(host='localhost', port=3306,
user='User name you set', password='Your password',
database='hrs', charset='utf8mb4')
try:
# Step 2: get cursor object
with conn.cursor() as cursor:
# Step 3: issue SQL statements to the database through the cursor object to obtain the execution results
affected_rows = cursor.execute(
'delete from tb_dept where dno=%s', (no,))
print(affected_rows)
# Step 4: submit manually
conn.commit()
except pymysql.MySQLError:
# Step 4 failure: manual rollback
conn.rollback()
finally:
# Step 5: close the connection to release resources
conn.close()
"""
Python connect MySQL,Update data
Author: RBHGO
"""
import pymysql
name = input('Modify the Department name as:')
area = input('The modified department address is:')
no = input('The department number to be modified is:')
conn = pymysql.connect(host='localhost', port=3306,
user='User name you set', password='Your password',
database='hrs', charset='utf8mb4')
try:
# Step 2: get cursor object
with conn.cursor() as cursor:
# Step 3: issue SQL statements to the database through the cursor object to obtain the execution results
affected_rows = cursor.execute(
'update tb_dept set dname=%s, dloc=%s where dno=%s',
(name, area, no))
print(affected_rows)
# Step 4: submit manually
conn.commit()
except pymysql.MySQLError:
# Step 4 failure: manual rollback
conn.rollback()
finally:
# Step 5: close the connection to release resources
conn.close()
"""
Python connect MySQL,query
Author: RBHGO
"""
import pymysql
conn = pymysql.connect(host='localhost', port=3306,
user='User name you set', password='Your password',
database='hrs', charset='utf8mb4')
try:
with conn.cursor() as cursor:
# Grab data through cursor
cursor.execute('select dno, dname, dloc from tb_dept')
# Print (cursor. Fetchone()) --- > grab one
# Print (cursor. Fetchall()) --- > grab all
# Print (cursor. Fetchmany()) --- > grab the specified quantity
dept_row = cursor.fetchone()
while dept_row:
print(f'Department number: {dept_row[0]}')
print(f'Department name:{dept_row[1]}')
print(f'Department location: {dept_row[2]}')
print('-' * 20)
dept_row = cursor.fetchone()
except pymysql.MySQLError as err:
print(err)
finally:
conn.close()
"""
The cursor type is set as a dictionary, and the operability is strengthened by taking values through key value pairs
Author: RBHGO
"""
import pymysql
conn = pymysql.connect(host='localhost', port=3306,
user='User name you set', password='Your password',
database='hrs', charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)
try:
with conn.cursor(pymysql.cursors.DictCursor) as cursor:
# Grab data through cursor
cursor.execute('select dno as no, dname as name, dloc as loc from tb_dept')
dept_row = cursor.fetchone()
while dept_row:
print(dept_row.get('no'), dept_row.get('name'), dept_row.get('loc'))
dept_row = cursor.fetchone()
except pymysql.MySQLError as err:
print(err)
finally:
conn.close()
"""
Face object
Author: RBHGO
"""
import pymysql
class Dept:
def __init__(self, no, name, location):
self.no = no
self.name = name
self.location = location
def __str__(self):
return f'number:{self.no} \n name:{self.name} \n Location:{self.location}'
conn = pymysql.connect(host='localhost', port=3306,
user='User name you set', password='Your password',
database='hrs', charset='utf8mb4')
try:
with conn.cursor() as cursor:
# Grab data through cursor
cursor.execute('select dno, dname, dloc from tb_dept')
dept_row = cursor.fetchone()
while dept_row:
print(dept_row.get('no'), dept_row.get('name'), dept_row.get('loc'))
dept_row = cursor.fetchone()
except pymysql.MySQLError as err:
print(err)
finally:
conn.close()
"""
Batch insert
Author: RBHGO
"""
import time
import pymysql
conn = pymysql.connect(host='localhost', port=3306,
user='User name you set', password='Your password',
database='hrs', charset='utf8mb4')
start = time.time()
try:
# Step 2: get cursor object
with conn.cursor() as cursor:
params = []
for i in range(1, 50001):
params.append((f'user{i}',))
if i % 1000 == 0:
cursor.executemany(
'insert into tb_user(username) values (%s)',
params
)
params.clear()
conn.commit()
# time
end = time.time()
print(f'time consuming{end - start:.2f}second')
except pymysql.MySQLError:
# Step 4 failure: manual rollback
conn.rollback()
finally:
# Step 5: close the connection to release resources
conn.close()
Expand knowledge
Xiaowei will share some other knowledge with you. It's not that they don't share in detail. It's because I don't use it often and don't have a special connection, so I can only make an introduction here. If you are interested, you can check the materials and learn.
Indexes
Index is the most important means to improve query performance in relational database. An index in a relational database is like the directory of a book. We can imagine that if we want to find a certain knowledge point from a book, but the book has no directory, it is extremely bad. Although creating an index will bring the overhead of storage space, just as the directory of a book will occupy part of the space, the reduction of query time after sacrificing space is also very significant. Space and time can not be balanced.
In MySQL, columns of all data types can be indexed. The commonly used storage engines InnoDB and MyISAM can support the creation of 16 indexes for each table. The underlying algorithm of index used by InnoDB and MyISAM is B-tree (B-tree). B-tree is a self balanced tree, similar to balanced binary sort tree, which can maintain data order. This data structure can complete the operations of finding data, sequential access, inserting data and deleting in logarithmic time.
-- In short:
-- The essence of index is like a well ordered directory to speed up query
-- The index usually needs to be built on the columns that users often query the filter criteria, so as to effectively speed up the query
-- Don't abuse the index, because although the index speeds up the query, it will make the operation of inserting data slower
-- about InnoDB For the engine, the bottom layer of the index is a B+Trees, B+A tree is a hierarchy
-- This structure has excellent disks I/O Performance, a 4-tier B+Trees can handle a billion levels of data
-- about InnoDB For the engine, an index will be built on the primary key by default, and the index is the data of the whole table
-- This index is also called a clustered index(Only one), and the indexes we create are non clustered indexes
As mentioned in the previous share, we can use the explain keyword of MySQL to view the SQL execution plan.
Using the performance profiling system:
explain select statement - > get the performance of the search statement
Usually, we just look at type and rows
type performance (bad - > good) all - > index - > range - > ref - > ref eg - > const / svstem
In the SQL execution plan, you will get the following information:
- select_type: the type of query.
- SIMPLE: SIMPLE SELECT, no UNION operation or subquery is required.
- PRIMARY: if the query contains subqueries, the outermost SELECT is marked PRIMARY.
- UNION: the second or subsequent SELECT statement in a UNION operation.
- SUBQUERY: the first SELECT in the SUBQUERY.
- DERIVED: SELECT subquery of DERIVED table.
- Table: query the corresponding table.
- Type: the way MySQL finds rows that meet the conditions in the table, also known as access type, Including: ALL (full table scan), index (full index scan, only traversing the index tree), range (index range scan), ref (non unique index scan), eq_ref (unique index scan), const/system (constant level query), NULL (no need to access the table or index). Of ALL access types, it is obvious that ALL has the worst performance. It represents a full table scan, which means to scan every row in the table to find a matching row.
- possible_keys: MySQL can select indexes, but they may not be used.
- key: the index actually used by MySQL. If it is NULL, it means that no index is used.
- key_len: the length of the index used must be as short as possible without affecting the query.
- Rows: the number of rows to scan to execute the query. This is an estimated value.
- extra: additional information about queries.
- Using filesort: MySQL cannot use the index to complete the sort operation.
- Using index: only use the information of the index without further looking up the table to get more information.
- Using temporary: MySQL needs to use temporary tables to store result sets, which are often used for grouping and sorting.
- Impossible where: the where clause will result in no eligible rows.
- Distinct: after MySQL finds the first matching row, it stops searching for more rows for the current row combination.
- Using where: the query column is not overwritten by the index, and the filter condition is not the leading column of the index.
-- Create index
create index idx_emp_ename on tb_emp (ename);
-- Prefix index
create index idx_emp_ename on tb_emp (ename(1));
-- Index coverage problem
-- If the column of the query is not overwritten by the index, a back to table phenomenon will occur
-- If we don't want the phenomenon of returning to the table, we can use the composite index to cover all the columns required by the query as much as possible when creating the index
select eno, ename, job from tb_emp where ename='Zeng Shuang';
-- Composite index
drop index idx_emp_ename_job on tb_emp;
create index idx_emp_ename_job on tb_emp (ename, job);
explain select eno, ename, job from tb_emp where ename='once';
explain select eno, ename, job from tb_emp where ename like 'once%' order by ename;
explain select eno, ename, job from tb_emp where ename like 'once%' order by sal;
explain select eno, ename, job from tb_emp
where ename='Zeng Shuang' and job='CEO';
-- Negative conditions cannot optimize queries with indexes
explain select eno, ename, job from tb_emp where ename<>'Zeng Shuang';
explain select eno, ename, job from tb_emp where ename like 'once%';
-- If a wildcard is used on the left side of a fuzzy query, the index will be invalidated
explain select eno, ename, job from tb_emp where ename like '%frost';
-- If a function is used in the filter criteria, the index will also be invalidated
explain select eno, ename, job from tb_emp where concat(ename, 'ma'am')='Ms. Zeng Shuang';
-- The following queries cannot use composite indexes( or)
explain select eno, ename, job from tb_emp
where ename='Zeng Shuang' or job='CEO';
-- The following queries cannot use composite indexes
explain select eno, ename, job from tb_emp where job='CEO';
-- Delete index
drop index idx_emp_ename on tb_emp;
alter table tb_emp drop index idx_emp_ename;
view
-- view -- A snapshot is generated for the query, and the query results can be obtained directly through the view in the future -- You can restrict users' access to data to specific columns through views -- A view is a virtual table．The updatability of the view depends on the specific situation (updating the view is actually updating the table behind the view) -- Create view create view vi_emp_simple as select eno, ename, job, dno from tb_emp; -- Delete view drop view vw_dept_emp_count; -- If you want to update the view, you can use the above command to delete the view, or you can use the`create or replace view`To update the view.
Rules and restrictions for views.
- Views can be nested, and data retrieved from other views can be used to construct a new view. Views can also be used with tables.
- You can use the order by clause when creating a view, but if you also use order by when retrieving data from a view, the original order by in the view will be overwritten.
- The view cannot use the index and will not trigger the execution of triggers (triggers are usually not recommended because of performance and other considerations in actual development, so we will not introduce this concept).
stored procedure
-- Stored procedures (also known as procedures) are a set of pre compiled procedures stored in the database SQL The calling process can simplify the work of application developers and reduce the communication with the database server, which is also helpful to improve the performance of data operations. In fact, so far, we have used SQL Statements are single statements for one or more tables, but in actual development, we often encounter that a certain operation requires multiple statements SQL Statement to complete.
-- View security related variables
show variables like '%safe%';
-- (storage)process:A series of SQL Assemble them together, compile and store them for calling
-- Delete stored procedure
-- drop procedure if exists sp_upgrade_emp_sal;
-- Create stored procedure
delimiter $$
create procedure sp_upgrade_emp_sal()
begin
set sql_safe_update=off;
update tb_emp set sal=sal+200 where dno=10;
update tb_emp set sal=sal+500 where dno=20;
update tb_emp set sal=sal+800 where dno=30;
end $$
delimiter ;
-- Call the stored procedure to modify the database data directly( hrs Department number in the library (corresponding to employee salary increase)
call sp_upgrade_emp_sal();
In the process, we can define variables and conditions, use branch and loop statements, operate query results through cursors, and use event scheduler. Although we have said a lot about the benefits of the process, in the actual development, if we excessively use the process and put a large number of complex operations into the process, it will inevitably occupy the CPU resources of the database server and cause the database server to bear great pressure. To this end, we generally hand over complex operations and processing to the application server, because it is easy to deploy multiple application servers to share these pressures.
SQL definition function
-- Delete function
-- drop function truncate_string;
-- use SQL Statement encapsulation function,SQL Is a computer language, of course, can also be used to write functions.
-- delimiter End the statement from;Change to $$(After writing, remember to change it back), and in the function no sql Declare that this paragraph is not sql Statement (but sql Encapsulated functions)
delimiter $$
create function truncate_string(
content varchar(16000),
max_length int unsigned
) returns varchar(16000) no sql
begin
declare result varchar(16000) default '';
if length(content) > max_length then
set result=concat(substring(content, 1, max_length), '......');
else
set result=content;
end if;
return result;
end $$
delimiter ;
-- test
select truncate_string('Long night, Miss fantasy, ready? Your bag! Tomorrow, I will go alone, facing the noon and the rising sun.', 25);
select
col_id,
col_name,
truncate_string(col_intro, 250)
from
tb_college;
Thank you for your study and company. Your praise and comments are the driving force for me to update