當(dāng)前位置：首頁(yè) > 运维知识 > 数据库 >内容正文

数据库

python mysql操作_Python的MySQL操作

發(fā)布時(shí)間：2023/12/2 数据库 31 豆豆

生活随笔收集整理的這篇文章主要介紹了 python mysql操作_Python的MySQL操作小編覺(jué)得挺不錯(cuò)的,現(xiàn)在分享給大家,幫大家做個(gè)參考.

Python的DB-API,為大多數(shù)的數(shù)據(jù)庫(kù)實(shí)現(xiàn)了接口,使用它連接各數(shù)據(jù)庫(kù)后,就可以用相同的方式操作各數(shù)據(jù)庫(kù)。

Python DB-API使用流程:

引入API模塊。

獲取與數(shù)據(jù)庫(kù)的連接。

執(zhí)行SQL語(yǔ)句和存儲(chǔ)過(guò)程。

關(guān)閉數(shù)據(jù)庫(kù)連接。

一、安裝MySQL客戶端

MySQLdb 是用于Python鏈接Mysql數(shù)據(jù)庫(kù)的接口，它實(shí)現(xiàn)了 Python 數(shù)據(jù)庫(kù) API 規(guī)范 V2.0，基于 MySQL C API 上建立的。

如果是windows系統(tǒng)：登錄https://pypi.python.org/pypi/MySQL-python/1.2.5找到.exe結(jié)尾的包，下載安裝就好了，然后在cmd中執(zhí)行：

如果結(jié)果如上圖所示，則說(shuō)明安裝成功了。

如果是Linux系統(tǒng)，可以下載源碼包進(jìn)行安裝，https://pypi.python.org/pypi/MySQL-python/1.2.5中下載zip包，然后安裝：

yum install –y python-devel

yum install –y mysql-devel

yum install –y gcc

unzip MySQL-python-1.2.5.zip

cd MySQL-python-1.2.5python setup.py build

python setup.py install

python>>> import MySQLdb

二、數(shù)據(jù)庫(kù)連接

MySQLdb提供了connect方法用來(lái)和數(shù)據(jù)庫(kù)建立連接,接收數(shù)個(gè)參數(shù),返回連接對(duì)象：代碼如下:

首先在mysql的數(shù)據(jù)庫(kù)中建立python庫(kù)

create database python;

conn=MySQLdb.connect(host="192.168.203.12",user="momo",passwd="123456",db="python",charset="utf8")

比較常用的參數(shù)包括:

host:數(shù)據(jù)庫(kù)主機(jī)名.默認(rèn)是用本地主機(jī)

user:數(shù)據(jù)庫(kù)登陸名.默認(rèn)是當(dāng)前用戶

passwd:數(shù)據(jù)庫(kù)登陸的秘密.默認(rèn)為空

db:要使用的數(shù)據(jù)庫(kù)名.沒(méi)有默認(rèn)值

port:MySQL服務(wù)使用的TCP端口.默認(rèn)是3306，數(shù)字類型

charset:數(shù)據(jù)庫(kù)編碼

推薦把所有數(shù)據(jù)庫(kù)的配置寫(xiě)在一個(gè)字典中，如下所示：

defconnect_mysql():

db_config={'host': '192.168.203.12','port': 3306,'user': 'momo','passwd': '123456','db': 'python','charset': 'utf8'}

cnx= MySQLdb.connect(**db_config)return cnx

三、MySQL事物

MySQL 事務(wù)主要用于處理操作量大，復(fù)雜度高的數(shù)據(jù)。比如，你操作一個(gè)數(shù)據(jù)庫(kù)，公司的一個(gè)員工離職了，你要在數(shù)據(jù)庫(kù)中刪除他的資料，也要?jiǎng)h除該人員相關(guān)的，比如郵箱，個(gè)人資產(chǎn)等。這些數(shù)據(jù)庫(kù)操作語(yǔ)言就構(gòu)成了一個(gè)事務(wù)。

在MySQL中只有使用了Innodb數(shù)據(jù)庫(kù)引擎的數(shù)據(jù)庫(kù)或表才支持事務(wù)，所以很多情況下我們都使用innodb引擎。

事務(wù)處理可以用來(lái)維護(hù)數(shù)據(jù)庫(kù)的完整性，保證成批的SQL語(yǔ)句要么全部執(zhí)行，要么全部不執(zhí)行。

一般來(lái)說(shuō)，事務(wù)是必須滿足4個(gè)條件(ACID)： Atomicity(原子性)、Consistency(穩(wěn)定性)、Isolation(隔離性)、Durability(可靠性)

1、事務(wù)的原子性：一組事務(wù)，要么成功；要么撤回。

2、穩(wěn)定性：有非法數(shù)據(jù)(外鍵約束之類)，事務(wù)撤回。

3、隔離性：事務(wù)獨(dú)立運(yùn)行。一個(gè)事務(wù)處理后的結(jié)果，影響了其他事務(wù)，那么其他事務(wù)會(huì)撤回。事務(wù)的100%隔離，需要犧牲速度。

4、可靠性：軟、硬件崩潰后，InnoDB數(shù)據(jù)表驅(qū)動(dòng)會(huì)利用日志文件重構(gòu)修改。可靠性和高速度不可兼得， innodb_flush_log_at_trx_commit選項(xiàng) 決定什么時(shí)候吧事務(wù)保存到日志里。

而mysql在默認(rèn)的情況下，他是把每個(gè)select，insert，update，delete等做為一個(gè)事務(wù)的，登錄mysql服務(wù)器，進(jìn)入mysql，執(zhí)行以下命令：

mysql> show variables like 'auto%';+--------------------------+-------+

| Variable_name | Value |

+--------------------------+-------+

| auto_increment_increment | 1 |

| auto_increment_offset | 1 |

| autocommit | ON |

| automatic_sp_privileges | ON |

+--------------------------+-------+

4 rows in set (0.00 sec)

如上所示：有一個(gè)參數(shù)autocommit就是自動(dòng)提交的意思，每執(zhí)行一個(gè)msyql的select，insert，update等操作，就會(huì)進(jìn)行自動(dòng)提交。

如果把改選項(xiàng)關(guān)閉，我們就可以每次執(zhí)行完一次代碼就需要進(jìn)行手動(dòng)提交，connect對(duì)象給我們提供了兩種辦法來(lái)操作提交數(shù)據(jù)。

a)??????? mysql事務(wù)的方法

commit():提交當(dāng)前事務(wù)，如果是支持事務(wù)的數(shù)據(jù)庫(kù)執(zhí)行增刪改后沒(méi)有commit則數(shù)據(jù)庫(kù)默認(rèn)回滾，白操作了

rollback():取消當(dāng)前事務(wù)

下面我們來(lái)看個(gè)例子：

我們先創(chuàng)建一個(gè)員工表：

create table employees (

emp_no intnotnull auto_increment,

emp_name varchar(16) notnull,

gender enum('M', 'F') notnull,

hire_date datenotnull,

primary key (emp_no)

);

其中，emp_no為員工id，為主鍵且唯一

emp_name為：員工的名字

fender為：性別，只有M和F兩種選擇

hire_date為：雇傭的時(shí)間。

為了試驗(yàn)的效果，我們插入幾條數(shù)據(jù)：

insert into employees(emp_no, emp_name, gender, hire_date) values(1001, 'lingjiang', 'M', '2015-04-01');

insert into employees(emp_no, emp_name, gender, hire_date) values(1002, 'xiang', 'M', '2015-04-01');

insert into employees(emp_no, emp_name, gender, hire_date) values(1003, 'shang', 'M', '2015-04-01');

mysql> select * fromemployees;+--------+-----------+--------+------------+

+--------+-----------+--------+------------+

| 1001 | lingjiang | M | 2015-04-01 |

| 1002 | xiang | M | 2015-04-01 |

| 1003 | shang | M | 2015-04-01 |

+--------+-----------+--------+------------+e) rowsin set (0.00 sec)

四、MySQL游標(biāo)

游標(biāo)(cursor)

游標(biāo)是系統(tǒng)為用戶開(kāi)設(shè)的一個(gè)數(shù)據(jù)緩沖區(qū)，存放SQL語(yǔ)句的執(zhí)行結(jié)果，用戶可以用SQL語(yǔ)句逐一從游標(biāo)中獲取記錄，并賦給主變量，交由python進(jìn)一步處理，一組主變量一次只能存放一條記錄，僅使用主變量并不能完全滿足SQL語(yǔ)句向應(yīng)用程序輸出數(shù)據(jù)的要求。

游標(biāo)和游標(biāo)的優(yōu)點(diǎn)：在數(shù)據(jù)庫(kù)中，游標(biāo)是一個(gè)十分重要的概念。游標(biāo)提供了一種對(duì)從表中檢索出的數(shù)據(jù)進(jìn)行操作的靈活手段，就本質(zhì)而言，游標(biāo)實(shí)際上是一種能從包括多條數(shù)據(jù)記錄的結(jié)果集中每次提取一條記錄的機(jī)制。游標(biāo)總是與一條SQL 選擇語(yǔ)句相關(guān)聯(lián)因?yàn)橛螛?biāo)由結(jié)果集(可以是零條、一條或由相關(guān)的選擇語(yǔ)句檢索出的多條記錄)和結(jié)果集中指向特定記錄的游標(biāo)位置組成。當(dāng)決定對(duì)結(jié)果集進(jìn)行處理時(shí)，必須聲明一個(gè)指向該結(jié)果集的游標(biāo)。

常用方法:

cursor():創(chuàng)建游標(biāo)對(duì)象

close():關(guān)閉此游標(biāo)對(duì)象

fetchone():得到結(jié)果集的下一行

fetchmany([size = cursor.arraysize]):得到結(jié)果集的下幾行

fetchall():得到結(jié)果集中剩下的所有行

excute(sql[, args]):執(zhí)行一個(gè)數(shù)據(jù)庫(kù)查詢或命令

executemany (sql, args):執(zhí)行多個(gè)數(shù)據(jù)庫(kù)查詢或命令

程序例子：

1.創(chuàng)建游標(biāo)對(duì)象

import MySQLdb

db_config={'host': '192.168.203.12','port': 3306,'user': 'momo','passwd': '123456','db': 'python','charset': 'utf8'}

cnx= MySQLdb.connect(**db_config)

cus= cnx.cursor()

2.對(duì)游標(biāo)的基本操作

importMySQLdbdefconnect_mysql():

db_config={'host': '192.168.203.12','port': 3306,'user': 'momo','passwd': '123456','db': 'python','charset': 'utf8'}

cnx= MySQLdb.connect(**db_config)returncnxif __name__ == '__main__':

cnx=connect_mysql()

cus=cnx.cursor()

sql= '''select * from employees;'''

try:

cus.execute(sql)

result1=cus.fetchone()print('result1:')print(result1)

result2= cus.fetchmany(1)print('result2:')print(result2)

result3=cus.fetchall()print('result3:')print(result3) cus.close()

cnx.commit()exceptException as e:

cnx.rollback()print('error')raiseefinally:

cnx.close()

結(jié)果：

result1:

(1001L, u'lingjiang', u'M', datetime.date(2015, 4, 1))

result2:

((1002L, u'xiang', u'M', datetime.date(2015, 4, 1)),)

result3:

((1003L, u'shang', u'M', datetime.date(2015, 4, 1)),)

解釋：

先通過(guò)MySQLdb.connect(**db_config)建立mysql連接對(duì)象

在通過(guò) = cnx.cursor()創(chuàng)建游標(biāo)

fetchone():在最終搜索的數(shù)據(jù)中去一條數(shù)據(jù)

fetchmany(1)在接下來(lái)的數(shù)據(jù)中在去1行的數(shù)據(jù)，這個(gè)數(shù)字可以自定義，定義多少就是在結(jié)果集中取多少條數(shù)據(jù)。

fetchall()是在所有的結(jié)果中搞出來(lái)所有的數(shù)據(jù)。

3.執(zhí)行多行SQL語(yǔ)句

#!/usr/bin/env python#-*- coding:utf-8 -*-#@Time : 2017/9/18 22:17#@Author : lingxiangxiang#@File : domon3.py

from demon2 importconnect_mysqlimportMySQLdbdefconnect_mysql():

db_config={"host": "192.168.203.12","port": 3306,"user": "momo","passwd": "123456","db": "python","charset": "utf8"}try:

cnx= MySQLdb.connect(**db_config)exceptException as e:raiseereturncnxif __name__ == "__main__":

sql= "select * from tmp;"sql1= "insert into tmp(id) value (%s);"param=[]for i in xrange(100, 130):

param.append([str(i)])print(param)

cnx=connect_mysql()

cus=cnx.cursor()print(dir(cus))try:

cus.execute(sql)

cus.executemany(sql1, param)#help(cus.executemany)

result1 =cus.fetchone()print("result1")print(result1)

result2= cus.fetchmany(3)print("result2")print(result2)

result3=cus.fetchall()print("result3")print(result3)

cus.close()

cnx.commit()exceptException as e:

cnx.rollback()raiseefinally:

cnx.close()

五、數(shù)據(jù)庫(kù)連接池

python編程中可以使用MySQLdb進(jìn)行數(shù)據(jù)庫(kù)的連接及諸如查詢/插入/更新等操作，但是每次連接mysql數(shù)據(jù)庫(kù)請(qǐng)求時(shí)，都是獨(dú)立的去請(qǐng)求訪問(wèn)，相當(dāng)浪費(fèi)資源，而且

訪問(wèn)數(shù)量達(dá)到一定數(shù)量時(shí)，對(duì)mysql的性能會(huì)產(chǎn)生較大的影響。因此，實(shí)際使用中，通常會(huì)使用數(shù)據(jù)庫(kù)的連接池技術(shù)，來(lái)訪問(wèn)數(shù)據(jù)庫(kù)達(dá)到資源復(fù)用的目的。

python的數(shù)據(jù)庫(kù)連接池包 DBUtils：

DBUtils是一套Python數(shù)據(jù)庫(kù)連接池包，并允許對(duì)非線程安全的數(shù)據(jù)庫(kù)接口進(jìn)行線程安全包裝。DBUtils來(lái)自Webware for Python。

DBUtils提供兩種外部接口：

* PersistentDB ：提供線程專用的數(shù)據(jù)庫(kù)連接，并自動(dòng)管理連接。

* PooledDB ：提供線程間可共享的數(shù)據(jù)庫(kù)連接，并自動(dòng)管理連接。

PooledDB的參數(shù)：

1. mincached，最少的空閑連接數(shù)，如果空閑連接數(shù)小于這個(gè)數(shù)，pool會(huì)創(chuàng)建一個(gè)新的連接

2. maxcached，最大的空閑連接數(shù)，如果空閑連接數(shù)大于這個(gè)數(shù)，pool會(huì)關(guān)閉空閑連接

3. maxconnections，最大的連接數(shù)，

4. blocking，當(dāng)連接數(shù)達(dá)到最大的連接數(shù)時(shí)，在請(qǐng)求連接的時(shí)候，如果這個(gè)值是True，

請(qǐng)求連接的程序會(huì)一直等待，直到當(dāng)前連接數(shù)小于最大連接數(shù)，如果這個(gè)值是False，會(huì)報(bào)錯(cuò)，

5. maxshared 當(dāng)連接數(shù)達(dá)到這個(gè)數(shù)，新請(qǐng)求的連接會(huì)分享已經(jīng)分配出去的連接

在uwsgi中，每個(gè)http請(qǐng)求都會(huì)分發(fā)給一個(gè)進(jìn)程，連接池中配置的連接數(shù)都是一個(gè)進(jìn)程為單位的(即上面的最大連接數(shù)，都是在一個(gè)進(jìn)程中的連接數(shù))，

而如果業(yè)務(wù)中，一個(gè)http請(qǐng)求中需要的sql連接數(shù)不是很多的話(其實(shí)大多數(shù)都只需要?jiǎng)?chuàng)建一個(gè)連接)，配置的連接數(shù)配置都不需要太大。

連接池對(duì)性能的提升表現(xiàn)在：

1.在程序創(chuàng)建連接的時(shí)候，可以從一個(gè)空閑的連接中獲取，不需要重新初始化連接，提升獲取連接的速度

2.關(guān)閉連接的時(shí)候，把連接放回連接池，而不是真正的關(guān)閉，所以可以減少頻繁地打開(kāi)和關(guān)閉連接

六、數(shù)據(jù)庫(kù)的操作

1.建表

各個(gè)表的結(jié)構(gòu)如下：

student表：

字段名

類型

是否為空

主鍵

描述

StdID

int

否

是

學(xué)生ID

StdName

varchar(100)

否

學(xué)生姓名

Gender

enum('M', 'F')

是

性別

Age

tinyint

是

年齡

course表：

字段名

類型

是否為空

主鍵

描述

CouID

int

否

是

課程ID

Cname

varchar(50)

否

課程名字

TID

int

否

老師ID

Score表：

字段名

類型

是否為空

主鍵

描述

SID

int

否

是

分?jǐn)?shù)ID

StdID

int

否

學(xué)生id

CouID

int

否

課程id

Grade

int

否

分?jǐn)?shù)

teacher表：

字段名

類型

是否為空

主鍵

描述

TID

int

否

是

老師ID

Tname

varcher(100)

否

老師名字

在Linux中MySQL建立student表，然后在Python代碼中執(zhí)行：

importMySQLdbdefconnect_mysql():

db_config={'host': '192.168.203.12','port': 3306,'user': 'momo','passwd': '123456','db': 'student','charset': 'utf8'}

cnx= MySQLdb.connect(**db_config)returncnxif __name__ == '__main__':

cnx=connect_mysql()

cus=cnx.cursor()#sql = '''insert into student(id, name, age, gender, score) values ('1001', 'ling', 29, 'M', 88), ('1002', 'ajing', 29, 'M', 90), ('1003', 'xiang', 33, 'M', 87);'''

student = '''create table Student(

StdID int not null,

StdName varchar(100) not null,

Gender enum('M', 'F'),

Age tinyint

)'''course= '''create table Course(

CouID int not null,

CName varchar(50) not null,

TID int not null

)'''score= '''create table Score(

SID int not null,

StdID int not null,

CID int not null,

Grade int not null

)'''teacher= '''create table Teacher(

TID int not null,

TName varchar(100) not null

)'''tmp= '''set @i := 0;

create table tmp as select (@i := @i + 1) as id from information_schema.tables limit 10;'''

try:

cus.execute(student)

cus.execute(course)

cus.execute(score)

cus.execute(thearch)

cus.execute(tmp)

cus.close()

cnx.commit()exceptException as e:

cnx.rollback()print('error')raiseefinally:

cnx.close()

結(jié)果：

mysql> show tables;

+------------------+

| Tables_in_python |

+------------------+

| Course?????????? |

| Score??????????? |

| Student????????? |

| Teacher????????? |

| tmp????????????? |

+------------------+

1??????? rows in set (0.00 sec)

information_schema數(shù)據(jù)庫(kù)表說(shuō)明:

SCHEMATA表：提供了當(dāng)前mysql實(shí)例中所有數(shù)據(jù)庫(kù)的信息。是show databases的結(jié)果取之此表。

TABLES表：提供了關(guān)于數(shù)據(jù)庫(kù)中的表的信息(包括視圖)。詳細(xì)表述了某個(gè)表屬于哪個(gè)schema，表類型，表引擎，創(chuàng)建時(shí)間等信息。是show tables from schemaname的結(jié)果取之此表。

COLUMNS表：提供了表中的列信息。詳細(xì)表述了某張表的所有列以及每個(gè)列的信息。是show columns from schemaname.tablename的結(jié)果取之此表。

STATISTICS表：提供了關(guān)于表索引的信息。是show index from schemaname.tablename的結(jié)果取之此表。

USER_PRIVILEGES(用戶權(quán)限)表：給出了關(guān)于全程權(quán)限的信息。該信息源自mysql.user授權(quán)表。是非標(biāo)準(zhǔn)表。

SCHEMA_PRIVILEGES(方案權(quán)限)表：給出了關(guān)于方案(數(shù)據(jù)庫(kù))權(quán)限的信息。該信息來(lái)自mysql.db授權(quán)表。是非標(biāo)準(zhǔn)表。

TABLE_PRIVILEGES(表權(quán)限)表：給出了關(guān)于表權(quán)限的信息。該信息源自mysql.tables_priv授權(quán)表。是非標(biāo)準(zhǔn)表。

COLUMN_PRIVILEGES(列權(quán)限)表：給出了關(guān)于列權(quán)限的信息。該信息源自mysql.columns_priv授權(quán)表。是非標(biāo)準(zhǔn)表。

CHARACTER_SETS(字符集)表：提供了mysql實(shí)例可用字符集的信息。是SHOW CHARACTER SET結(jié)果集取之此表。

COLLATIONS表：提供了關(guān)于各字符集的對(duì)照信息。

COLLATION_CHARACTER_SET_APPLICABILITY表：指明了可用于校對(duì)的字符集。這些列等效于SHOW COLLATION的前兩個(gè)顯示字段。

TABLE_CONSTRAINTS表：描述了存在約束的表。以及表的約束類型。

KEY_COLUMN_USAGE表：描述了具有約束的鍵列。

ROUTINES表：提供了關(guān)于存儲(chǔ)子程序(存儲(chǔ)程序和函數(shù))的信息。此時(shí)，ROUTINES表不包含自定義函數(shù)(UDF)。名為“mysql.proc name”的列指明了對(duì)應(yīng)于INFORMATION_SCHEMA.ROUTINES表的mysql.proc表列。

VIEWS表：給出了關(guān)于數(shù)據(jù)庫(kù)中的視圖的信息。需要有show views權(quán)限，否則無(wú)法查看視圖信息。

TRIGGERS表：提供了關(guān)于觸發(fā)程序的信息。必須有super權(quán)限才能查看該表

而TABLES在安裝好mysql的時(shí)候，一定是有數(shù)據(jù)的，因?yàn)樵诔跏蓟痬ysql的時(shí)候，就需要?jiǎng)?chuàng)建系統(tǒng)表，該表一定有數(shù)據(jù)。

set?@i?:=?0;

create?table?tmp?as?select?(@i?:=?@i?+?1)?as?id?from?information_schema.tables?limit?10;

mysql中變量不用事前申明，在用的時(shí)候直接用“@變量名”使用就可以了。set這個(gè)是mysql中設(shè)置變量的特殊用法，當(dāng)@i需要在select中使用的時(shí)候，必須加:，這樣就創(chuàng)建好了一個(gè)表tmp，查看tmp的數(shù)據(jù)：

mysql> select * from tmp;

+------+

| id

+------+

1 |

2 |

3 |

4 |

5 |

6 |

7 |

8 |

9 |

10 |

+------+

10 rows in set (0.00 sec)

我們只是從information_schema.tables表中取10條數(shù)據(jù)，任何表有10條數(shù)據(jù)也是可以的，然后把變量@i作為id列的值，分10次不斷輸出，依據(jù)最后select的結(jié)果，創(chuàng)建表tmp。

2.增加數(shù)據(jù)

substr是一個(gè)字符串函數(shù)，從第二個(gè)參數(shù)1,開(kāi)始取字符，取到3+ floor(rand() * 75)結(jié)束

floor函數(shù)代表的是去尾法取整數(shù)。

rand()函數(shù)代表的是從0到1取一個(gè)隨機(jī)的小數(shù)。

rand() * 75就代表的是：0到75任何一個(gè)小數(shù)，

3+floor(rand() * 75)就代表的是：3到77的任意一個(gè)數(shù)字

concat()函數(shù)是一個(gè)對(duì)多個(gè)字符串拼接函數(shù)。

sha1是一個(gè)加密函數(shù)，sha1(rand())對(duì)生成的0到1的一個(gè)隨機(jī)小數(shù)進(jìn)行加密，轉(zhuǎn)換成字符串的形式。

concat(sha1(rand()), sha1(rand()))就代表的是：兩個(gè)0-1生成的小數(shù)加密然后進(jìn)行拼接。

substr(concat(sha1(rand()), sha1(rand())), 1, floor(rand() * 80))就代表的是：從一個(gè)隨機(jī)生成的一個(gè)字符串的第一位開(kāi)始取，取到(隨機(jī)3-77)位結(jié)束。

Gender字段：case floor(rand()*10) mod 2 when 1 then 'M' else 'F' end,就代表的是，

floor(rand()*10)代表0-9隨機(jī)取一個(gè)數(shù)

floor(rand()*10) mod 2 就是對(duì)0-9取得的隨機(jī)數(shù)除以2的余數(shù)，

case floor(rand()*10) mod 2 when 1 then 'M' else 'F' end,代表：當(dāng)余數(shù)為1是，就取M，其他的為F

Age字段：25-floor(rand() * 5)代表的就是，25減去一個(gè)0-4的一個(gè)整數(shù)

代碼如下：

importMySQLdbdefconnect_mysql():

db_config={'host': '192.168.203.12','port': 3306,'user': 'momo','passwd': '123456','db': 'student','charset': 'utf8'}

cnx= MySQLdb.connect(**db_config)returncnxif __name__ == '__main__':

cnx=connect_mysql()

students= '''set @i := 10000;

insert into Student select @i:=@i+1, substr(concat(sha1(rand()), sha1(rand())), 1, 3 + floor(rand() * 75)), case floor(rand()*10) mod 2 when 1 then 'M' else 'F' end, 25-floor(rand() * 5) from tmp a, tmp b, tmp c, tmp d;'''course= '''set @i := 10;

insert into Course select @i:=@i+1, substr(concat(sha1(rand()), sha1(rand())), 1, 5 + floor(rand() * 40)), 1 + floor(rand() * 100) from tmp a;'''score= '''set @i := 10000;

insert into Score select @i := @i +1, floor(10001 + rand()*10000), floor(11 + rand()*10), floor(1+rand()*100) from tmp a, tmp b, tmp c, tmp d;'''theacher= '''set @i := 100;

insert into Teacher select @i:=@i+1, substr(concat(sha1(rand()), sha1(rand())), 1, 5 + floor(rand() * 80)) from tmp a, tmp b;'''

try:

cus_students=cnx.cursor()

cus_students.execute(students)

cus_students.close()

cus_course=cnx.cursor()

cus_course.execute(course)

cus_course.close()

cus_score=cnx.cursor()

cus_score.execute(score)

cus_score.close()

cus_teacher=cnx.cursor()

cus_teacher.execute(theacher)

cus_teacher.close()

cnx.commit()exceptException as e:

cnx.rollback()print('error')raiseefinally:

cnx.close()

3.查數(shù)據(jù)

在數(shù)據(jù)庫(kù)中查出來(lái)所有名字有重復(fù)的同學(xué)的所有信息，然后寫(xiě)入到文件中，代碼如下：

importcodecsimportMySQLdbdefconnect_mysql():

db_config={'host': '192.168.203.12','port': 3306,'user': 'momo','passwd': '123456','db': 'student','charset': 'utf8'}

cnx= MySQLdb.connect(**db_config)returncnxif __name__ == '__main__':

cnx=connect_mysql()

sql= '''select * from Student where StdName in (select StdName from Student group by StdName having count(1)>1 ) order by StdName;'''

try:

cus=cnx.cursor()

cus.execute(sql)

result=cus.fetchall()

with codecs.open('select.txt', 'w+') as f:for line inresult:

f.write(str(line))

f.write('\n')

cus.close()

cnx.commit()exceptException as e:

cnx.rollback()print('error')raiseefinally:

cnx.close()

解釋:

我們先來(lái)分析一下select查詢這個(gè)語(yǔ)句：select * from Student where StdName in (select StdName from Student group by StdName having count(1)>1 ) order by StdName;'

我們先來(lái)看括號(hào)里面的語(yǔ)句：select StdName from Student group by StdName having count(1)>1；這個(gè)是把所有學(xué)生名字重復(fù)的學(xué)生都列出來(lái)，

最外面select是套了一個(gè)子查詢，學(xué)生名字是在我們()里面的查出來(lái)的學(xué)生名字，把這些學(xué)生的所有信息都列出來(lái)。

result = cus.fetchall()列出結(jié)果以后，我們通過(guò)fetchall()函數(shù)把所有的內(nèi)容都取出來(lái)，這個(gè)result是一個(gè)tuple

通過(guò)文件寫(xiě)入的方式，我們把取出來(lái)的result寫(xiě)入到select.txt文件中。得到最終的結(jié)果

4.刪除數(shù)據(jù)

刪除課程成績(jī)最差的5名老師，刪除之前要先進(jìn)行查詢。代碼如下：

importcodecsimportMySQLdbdefconnect_mysql():

db_config={'host': '192.168.203.12','port': 3306,'user': 'momo','passwd': '123456','db': 'student','charset': 'utf8'}

cnx= MySQLdb.connect(**db_config)returncnxif __name__ == '__main__':

cnx=connect_mysql()

sql= '''delete from Teacher where TID in(

select TID from (select Course.CouID, Course.TID, Teacher.TName, count(Teacher.TID) as count_teacher from Course

left join Score on Score.Grade < 60 and Course.CouID = Score.CouID

left join Teacher on Course.TID = Teacher.TID

group by Course.TID

order by count_teacher desc

limit 5) as test )'''

try:

cus=cnx.cursor()

cus.execute(sql)

result=cus.fetchall()

cus.close()

cnx.commit()exceptException as e:

cnx.rollback()print('error')raiseefinally:

cnx.close()

解釋：

先查詢出Course表中的Course.TID和Course.TID

left join 是關(guān)聯(lián)Score表，查出Score.Grade > 59，并且，課程ID和課程表的CouID要對(duì)應(yīng)上

left join Teacher 是關(guān)聯(lián)老師表，課程中的了老師ID和老師表中的老師ID對(duì)應(yīng)上

select中加上老師的名字Teacher.Tname和count(Teacher.TID)

group by Course.TID，在根據(jù)老師的的TID進(jìn)行分組

oder by 最后對(duì)count_teacher進(jìn)行排序，取前5行，

在通過(guò)套用一個(gè)select子查詢，把所有的TID摟出來(lái)

然后delete from Teacher 最后刪除TID在上表中的子查詢中

5.修改數(shù)據(jù)

把分?jǐn)?shù)低于5分的成績(jī)加上60分，代碼如下：

importcodecsimportMySQLdbdefconnect_mysql():

db_config={'host': '192.168.203.12','port': 3306,'user': 'momo','passwd': '123456','db': 'student','charset': 'utf8'}

cnx= MySQLdb.connect(**db_config)returncnxif __name__ == '__main__':

cnx=connect_mysql()

sql= '''select *, (grade+60) as newGrade from Score where Grade <5;'''update= '''update Score set grade = grade + 60 where grade < 5;'''

try:

cus_start=cnx.cursor()

cus_start.execute(sql)

result1=cus_start.fetchall()print(len(result1))

cus_start.close()

cus_update=cnx.cursor()

cus_update.execute(update)

cus_update.close()

cus_end=cnx.cursor()

cus_end.execute(sql)

result2=cus_end.fetchall()print(len(result2))

cus_end.close()

cnx.commit()exceptException as e:

cnx.rollback()print('error')raiseefinally:

cnx.close()

解釋：

剛開(kāi)始，我們可以查到分?jǐn)?shù)小于5分的總個(gè)數(shù)有321個(gè)

select *, (grade+60) as newGrade from Score where Grade <5;這個(gè)sql是把所有的成績(jī)小于5的都列出來(lái)，然后最后加一列分?jǐn)?shù)加60分的結(jié)果。

update Score set grade = grade + 60 where grade < 5;是把分?jǐn)?shù)小于5的所有成績(jī)都加60分

最后在檢查分?jǐn)?shù)小于5的個(gè)數(shù)為0，說(shuō)明所有低于5分的分?jǐn)?shù)都發(fā)生了改變。

6.索引

MySQL索引的概念

索引是一種特殊的文件(InnoDB數(shù)據(jù)表上的索引是表空間的一個(gè)組成部分)，它們包含著對(duì)數(shù)據(jù)表里所有記錄的引用指針。更通俗的說(shuō)，數(shù)據(jù)庫(kù)索引好比是一本書(shū)前面的目錄，能加快數(shù)據(jù)庫(kù)的查詢速度。

索引類別

普通索引

普通索引(由關(guān)鍵字 KEY 或 INDEX 定義的索引)的唯一任務(wù)是加快對(duì)數(shù)據(jù)的訪問(wèn)速度。因此，應(yīng)該只為那些最經(jīng)常出現(xiàn)在查詢條件(WHERE column =)或排序條件(ORDER BY column)中的數(shù)據(jù)列創(chuàng)建索引。只要有可能，就應(yīng)該選擇一個(gè)數(shù)據(jù)最整齊、最緊湊的數(shù)據(jù)列(如一個(gè)整數(shù)類型的數(shù)據(jù)列)來(lái)創(chuàng)建索引。

唯一索引

普通索引允許被索引的數(shù)據(jù)列包含重復(fù)的值。比如說(shuō)，因?yàn)槿擞锌赡芡?#xff0c;所以同一個(gè)姓名在同一個(gè)“員工個(gè)人資料”數(shù)據(jù)表里可能出現(xiàn)兩次或更多次。

如果能確定某個(gè)數(shù)據(jù)列將只包含彼此各不相同的值，在為這個(gè)數(shù)據(jù)列創(chuàng)建索引的時(shí)候就應(yīng)該用關(guān)鍵字UNIQUE 把它定義為一個(gè)唯一索引。這么做的好處：一是簡(jiǎn)化了 MySQL 對(duì)這個(gè)索引的管理工作，這個(gè)索引也因此而變得更有效率；二是 MySQL 會(huì)在有新記錄插入數(shù)據(jù)表時(shí)，自動(dòng)檢查新記錄的這個(gè)字段的值是否已經(jīng)在某個(gè)記錄的這個(gè)字段里出現(xiàn)過(guò)了；如果是，MySQL 將拒絕插入那條新記錄。也就是說(shuō)，唯一索引可以保證數(shù)據(jù)記錄的唯一性。事實(shí)上，在許多場(chǎng)合，人們創(chuàng)建唯一索引的目的往往不是為了提高訪問(wèn)速度，而只是為了避免數(shù)據(jù)出現(xiàn)重復(fù)。

主索引

在前面已經(jīng)反復(fù)多次強(qiáng)調(diào)過(guò)：必須為主鍵字段創(chuàng)建一個(gè)索引，這個(gè)索引就是所謂的“主索引”。主索引與唯一索引的唯一區(qū)別是：前者在定義時(shí)使用的關(guān)鍵字是 PRIMARY 而不是 UNIQUE。

外鍵索引

如果為某個(gè)外鍵字段定義了一個(gè)外鍵約束條件，MySQL 就會(huì)定義一個(gè)內(nèi)部索引來(lái)幫助自己以最有效率的方式去管理和使用外鍵約束條件。

復(fù)合索引

索引可以覆蓋多個(gè)數(shù)據(jù)列，如像 INDEX (columnA, columnB) 索引。這種索引的特點(diǎn)是 MySQL 可以有選擇地使用一個(gè)這樣的索引。如果查詢操作只需要用到 columnA 數(shù)據(jù)列上的一個(gè)索引，就可以使用復(fù)合索引 INDEX(columnA, columnB)。不過(guò)，這種用法僅適用于在復(fù)合索引中排列在前的數(shù)據(jù)列組合。比如說(shuō)，INDEX (A，B，C) 可以當(dāng)做 A 或 (A,B) 的索引來(lái)使用，但不能當(dāng)做 B、C 或 (B,C) 的索引來(lái)使用。

mysql主鍵和索引的區(qū)別:

主鍵一定是唯一性索引，唯一性索引并不一定就是主鍵。

所謂主鍵就是能夠唯一標(biāo)識(shí)表中某一行的屬性或?qū)傩越M，一個(gè)表只能有一個(gè)主鍵，但可以有多個(gè)候選索引。因?yàn)橹麈I可以唯一標(biāo)識(shí)某一行記錄，所以可以確保執(zhí)行數(shù)據(jù)更新、刪除的時(shí)候不會(huì)出現(xiàn)張冠李戴的錯(cuò)誤。主鍵除了上述作用外，常常與外鍵構(gòu)成參照完整性約束，防止出現(xiàn)數(shù)據(jù)不一致。數(shù)據(jù)庫(kù)在設(shè)計(jì)時(shí)，主鍵起到了很重要的作用。主鍵可以保證記錄的唯一和主鍵域非空，數(shù)據(jù)庫(kù)管理系統(tǒng)對(duì)于主鍵自動(dòng)生成唯一索引，所以主鍵也是一個(gè)特殊的索引。

一個(gè)表中可以有多個(gè)唯一性索引，但只能有一個(gè)主鍵。

主鍵列不允許空值，而唯一性索引列允許空值。

索引可以提高查詢的速度。

創(chuàng)建Course的CouID的字段為主鍵?? Score的SID字段為主鍵??? Student的StdID字段為主鍵? Teacher的TID字段為主鍵，代碼如下：

importcodecsimportMySQLdbdefconnect_mysql():

db_config={'host': '192.168.203.12','port': 3306,'user': 'momo','passwd': '123456','db': 'student','charset': 'utf8'}

cnx= MySQLdb.connect(**db_config)returncnxif __name__ == '__main__':

cnx=connect_mysql()

sql1= '''alter table Teacher add primary key(TID);'''sql2= '''alter table Student add primary key(StdID);'''sql3= '''alter table Score add primary key(SID);'''sql4= '''alter table Course add primary key(CouID);'''sql5= '''alter table Score add index idx_StdID_CouID(StdID, CouID);'''

#sql6 = '''alter table Score drop index idx_StdID_CouID;''' 刪除索引

sql7 = '''explain select * from Score where StdID = 16213;'''

try:

cus=cnx.cursor()

cus.execute(sql1)

cus.close()

cus=cnx.cursor()

cus.execute(sql2)

cus.close()

cus=cnx.cursor()

cus.execute(sql3)

cus.close()

cus=cnx.cursor()

cus.execute(sql4)

cus.close()

cus=cnx.cursor()

cus.execute(sql5)

cus.close()

cus=cnx.cursor()

cus.execute(sql7)

result=cus.fetchall()print(result)

cus.close()

cnx.commit()exceptException as e:

cnx.rollback()print('error')raiseefinally:

cnx.close()

結(jié)果：

((1L, u'SIMPLE', u'Score', u'ref', u'idx_StdID_CouID', u'idx_StdID_CouID', u'4', u'const', 4L, None),)

解釋：

Sql1， sql2， sql3， sql4是添加主鍵，sql5是增加一個(gè)索引，我們也可以在mysql的客戶端上執(zhí)行sq7，得到如下的結(jié)果：

mysql> explain select * from Score where StdID = 16213;

+----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-------+

+----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-------+

+----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-------+

1 row in set (0.00 sec)

這個(gè)說(shuō)明，我們?cè)谒阉鱏tdID的時(shí)候，是走了idx_StdID_CouID索引的。

總結(jié)

以上是生活随笔為你收集整理的python mysql操作_Python的MySQL操作的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。

上一篇：男生qq网名帅气阳光133个
下一篇： wincc历史数据库_WinCC系统的基