Python學(xué)習(xí)—數(shù)據(jù)庫(kù)操作

python2中操作數(shù)據(jù)庫(kù)：MySQLdb
python3中操作數(shù)據(jù)庫(kù)：pymysql

python接連數(shù)據(jù)庫(kù)

1.創(chuàng)建數(shù)據(jù)庫(kù)連接：

    mysql.connect()函數(shù)

2.執(zhí)行數(shù)據(jù)庫(kù)操作：
執(zhí)行數(shù)據(jù)庫(kù)操作，我們要使用連接對(duì)象獲得一個(gè)cursor（游標(biāo)）對(duì)象：

用連接對(duì)象的cursor()方法返回一個(gè)游標(biāo)對(duì)象

接下來(lái),我們會(huì)使用cursor提供的方法來(lái)進(jìn)行工作. 這些方法包括兩大類:1.執(zhí)行命令,2.接收返回值

    執(zhí)行命令：
        callproc(self, procname, args):用來(lái)執(zhí)行存儲(chǔ)過(guò)程,接收的參數(shù)為存儲(chǔ)過(guò)程名和參數(shù)列表,返回值為受影響的行數(shù)
        execute(self, query, args):執(zhí)行單條sql語(yǔ)句,接收的參數(shù)為sql語(yǔ)句本身和使用的參數(shù)列表,返回值為受影響的行數(shù)，它執(zhí)行查找語(yǔ)句時(shí)的返回值是查找到的記錄條數(shù)。
        executemany(self, query, args):執(zhí)行單挑sql語(yǔ)句,但是重復(fù)執(zhí)行參數(shù)列表里的參數(shù),返回值為受影響的行數(shù)
        nextset(self):移動(dòng)到下一個(gè)結(jié)果集
    接受返回值：
        fetchall(self):接收全部的返回結(jié)果行.返回值也是元組，元組中的元素也是元組
        fetchmany(self, size=None):接收size條返回結(jié)果行.如果size的值大于返回的結(jié)果行的數(shù)量,則會(huì)返回cursor.arraysize條數(shù)據(jù).返回結(jié)果時(shí)元組，元組的元素是整條記錄的元組
        fetchone(self):返回一條結(jié)果行.游標(biāo)移動(dòng)到下一條記錄開(kāi)頭
        scroll(self, value, mode='relative'):移動(dòng)指針到某一行.
            如果mode='relative',則表示從當(dāng)前所在行移動(dòng)value條,value可以是負(fù)數(shù)，表示向前移動(dòng)，正數(shù)表示向后移動(dòng)
            如果mode='absolute',則表示從結(jié)果集的第一行移動(dòng)value條.value=0則表示結(jié)果集的開(kāi)頭，
    游標(biāo)屬性：
        游標(biāo)對(duì)象.description:返回表的結(jié)構(gòu)（包括字段名等），返回結(jié)果是一個(gè)元組，元組內(nèi)元素是每個(gè)字段信息構(gòu)成的元組

3.關(guān)閉數(shù)據(jù)庫(kù)：

    關(guān)閉數(shù)據(jù)庫(kù)前先關(guān)閉游標(biāo)：游標(biāo)對(duì)象.close()
    關(guān)閉數(shù)據(jù)庫(kù)連接：連接對(duì)象.close()

1.查詢符合條件的記錄：

select count(*) from tablename where col = 'col';
這種方法性能上有些浪費(fèi)，沒(méi)必要把全部記錄查出來(lái)。
select 1 from tablename where col = 'col' limit 1;
執(zhí)行這條sql語(yǔ)句，所影響的行數(shù)不是0就是1。

特別解釋下limit 1，mysql在找到一條記錄后就不會(huì)往下繼續(xù)找了。性能提升很多。
結(jié)論：推薦第二種方式。

2.添加用戶：

create user：沒(méi)有任何權(quán)限只能登陸。
添加用戶的同時(shí)賦予權(quán)限：grant user

在新添加用戶時(shí)候，如果沒(méi)有在用戶名后指定本地登陸或者遠(yuǎn)程登陸，這個(gè)用戶默認(rèn)是可以遠(yuǎn)程登陸的。
drop user 可以同時(shí)刪除多個(gè)允許遠(yuǎn)程登陸的用戶（不用跟@'%'）。

查看所有用戶
SELECT User, Host, Password FROM mysql.user;

3. mysql的備份與恢復(fù)

備份:

備份數(shù)據(jù)庫(kù)mariadb下的所有表到文件mariadbdump
mysqldump -uroot -p mariadb >mariadb.dump
備份數(shù)據(jù)庫(kù)mariadb下的所有表的結(jié)構(gòu)到文件mariadbdump，不備份其中的數(shù)據(jù)
mysqldump -uroot -pwestos --no-data mariadb > `date +%Y_%m_%d`_mariadb.dump
備份所有的數(shù)據(jù)庫(kù)的所有表到文件mariadbdump
mysqldump -uroot -pwestos --all-databases >mariadb4.dump

恢復(fù):

mysqladmin -uroot -pwestos create mariadb2
mysql -uroot -pwestos mariadb2< mariadb.dump

-----> 在用python的MysqlDB包對(duì)Mysql進(jìn)行insert操作時(shí)出現(xiàn)了問(wèn)題，程序運(yùn)行不報(bào)錯(cuò)，insert語(yǔ)句也沒(méi)有問(wèn)題，但是新的數(shù)據(jù)一直沒(méi)有插入到數(shù)據(jù)庫(kù)中。在查閱了資料之后，發(fā)現(xiàn)是未提交的原因。
數(shù)據(jù)庫(kù)語(yǔ)言可分為四種，

    數(shù)據(jù)操作語(yǔ)言（Date Manipulation Language）DML語(yǔ)言，實(shí)現(xiàn)對(duì)數(shù)據(jù)的基本操作，”增刪改”。
        UPDATE DELETE INSERT

    數(shù)據(jù)定義語(yǔ)言（Data Definition Language ）DDL語(yǔ)言，可以實(shí)現(xiàn)對(duì)數(shù)據(jù)庫(kù)結(jié)構(gòu)、操作方法等的定義：
        create table 創(chuàng)建表
        alter table 修改表
        drop table 刪除表
        truncate table 刪除表中所有行
        create index 創(chuàng)建索引
        drop index 刪除索引**

    數(shù)據(jù)庫(kù)控制語(yǔ)言（Data Control Language）DCL授權(quán)，角色控制
        GRANT 授權(quán)
        REVOKE 取消授權(quán)

    事務(wù)控制語(yǔ)言（Transaction Control Language）事務(wù)控制語(yǔ)言
        SAVEPOINT 設(shè)置保存點(diǎn)
        ROLLBACK 回滾
        SET TRANSACTION

在這之中，DDL語(yǔ)句是自帶commit的，而執(zhí)行DML命令如果沒(méi)有提交，將不會(huì)被其他會(huì)話看到。除非在DML命令之后執(zhí)行了DDL命令或DCL命令，或用戶退出會(huì)話，或終止實(shí)例，此時(shí)系統(tǒng)會(huì)自動(dòng)發(fā)出commit命令，使未提交的DML命令提交。

所以在執(zhí)行了UPDATE,INSERT操作都執(zhí)行一下提交命令：連接對(duì)象.commit()
另外，在連接數(shù)據(jù)庫(kù)的時(shí)候，有一個(gè)默認(rèn)參數(shù)：autocommit，它默認(rèn)值為False，在連接數(shù)據(jù)庫(kù)的時(shí)候設(shè)置這個(gè)參數(shù)autocommit=True，就可以不用再調(diào)用commit()方法了。

參考博客：https://blog.csdn.net/lee_zix/article/details/52216126

-----> mysql語(yǔ)句中----刪除表數(shù)據(jù)drop、truncate和delete的用法:
程度從強(qiáng)到弱

1、drop  table tb
      drop將表格直接刪除，沒(méi)有辦法找回
2、truncate (table) tb
      刪除表中的所有數(shù)據(jù)，不能與where一起使用
3、delete from tb (where)
      刪除表中的數(shù)據(jù)(可制定某一行)

區(qū)別：truncate和delete的區(qū)別
         1、事務(wù)：truncate是不可以rollback的，但是delete是可以rollback的；
              原因：truncate刪除整表數(shù)據(jù)(ddl語(yǔ)句,隱式提交)，delete是一行一行的刪除，可以rollback
         2、效果：truncate刪除后將重新水平線和索引(id從零開(kāi)始) ,delete不會(huì)刪除索引
         3、 truncate 不能觸發(fā)任何Delete觸發(fā)器。
         4、delete 刪除可以返回行數(shù)

來(lái)自：https://www.cnblogs.com/shuaiandjun/p/6042600.html

"""

"""

4.查詢所有字段

def list_col(localhost, username, password, database, tabls_name):
    db = pymysql.connect(localhost, username, password, database, charset="utf8")
    cursor = db.cursor()
    cursor.execute("select * from %s" % tabls_name)
    col_name_list = [tuple[0] for tuple in cursor.description]
    db.close()
    return col_name_list

5.列出所有的表

def list_table(localhost, username, password, database):
    db = pymysql.connect(localhost, username, password, database, charset="utf8")
    cursor = db.cursor()
    cursor.execute("show tables")
    table_list = [tuple[0] for tuple in cursor.fetchall()]
    db.close()
    return table_list

例子：

import pymysql as mysql
u = 'root'
p = 'westos'
d = 'python'

# connect函數(shù)創(chuàng)建數(shù)據(jù)庫(kù)或者打開(kāi)數(shù)據(jù)庫(kù)，返回一個(gè)數(shù)據(jù)庫(kù)的連接對(duì)象，程序員通過(guò)連接對(duì)象訪問(wèn)數(shù)據(jù)庫(kù)
conn = mysql.connect(user=u,passwd=p,db=d,charset='utf8',autocommit=True)
#cursor函數(shù)創(chuàng)建一個(gè)游標(biāo)
cur = conn.cursor()
#execute函數(shù)執(zhí)行sql語(yǔ)句
try:
    cur.execute('create table employee(id int,name varchar(20),age int,sex enum("男","女"),salary int,department varchar(40));')
    cur.execute('insert into employee values(1,"張三",18,"男",5000,"IT部門"),(3,"李四",20,"男",6000,"市場(chǎng)部"),(2,"小紅",28,"女",10000,"人事部");')
    cur.execute('delete from employee where name="張三";')
    cur.execute('update employee set sex="女" where name ="李四";')
except:
    print("error......11111......")
else:
    print('success....11111......')

li = [(i,'user'+str(i),123456) for i in range(1,21)]
try:
    cur.executemany('insert into users values(%s,%s,%s);',li)
    cur.execute('select * from users;')
except:
    print("error......222......")
else:
    print('success....222......')

#用tmp來(lái)接收從數(shù)據(jù)庫(kù)查詢到的所有記錄。
tmp = cur.fetchall()
cur.close()
conn.close()
for i in tmp:
    print(i)