Home >Database >Mysql Tutorial >Python full stack introduction to MySQL database
Python full stack introduction to MySQL database
- coldplay.xixiforward
- 2020-12-17 10:38:482258browse
mysql tutorialColumn Python full stack explanation database
Recommended (free): mysql tutorial
Mainly three aspects:
1.Linux terminal command
2.MySQL statement
3.Python call
Terminal command:
vi . because python3-pip pip3 pip ient Installation client Duan
# Sudo Apt-Get Update Read the list Save to/var/lib/apt/list/lists
Sudo Apt-Get Upgrade. /init.d/mysql status Query status
sudo /etc/init.d/mysql stop Stop service
sudo /etc/init.d/mysql restart Restart service
sudo /etc/init.d/mysql reload Reload
mysql -h host address-u username-p password Link mysql
Modify mysql default character set:sudo -i Modify the file permissions
cd etc/mysql/mysql.conf.d Directory
cp etc/msql.cnf/mysqld.cnf.bak 4. Backup
subl mysqld.cnf Open the source file
[mysqld] Directory
character_set_server = utf8 6. Add the command /etc/init.d/mysql
mysqldump -u user-p source library name> ~/xxx.sql
Data backup:
Parameters: –all-databases 1. Back up all libraries . Back up a single library -b library 1 library 2. 3. Back up multiple librariesLibrary name table 1 Table 2… 1. Restore the backup database
mysql -uroot -p –one-database Target database namef54965f696c4fe33613b4d94878d218b>> import pymysql
> install mysql-python
sqlalchemy framework installation:Online: sudo pip3 install sqlalchemy
Offline:
$ tar -zxvf SQLAlchemy-1.2.10.tar.gz
$ cd SQLAlchemy-1.2.10 $ sudo python3 setup.py install
Verification:
$ python3
,,,, , import * Import module
(db = pymysql.connect(…)) c.execute(“insert….”) Cursor method:
db.commit() 4. Submit to the database
c.close()
db.close()7.connect object:
db = pymysql.connect (parameter list)
1. host: host address, localhost
2. port: port number, default 3306
3. user: username
4. password: password
5. database: database
6. Charset: encoding method, it is recommended to use utf8
8.Method of connecting object:
Database connection object ( db) method
1. db.close() closes the connection
2. db.commit() submits it to the database for execution
3. db.rollback() returns Roll
4. cur = db.cursor() returns a cursor object, used to execute specific SQL commands
9.Methods of cursor objects:
Methods of cursor object (cur)
1. cur.execute(sql command, [list]) Execute SQL command
2. cur.close() Close Cursor object
3, Cur.Fetchone () Get the first data of the query results set
((record 1),)
## 4, cur.Fetchmany (n ) Get n records ((Record 1), (Record 2)) 5. cur.fetchall() Get all recordsORM ：orm (Object Relation Mapping Object Relational Mapping) Definition: Map the object model to the MySQL database
SQL command:
- /var/ lib/mysql Query the commit transaction
- begin; Open transaction
- commit; Terminate transaction
- system sudo -i Directly enter the terminal
- show databases;
create database database name charcater set utf8; Character set
select database ();
# Use use library name; switch library
# Drop database library name; delete library
Show tables; see Already have a table
create table table name (field name 1 data type, ....); View table character set
- ##desc table name;
#insert into table name values (value 1), (value 2)...; Insert complete record
insert into table name (field name 1,...) values (value 1 ),...; Insert field data
select * from table name [where condition]; Name 2,...from table name [where condition]; View fields
-
alter table table name add field name data type; alter table table name add field name data type first;
#alter table table name drop field name; ##alter table table name rename table name; Delete table records (must add where)
update table name set field 1=value 1, field name 2=value 2,...where condition Change table record (where must be added)
alter table table name change original name new name data type; create table table name select * from table name where false;
sex enum ("M","F","S") not null defaulf "S" Constraints
show variables like variable name; Query MySQL variable
select field name list from table name list; ,t2 where condition Multi-table query
create index index name on table name (field name); .index (field name), ...) When creating a table, create a common index
- Drop Index index on the name on the name; show index from table name;
create table table name (…., unique key (field name));
#show unique index from table name; #create table table name (…., id int, primary key (field name)); Create a primary key index when creating the table
(id int primary key auto_increment ,)auto_increment=10000; Set the starting value of auto-increment
alter table table name modify id int auto_increment; Table name Auto_increment = 20000; Modify the starting value from the starting value
Al Table Table name modify id into; Name drop primary key; View table key)
Non_Unique: 1 ：index ’s ’ s ‐ ‐ ‐ ‐ ‐ ‐‐ n‐‐ , 1 to 1 to 1 to 1 to 1
Non_Unique: 0 : unique . Delete foreign keys
show create table table name; ();
create table t2 (…
foreign key (reference field name)
references main table (referenced field name)on delete cascade action
on update cascade action);
Add foreign key:alter table table name add
foreign key (reference field) references main table (referenced field)
on delete …
on update …
Cascading actions:
restrict (default) does not allow the master table to operate the slave table
cascade: follow delete and update
set null: the slave table value is NULL after the master table is changed
Inner link:select field name from table 1
inner join table 2 on conditions
inner join table 3 on Condition...；
External link. Left link:Display query results based on the left table
Select field name from table 1
LEFT JOIN Table 2 ON Conditions
LEFT JOIN Table 3 ON Conditions…;
Right link
## This is displayed on the right table to display query Result
Data import:
load data infile "file name"
into table table Namefields terminated by “delimiter”lines terminated by “n”;
Data export:
select … from table nameinto outfile “/var/lib/mysql-files/file name”fields terminated by “delimiter”lines terminated by “n”;Data recovery:
Restore a single library mysql -uroot -p a9755e94113b77bf0c4bda48ade67352 ~/xxx.sql–all-databases backup All librariesLibrary name Back up a single library-B Library 1 Library 2.. Back up multiple librariesLibrary name table 1 Table 2... Back up the specified library specified tableRunning time detection:
Turn on: set profiling=1; Turn off: set profiling=0; Query execution record: show profilings;
SQL query:
3.select … aggregate function from table name 1.where2.group by…4.having …5.order by …
6.limit …；
Query nesting:
select ... from table name where condition (select ....);## where
(country,gongji) in
(select country,max(gongji) from sanguo group by country);
where: can only be operated Fields that actually exist in the table
group by: Group the query resultshaving: Further filter the query results
distinct: Do not display duplicate field values
show engines;
alter table table name engine=innodb; Add storage engine
InnoDB:InnoDB features (2 files):
Row-level locks, support for foreign keys, transaction operations
.frm (table structure, index), .ibd (table records)
MyISAM:
MyISAM features (3 files): . .myd (record), .myi (index)
Lock:select: After adding the read lock, others cannot change the table records, but they can query insert, delete, update: After the write lock is added, others cannot check or change it
Lock granularity:
Table-level lock: myisam
Row-level lock: innodb
Tuning:1. Choose the appropriate storage engine 2. Create indexes on common fields
3. Where to avoid Use !=, NULL judgment, or link,
like preceded by %, in, not in, * instead of field,
Data type:Data type: int ’ ’ ’ together to ’ t ’s ’ through ’ through ’ through ’ to ’ t out out out through out out out out out out through out through ''' ’ ’s ’ ’s ’ through ’ s ‐ w ‐ w t t i s t t Signed (signed default): -128 ~ 127
. . Integer type (8 bytes)
float Float (4 bytes, 7 significant digits)
Field name float (m, n) m: total number of digits n: decimal place Number
decimal using using ’ s ’ s ’ s ‐ ‐ ‐ ‐ ‐ ‐ ‐ 1 Packed into 4 bytes
Remainder Bytes
0 0
1-2 1
3-4 2
5 -6 3
7-9 4
Field name enum (value 1, value 2...); 2 ...); multiple choice (set)
(Putting it in one string for multiple, separated)
Date: "yyyy-mm-dd"
time: "HH:MM:SS"
datetime: "YYYY-MM-DD HH:MM:SS"
timestamp: "YYYY-MM-DD HH:MM:SS"
datetime: Null is returned by default if no value is given
timestamp: system time is returned by default if no value is given
Time function
now() Time
Curdate () Return to the current period
# Curtime () Return to the current date
# Year (date) Return to the specified time
Date (date) Return the date at the specified time
time(date) ##sum (field name): sum
max (field name): maximum value
min (field name): minimum value
count (field name): Count the number of this field
Operator: – * / %
Time operator
select * from table name
where field name operator (Time-interval time interval unit);
Time interval unit: 1 day | 2hour | 1 minute | 2year | month
Value comparison: = != > >= < < ;=
Character comparison: = !=
Logical comparison: and or
Comparison within range:
1.where field name between value 1 and value 2
2.where field name in (value 1, value 2,….)
3.where field name not in (value 1, value 2,...)
empty: where name is null
non-empty: where name is not null
NILL: Null value, you can only use is or is not to match
"": Empty string, use = or != to match
Fuzzy comparison:
where Field name like expression
Expression
_: Match a single character
%: Match 0 to multiple characters
NULL will not be counted
Sort: order by ASC | DESC
Display: limit starting display position, number of items
Display n records per page, display page m:
limit (m-1)*n, n
MySQL and Python interaction
# mysqlpython.py
# 导入mysql模块
from pymysql import *
class MysqlPython:
def __init__(self, database, # 库
host="127.0.0.1", # ip地址
user="root", # 用户名
password="123456", # 密码
port=3306, # 端口
charset="utf8"): # 字符集
self.host = host
self.database = database
self.user = user
self.password = password
self.port = port
self.charset = charset
def open(self): # 创建数据库链接函数
self.db = connect(host=self.host,
database=self.database,
user=self.user,
password=self.password,
port=self.port,
charset=self.charset)
self.cur = self.db.cursor() # 创建游标对象
def close(self): # 创建断开数据库链接 关闭游标函数
self.cur.close()
self.db.close()
def zhixing(self, sql, L=[]): # 创建pymysql.execute() 方法函数
try:
self.open() # 链接数据库
self.cur.execute(sql, L) # 参数化执行ＳＱＬ命令
self.db.commit() # 提交数据
print("ok")
except Exception as e:
self.db.rollback() # 出错取消提交
print("Failed", e)
self.close() # 断开数据库链接 关闭游标
def all(self, sql, L=[]):
try:
self.open()
self.cur.execute(sql, L)
result = self.cur.fetchall()
return result
except Exception as e:
print("Failed", e)
self.close()Database user login
from mysqlpython import Mysqlpython
from hashlib import sha1
uname = input("请输入用户名:")
pwd = input("请输入密码:")
# 用sha1给pwd加密
s1 = sha1() # 创建sha1加密对象
s1.update(pwd.encode("utf8")) # 指定编码
pwd2 = s1.hexdigest() # 返回16进制加密结果
sqlh = Mysqlpython("db4")
select = "select password from user where
username=%s;"
result = sqlh.all(select, [uname])
# print(result)
# (('7c4a8d09ca3762af61e59520943dc26494f8941b',),)
if len(result) == 0:
print("用户名不存在")
elif result[0][0] == pwd2:
print("登录成功")
else:
print("密码错误")ORM sqlalchemy framework
# 创建一张表 # 连接数据库的模块 from
sqlalchemy import create_engine fromsqlalchemy.ext.declarative
import declarative_base from sqlalchemy import Column, Integer
,String engine = create_engine("mysql+pymysql://root:123456@localhost/db4",
encoding="utf8") Base = declarative_base() # orm基类 class User(Base):
# 继承Base基类 __tablename__ = "t123" id =Column(Integer, primary_key=True)
name = Column(String(20)) address = Column(String(40))Base.metadata.create_all
(engine)The above is the detailed content of Python full stack introduction to MySQL database. For more information, please follow other related articles on the PHP Chinese website!