Python full stack introduction to MySQL database-Mysql Tutorial-php.cn

mysql tutorialColumn Python full stack explanation database

Python full stack introduction to MySQL 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 libraries

Library name table 1 Table 2… 1. Restore the backup database

mysql -uroot -p –one-database Target database name< xxx.sql 2. Restore a certain database in the backupRestore: table is not deleted, table record is deleted Override

MySQL remote connection:

sudo -i 1. Administrator mode

cd /etc/mysql/mysql.conf.d/ 2 .cd to the mysql directoryvi mysqld.cnf 3. Open and comment out the default IP

#bind-address = 127.0.0.1 4. Save

/etc/init. D/MySQL RESTART 5. Restart service

授 Authorized users:

g 授授授授 d. with grant option 1. Command format

Example: grant all privileges on *.* to “tiger”@”%” identified by “123” with grant option; 2.Exampleall privileges , Select, Insert ... 4. Library. Form: *. *All libraries all tables 3. permissions list

Python3 module installation:

Module name: pymysql

Online: sudo pip3 install pymysql

Offline: pymysql-0.7.11.tar.gz

$ tar -zxvf pymyql-0.7.11.tar.gz

$ cd pymysql-0.7.11

$ sudo python3 setup.py install

Verification:

$ python3

>>> 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 records

ORM ：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));
#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; ();
…

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 Name

fields terminated by “delimiter”

lines terminated by “n”;

Data export:

select … from table name

into outfile “/var/lib/mysql-files/file name”

fields terminated by “delimiter”

lines terminated by “n”;

Data recovery:

Restore a single library

mysql -uroot -p < Target library name xxx.sql

From Restore a certain database from all database backups (-one-database)

mysql -uroot -p –one-database Target database name < xxx.sql

Restore: table records will not be deleted Delete and overwrite

data backup:

mysqldump -u user -p source library name> ~/xxx.sql

–all-databases backup All libraries

Library name Back up a single library

-B Library 1 Library 2.. Back up multiple libraries

Library name table 1 Table 2... Back up the specified library specified table

Running 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.where

2.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()

Copy after login

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)
# ((&#39;7c4a8d09ca3762af61e59520943dc26494f8941b&#39;,),)

if len(result) == 0:
    print("用户名不存在")
elif result[0][0] == pwd2:
    print("登录成功")
else:
    print("密码错误")

Copy after login

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)

Copy after login

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!