Python全棧介紹MySQL數(shù)據(jù)庫的示例

這篇文章給大家分享的是有關(guān)Python全棧介紹MySQL數(shù)據(jù)庫的示例的內(nèi)容。小編覺得挺實用的，因此分享給大家做個參考。一起跟隨小編過來看看吧。

主要三個方面：

1.Linux終端命令

2.MySQL語句

3.Python調(diào)用

終端命令：

vi                                                                文本編輯器

cat /etc/password | grep “用戶名”         獲取user表

sudo -i                                                       獲取root權(quán)限

sudo apt-get install python3-pip             安裝pip3

sudo pip3 install pymysql                        安裝mysql

sudo apt-get install mysql-server            安裝服務(wù)端

sudo apt-get install mysql-client             安裝客戶端

sudo apt-get update                               讀取列表 保存到 /var/lib/apt/lists

sudo apt-get upgrade                             對比下載列表并更新

sudo /etc/init.d/mysql status                  查詢狀態(tài)

sudo /etc/init.d/mysql stop                     停止服務(wù)

sudo /etc/init.d/mysql restart                 重啟服務(wù)

sudo /etc/init.d/mysql reload                 重新加載

mysql -h主機地址 -u用戶名 -p密碼          鏈接mysql

修改mysql默認(rèn)字符集：

sudo -i                                                                       1.獲取root

chmod  644 文件名                                                    2.修改文件權(quán)限

cd etc/mysql/mysql.conf.d                                        3.進入配置文目錄

cp etc/msql.cnf/mysqld.cnf.bak                                4.備份

subl mysqld.cnf                                                         5.vi打開源文件

[mysqld]  目錄

character_set_server = utf8                                       6.添加命令

/etc/init.d/mysql

mysqldump -u用戶 -p源庫名 > ~/xxx.sql            

數(shù)據(jù)備份：

參數(shù)：

–all-databases                                                   1.備份所有庫

庫名                                                                     2.備份單個庫

-B 庫1 庫2..                                                          3.備份多個庫

庫名 表1 表2…                                                     4.備份指定庫指定表

數(shù)據(jù)恢復(fù)：

mysql -uroot -p < 目標(biāo)庫名 xxx.sql                                  1. 恢復(fù)備份庫

mysql -uroot -p –one-database 目標(biāo)庫名 < xxx.sql        2. 恢復(fù)備份內(nèi)某一個庫

恢復(fù)：表不刪除 表記錄刪除覆蓋

MySQL遠程連接：

sudo -i                                                               1.管理員模式

cd /etc/mysql/mysql.conf.d/                             2.cd到mysql目錄

vi mysqld.cnf                                                     3.打開注釋掉默認(rèn)IP

#bind-address = 127.0.0.1                                4.保存

/etc/init.d/mysql restart                                    5.重啟服務(wù)

授權(quán)用戶：

grant 授權(quán)列表 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.權(quán)限列表

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模塊安裝：

模塊名 ：MySQLdb

安裝 ：sudo pip install mysql-python

sqlalchemy 框架 安裝：

在線 ：sudo pip3 install sqlalchemy

離線 ：

$ tar -zxvf SQLAlchemy-1.2.10.tar.gz

$ cd SQLAlchemy-1.2.10

$ sudo python3 setup.py install

驗證：

$ python3

>>> import sqlalchemy

>>>

pymysql使用：

from pymsql import *                                   導(dǎo)入模塊

(db = pymysql.connect(…))                               1、建立數(shù)據(jù)庫連接

c = db.cursor())                                                 2、創(chuàng)建游標(biāo)對象

c.execute(“insert ….”)                                        3、游標(biāo)方法:

db.commit()                                                      4、提交到數(shù)據(jù)庫

c.close()                                                            5、關(guān)閉游標(biāo)對象

db.close()                                                         6、斷開數(shù)據(jù)庫連接 ：

7.connect對象：

db = pymysql.connect(參數(shù)列表)

1、host ：主機地址,本地 localhost

2、port ：端口號,默認(rèn)3306

3、user ：用戶名

4、password ：密碼

5、database ：庫

6、charset ：編碼方式,推薦使用 utf8

8.連接對象的方法：

數(shù)據(jù)庫連接對象(db)的方法

1、db.close() 關(guān)閉連接

2、db.commit() 提交到數(shù)據(jù)庫執(zhí)行

3、db.rollback() 回滾

4、cur = db.cursor() 返回游標(biāo)對象,用于執(zhí)行具體SQL命令

9.游標(biāo)對象的方法：

游標(biāo)對象(cur)的方法

1、cur.execute(sql命令,[列表]) 執(zhí)行SQL命令

2、cur.close() 關(guān)閉游標(biāo)對象

3、cur.fetchone() 獲取查詢結(jié)果集的第一條數(shù)據(jù)

((記錄1),)

4、cur.fetchmany(n) 獲取n條

((記錄1),(記錄2))

5、cur.fetchall() 獲取所有記錄

ORM：orm(Object Relation Mapping 對象關(guān)系映射) 定義：把對象模型映射到MySQL數(shù)據(jù)庫中

SQL命令：

• /var/lib/mysql                                                                          MySQL數(shù)據(jù)目錄

• show variables like “autocommit”;                                          查詢commit事務(wù)

• begin；                                                                                     開啟事務(wù)

• commit；                                                                                  提交事務(wù)（MySQL默認(rèn)自動提交）

• rollback；                                                                                 終止事務(wù)

• system sudo -i                                                                          由數(shù)據(jù)直接進入終端

• show databases；                                                                    查看已有庫

• create database 庫名；                                                             創(chuàng)建庫

• create database 庫名 charcater set utf8；                               指定字符集

• show create database 庫名；                                                   查看庫字符集

• select database（）；                                                              查看當(dāng)前所在庫

• use 庫名；                                                                                切換庫

• drop database 庫名；                                                              刪除庫

• show tables；                                                                           查看已有表

• create table 表名（字段名1 數(shù)據(jù)類型，….）；                           創(chuàng)建表

• show create table 表名；                                                          查看表字符集

• desc 表名；                                                                               查看表結(jié)構(gòu)

• drop table 表名；                                                                      刪除表

• insert into 表名 values（值1），（值2）…；                             插入完整記錄

• insert into 表名 （字段名1，…） values（值1），…；               插入字段數(shù)據(jù)

• select * from 表名 [where 條件]；                                             查詢所有字段

• select 字段名1，字段名2，…from 表名[where 條件]；                查看字段

• alter table 表名 add 字段名 數(shù)據(jù)類型;                                        添加字段

• alter table 表名 add 字段名 數(shù)據(jù)類型 first；                               頭插）

• alter table 表名 add 字段名 數(shù)據(jù)類型 after 字段名；                  指定插入）

• alter table 表名 drop 字段名；                                                   刪除字段

• alter table 表名 modify 字段名 新數(shù)據(jù)類型；                             修改數(shù)據(jù)類型

• alter table 表名 rename 表名；                                                 表重命名

• delete from 表名 where 條件；                                                 刪除表記錄（必須加where）

• update 表名 set 字段1=值1，字段名2=值2，… where 條件       更改表記錄（必須加where）

• alter table 表名 change 原名 新名 數(shù)據(jù)類型；                           字段重命名

• create table 表名 select .. from 表名 where 條件;                    復(fù)制表（不復(fù)制key）

• create table 表名 select * from 表名 where false;                    復(fù)制表結(jié)構(gòu)（不復(fù)制key）

• sex enum（“M”,”F”,”S”） not null defaulf “S”                       約束

• show variables like 變量名；                                                    查詢MySQL變量

• select 字段名列表 from 表名列表;                                              (笛卡爾積)

• select t1.name,t2.name from t1,t2 where 條件                        多表查詢

• create index 索引名 on 表名(字段名);                                         添加普通索引

• create table（….index（字段名），…）                                      創(chuàng)建表時創(chuàng)建普通索引

• drop index 索引名 on 表名；                                                      刪除普通索引

• show index from 表名；                                                            查看普通索引

• create unique index 索引名 on表名（字段名）；                       添加唯一索引

• create table 表名( …. , unique key (字段名) );                             創(chuàng)建表時創(chuàng)建唯一索引

• drop unique index 索引名 on 表名；                                         刪除唯一索引

• show unique index from 表名；                                                查看唯一索引

• alter table 表名 add primary key（字段名）；                           添加主鍵索引

• create table 表名( …. , id int， primary key (字段名) );                創(chuàng)建表時創(chuàng)主鍵一索引

• (id int primary key auto_increment,)auto_increment=10000;    設(shè)置自增長起始值

• alter table 表名 modify id int auto_increment;                            添加自增長

• alter table 表名 auto_increment=20000;                                     修改自增長起始值

• alter table 表名 modify id int;                                                       刪除自增長

• alter table 表名 drop primary key;                                                刪除主鍵索引

• show index from 表名G;                                                             查看表索引

• desc 表名；                                                                                查看表結(jié)構(gòu)（key）

• Non_Unique：1   ：index                                                         普通索引（查詢結(jié)果）

• Non_Unique：0   ：unique                                                        唯一索引（查詢結(jié)果）

• alter table 表名 drop foreign key 外鍵名;                                    刪除外鍵

• show create table 表名;                                                                 查看外鍵名

創(chuàng)建外鍵：

create……t1（）；

create table t2（

…

foreign key(參考字段名)

references 主表(被參考字段名)

on delete 級聯(lián)動作

on update 級聯(lián)動作）；

添加外鍵：

alter table 表名 add

foreign key(參考字段) references 主表(被參考字段)

on delete …

on update …

級聯(lián)動作：

restrict(默認(rèn))不允許主表操作從表

cascade ：跟隨刪除、更新

set null：主表更改后從表值為NULL

內(nèi)鏈接：

select 字段名 from表1

inner join 表2 on 條件

inner join 表3 on 條件…；

外鏈接.左鏈接：

以左表為主顯示查詢結(jié)果

select 字段名 from 表1

left join 表2 on 條件

left join 表3 on 條件…；

右鏈接

以右表為主顯示查詢結(jié)果

數(shù)據(jù)導(dǎo)入：

load data  infile “文件名”

into table 表名

fields terminated by “分隔符”

lines terminated by “n”；

數(shù)據(jù)導(dǎo)出：

select … from 表名

into outfile “/var/lib/mysql-files/文件名”

fields terminated by “分隔符”

lines terminated by “n”;

數(shù)據(jù)恢復(fù)：

恢復(fù)單個庫

mysql -uroot -p < 目標(biāo)庫名 xxx.sql

從所有庫備份中恢復(fù)某一個庫（-one-database）

mysql -uroot -p –one-database 目標(biāo)庫名 < xxx.sql

恢復(fù)：表不刪除 表記錄刪除覆蓋

數(shù)據(jù)備份：

mysqldump -u用戶 -p源庫名 > ~/xxx.sql

–all-databases  備份所有庫

庫名             備份單個庫

-B 庫1 庫2..     備份多個庫

庫名 表1 表2…  備份指定庫指定表

運行時間檢測：

開啟：set profiling=1；

關(guān)閉：set profiling=0；

查詢執(zhí)行記錄：show profilings；

SQL查詢：

3.select … 聚合函數(shù) from 表名

1.where

2.group by…

4.having …

5.order by …

6.limit …；

查詢嵌套：

select … from 表名 where 條件(select ….);

2、找出每個國家攻擊力最高的英雄的名字和攻擊值

select name,gongji from sanguo

where

(country,gongji) in

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

where：只能操作表中實際存在的字段

group by：給查詢結(jié)果進行分組

having：對查詢結(jié)果進一步篩選

distinct：不顯示字段重復(fù)值

show engines;                                                                   查看所有存儲引擎

show create table 表名;                                                     查看表的存儲引擎

create table 表名(…)engine=myisam;                                創(chuàng)建表時指定存儲引擎

alter table 表名 engine=innodb;                                       添加儲存引擎

InnoDB：

InnoDB特點（2文件）：

行級鎖、支持外鍵、事務(wù)操作

.frm（表結(jié)構(gòu)，索引）、.ibd（表記錄）

MyISAM：

MyISAM特點（3文件）：

獨享表空間、表級鎖

.frm（結(jié)構(gòu)）、.myd（記錄）、.myi（索引）

鎖：

select ：加讀鎖之后別人不能更改表記錄,但可以進行查詢

insert、delete、update ：加寫鎖之后別人不能查、不能改

鎖粒度：

表級鎖 ：myisam

行級鎖 ：innodb

調(diào)優(yōu)：

1.選擇合適的存儲引擎

2.常用字段建立索引

3.where 避免使用 !=、NULL判斷、or鏈接、

like前置%、in、not in、*代替字段、

數(shù)據(jù)類型:

數(shù)據(jù)類型:

int             大整型（4個字節(jié)）  2**32 – 1（4294967295）

tinyint         微小整型（一個字節(jié)）

有符號（signed默認(rèn)）：-128 ~ 127

無符號（unsigned）：0 ~ 255

smallint        小整型（2字節(jié)）

bigint          極大整型（8字節(jié)）

float           浮點數(shù)（4個字節(jié)，7個有效位）

字段名 float（m，n） m：總位數(shù) n：小數(shù)位數(shù)

decimal         浮點數(shù)（28個有效位）

字段名 decimal（m，n）m：總位數(shù) n：小數(shù)位數(shù)

將9的倍數(shù)包裝成4個字節(jié)

余數(shù)   字節(jié)

0      0

1-2     1

3-4     2

5-6     3

7-9     4

字段名 enum（值1，值2…）；               單選（enum）

字段名 set（值1，值2…）；                多選（set）

（多項放在一個字符串內(nèi)用，號隔開）

date：“YYYY-MM-DD”

time：“HH：MM：SS”

datetime：“YYYY-MM-DD HH：MM：SS”

timestamp：“YYYY-MM-DD HH：MM：SS”

datetime：不給值默認(rèn)返回Null

timestamp：不給值默認(rèn)返回系統(tǒng)時間

時間函數(shù)

now（）                                                          返回服務(wù)器當(dāng)前的時間

curdate（）                                                    返回當(dāng)前時期

curtime（）                                                    返回當(dāng)前日期

year（date）                                                  返回指定時間的年份

date（date）                                                  返回指定時間的日期

time（date）                                                  返回指定時間的時間

聚合函數(shù)

avg（字段名）：求該字段的平均值

sum（字段名）：求和

max（字段名）：最大值

min（字段名）：最小值

count（字段名）：統(tǒng)計該字段的個數(shù)

運算符：+ – * / %

時間運算符

select * from 表名

where 字段名 運算符（時間-interval 時間間隔單位）；

時間間隔單位：1 day | 2hour | 1 minute | 2year | month

數(shù)值比較： =  !=  >  >= < <=

字符比較： =  !=

邏輯比較： and   or

范圍內(nèi)比較：

1.where 字段名 between 值1 and 值2

2.where 字段名 in（值1，值2，….）

3.where 字段名 not in （值1，值2，…）

空：where name is null

非空：where name is not null

NILL：空值，只能用is或is not取匹配

“” ： 空字符串，用 = 或  != 去匹配

模糊比較：

where 字段名 like 表達式

表達式

_ : 匹配單個字符

%：匹配0到多個字符

NULL不會被統(tǒng)計

排序：order by ASC | DESC

顯示： limit 開始顯示位置，條數(shù)

每頁顯示n條記錄，顯示第m頁：

limit（m-1）*n，n

MySQL 與 Python 交互

# mysqlpython.py

# 導(dǎo)入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):  # 創(chuàng)建數(shù)據(jù)庫鏈接函數(shù)
        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()  # 創(chuàng)建游標(biāo)對象

    def close(self):  # 創(chuàng)建斷開數(shù)據(jù)庫鏈接　關(guān)閉游標(biāo)函數(shù)
        self.cur.close()
        self.db.close()

    def zhixing(self, sql, L=[]):  # 創(chuàng)建pymysql.execute() 方法函數(shù)
        try:
            self.open()  # 鏈接數(shù)據(jù)庫
            self.cur.execute(sql, L)  # 參數(shù)化執(zhí)行ＳＱＬ命令
            self.db.commit()  # 提交數(shù)據(jù)
            print("ok")
        except Exception as e:
            self.db.rollback()  # 出錯取消提交
            print("Failed", e)
        self.close()  # 斷開數(shù)據(jù)庫鏈接　關(guān)閉游標(biāo)

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

數(shù)據(jù)庫用戶登錄

from mysqlpython import Mysqlpython
from hashlib import sha1

uname = input("請輸入用戶名:")
pwd = input("請輸入密碼:")
# 用sha1給pwd加密

s1 = sha1()  # 創(chuàng)建sha1加密對象
s1.update(pwd.encode("utf8"))  # 指定編碼
pwd2 = s1.hexdigest()  # 返回16進制加密結(jié)果

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

# 創(chuàng)建一張表 # 連接數(shù)據(jù)庫的模塊 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)

感謝各位的閱讀！關(guān)于Python全棧介紹MySQL數(shù)據(jù)庫的示例就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，讓大家可以學(xué)到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到吧！