Python full stack introduction to MySQL database

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

$ 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));

#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 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()

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!