Shulou(Shulou.com)06/01 Report--

This article shares with you an example of a full stack introduction to the MySQL database by Python. The editor thought it was very practical, so I shared it with you as a reference. Let's follow the editor and have a look.

There are three main aspects:

1.Linux terminal command

2.MySQL statement

3.Python call

Terminal command:

Vi text editor

Cat / etc/password | grep "user name" gets the user table

Sudo-I get root permission

Sudo apt-get install python3-pip install pip3

Sudo pip3 install pymysql install mysql

Sudo apt-get install mysql-server installation server

Sudo apt-get install mysql-client install client

Sudo apt-get update read list saved to / var/lib/apt/lists

Sudo apt-get upgrade compares the download list and updates

Sudo / etc/init.d/mysql status query status

Sudo / etc/init.d/mysql stop out of 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 the mysql default character set:

Sudo-I 1. Get root

Chmod 644 filename 2. Modify file permissions

Cd etc/mysql/mysql.conf.d 3. Enter the configuration text directory

Cp etc/msql.cnf/mysqld.cnf.bak 4. Backup

Subl mysqld.cnf 5.vi opens the source file

[mysqld] directory

Character_set_server = utf8 6. Add command

/ etc/init.d/mysql

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

Data backup:

Parameters:

-all-databases 1. Back up all libraries

Library name 2. Back up a single library

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

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

Data recovery:

Mysql-uroot-p

< 目标库名 xxx.sql 1. 恢复备份库 mysql -uroot -p -one-database 目标库名 < xxx.sql 2. 恢复备份内某一个库 恢复：表不删除 表记录删除覆盖 MySQL远程连接： sudo -i 1.管理员模式 cd /etc/mysql/mysql.conf.d/ 2.cd到mysql目录 vi mysqld.cnf 3.打开注释掉默认IP #bind-address = 127.0.0.1 4.保存 /etc/init.d/mysql restart 5.重启服务 授权用户： grant 授权列表 on 库.表 to "用户名"@"%"identified by "密码" with grant option 1.命令格式 示例：grant all privileges on *.* to "tiger"@"%" identified by "123" with grant option; 2.示例 all privileges、select、insert … 4.库.表: *.* 所有库所有表 3.权限列表 python3模块安装： 模块名 ：pymysql 在线 ：sudo pip3 install pymysql 离线 ：pymysql-0.7.11.tar.gz $ tar -zxvf pymyql-0.7.11.tar.gz $ cd pymysql-0.7.11 $ sudo python3 setup.py install 验证： $ python3 >

> > import pymysql

> > >

Python2 module installation:

Module name: MySQLdb

Installation: sudo pip 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

Verify:

$python3

> import sqlalchemy

> > >

Pymysql uses:

From pymsql import * Import module

(db = pymysql.connect (…) 1. Establish a database connection

C = db.cursor () 2. Create a cursor object

C.execute ("insert ….") 3. Cursor method:

Db.commit () 4. Submit to database

C.close () 5. Close the cursor object

Db.close () 6. Disconnect the database:

7.connect object:

Db = pymysql.connect (parameter list)

1. Host: host address, local localhost

2. Port: Port number. Default is 3306.

3. User: user name

4. Password: password

5. Database: library

6. Charset: encoding method. Utf8 is recommended.

8. How to connect objects:

The method of database connection object (db)

1. Db.close () closes the connection

2. Db.commit () is submitted to the database for execution

3. Db.rollback () rollback

4. Cur = db.cursor () returns a cursor object, which is used to execute specific SQL commands

9. Methods for cursor objects:

The method of cursor object (cur)

1. Cur.execute (sql command, [list]) executes the SQL command

2. Cur.close () closes the cursor object

3. Cur.fetchone () gets the first piece of data of the query result set

((record 1))

4. Cur.fetchmany (n) get n entries

((record 1), (record 2))

5. Cur.fetchall () gets all the records

ORM:orm (Object Relation Mapping object Relational Mapping) definition: mapping an object model to an MySQL database

SQL command:

/ var/lib/mysql MySQL data directory

Show variables like "autocommit"; query commit transactions

Begin; starts a transaction

Commit; commit transaction (MySQL default autocommit)

Rollback; terminates a transaction

System sudo-I enters the terminal directly from the data.

Show databases; to view existing libraries

Create database library name; create library

Create database library name charcater set utf8; specified character set

Show create database library name; view library character set

Select database (); view the current library

Use library name; switch library

Drop database library name; delete library

Show tables; to view existing tables

Create table table name (field name 1 data type, … .); create tables

Show create table table name; view table character set

Desc table name; view table structure

Drop table table name; delete table

Insert into table name values (value 1), (value 2)... ; insert a full record

Insert into table name (field name 1, … ) values (value 1), … ; insert field data

Select * from table name [where condition]; query all fields

Select field name 1, field name 2, … From table name [where condition]; View field

Alter table table name add field name data type; add field

Alter table table name add field name data type first; header)

Alter table table name add field name data type after field name; specify insert)

Alter table table name drop field name; delete field

Alter table table name modify field name new data type; modify data type

Alter table table name rename table name; table rename

Delete from table name where condition; delete table record (must add where)

Update table name set field 1 = value 1, field name 2 = value 2, … Where condition change table record (must add where)

Alter table table name change original name new name data type; field rename

Create table table name select.. From table name where condition; copy table (do not copy key)

Create table table name select * from table name where false; replication table structure (do not copy key)

Sex enum ("M", "F", "S") not null defaulf "S" constraint

Show variables like variable name; query MySQL variable

Select field name list from table name list; (Cartesian product)

Select t1.namedirection t2.name from T1 authoring T2 where conditional multi-table query

Create index index name on table name (field name); add normal index

Create table (… .index (field name), … Create a normal index when creating a table

Drop index index name on table name; delete normal index

Show index from table name; view normal index

Create unique index index name on table name (field name); add unique index

Create table table name (… . , unique key (field name); create a unique index when the table is created

Drop unique index index name on table name; delete unique index

Show unique index from table name; view unique index

Alter table table name add primary key (field name); add primary key index

Create table table name (… . , id int, primary key (field name); create a primary key index when creating a table

(id int primary key auto_increment,) auto_increment=10000; setting since the beginning of growth

Alter table table name modify id int auto_increment; added since growth

Alter table table name auto_increment=20000; modified from the beginning of growth

Alter table table name modify id int; deleted since growth

Alter table table name drop primary key; delete primary key index

Show index from table name G; view table index

Desc table name; view table structure (key)

Non_Unique:1: index general index (query results)

Non_Unique:0: unique unique index (query results)

Alter table table name drop foreign key foreign key name; delete foreign key

Show create table table name; view foreign key name

Create a foreign key:

Create... T1 ()

Create table T2 (

...

Foreign key (reference field name)

References master table (referenced field name)

On delete cascade action

On update cascade action)

Add a foreign key:

Alter table table name add

Foreign key (reference field) references master table (referenced field)

On delete...

On update...

Cascade actions:

Restrict (default) does not allow master tables to operate on slave tables

Cascade: follow delete, update

Set null: the slave table value is NULL after the master table has been changed

Internal link:

Select Field name from Table 1

Inner join Table 2 on condition

Inner join Table 3 on conditions …

External link. Left link:

Display the query results mainly with the left table

Select Field name from Table 1

Left join Table 2 on condition

Left join Table 3 on conditions …

Right link

Display the query results mainly with the right table

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

< 目标库名 xxx.sql 从所有库备份中恢复某一个库（-one-database） mysql -uroot -p -one-database 目标库名 < xxx.sql 恢复：表不删除 表记录删除覆盖 数据备份： mysqldump -u用户 -p源库名 >

~ / xxx.sql

-all-databases backs up 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 specification table

Run time detection:

Enable: 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 … .)

2. Find out the name and attack value of the hero with the highest attack power in each country

Select name,gongji from sanguo

Where

(country,gongji) in

(select country,max (gongji) from sanguo group by country)

Where: can only manipulate fields that actually exist in the table

Group by: group query results

Having: further filter the query results

Distinct: do not display field duplicate values

Show engines; view all storage engines

Show create table table name; view the storage engine of the table

Create table table name (…) Engine=myisam; specifies the storage engine when creating the table

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

MyISAM:

MyISAM features (3 files):

Exclusive tablespace, table-level lock

.frm (structure), .myd (record), .myi (index)

Lock:

Select: after adding a read lock, others cannot change the table record, but they can query it.

Insert, delete, update: cannot be checked or changed after adding a write lock

Lock granularity:

Table-level lock: myisam

Row-level lock: innodb

Tuning:

1. Select the appropriate storage engine

two。 Index commonly used fields

Avoid using 3.where! =, NULL judgment, or links,

Like prefix%, in, not in, * substitute field,

Data type:

Data type:

Int large Integer (4 bytes) 2 blocks 32-1 (4294967295)

Tinyint Mini Integer (1 byte)

Signed (signed default):-128127

Unsigned (unsigned): 0-255

Smallint small integer (2 bytes)

Bigint Max Integer (8 bytes)

Float floating point number (4 bytes, 7 significant bits)

Field name float (m) m: total number of places n: number of decimal places

Decimal floating point number (28 significant digits)

Field name decimal (m) m: total places n: number of decimal places

Pack multiples of 9 into 4 bytes

Remainder byte

0 0

1-2 1

3-4 2

5-6 3

7-9 4

Field name enum (value 1, value 2... ); Radio (enum)

Field name set (value 1, value 2... ); multiple selection (set)

(multiple items are separated by symbols in a string)

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: returns the system time by default if the value is not given

Time function

Now () returns the current time of the server

Curdate () returns the current period

Curtime () returns the current date

Year (date) returns the year of the specified time

Date (date) returns the date of the specified time

Time (date) returns the time of the specified time

Aggregate function

Avg (field name): find the average of the field

Sum (field name): summation

Max (field name): maximum

Min (field name): minimum

Count (field name): count the number of fields

Operator: +-* /%

Time operator

Select * from table name

Where field name operator (time-interval interval unit)

Interval unit: 1 day | 2hour | 1 minute | 2year | month

Numerical comparison: =! = > > = <

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.