Python學習—數據庫操作

python2中操作數據庫：MySQLdb
python3中操作數據庫：pymysql

python接連數據庫

1.創建數據庫連接：

    mysql.connect()函數

2.執行數據庫操作：
執行數據庫操作，我們要使用連接對象獲得一個cursor（游標）對象：

用連接對象的cursor()方法返回一個游標對象

接下來,我們會使用cursor提供的方法來進行工作. 這些方法包括兩大類:1.執行命令,2.接收返回值

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

3.關閉數據庫：

    關閉數據庫前先關閉游標：游標對象.close()
    關閉數據庫連接：連接對象.close()

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

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

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

2.添加用戶：

create user：沒有任何權限只能登陸。
添加用戶的同時賦予權限：grant user

在新添加用戶時候，如果沒有在用戶名后指定本地登陸或者遠程登陸，這個用戶默認是可以遠程登陸的。
drop user 可以同時刪除多個允許遠程登陸的用戶（不用跟@'%'）。

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

3. mysql的備份與恢復

備份:

備份數據庫mariadb下的所有表到文件mariadbdump
mysqldump -uroot -p mariadb >mariadb.dump
備份數據庫mariadb下的所有表的結構到文件mariadbdump，不備份其中的數據
mysqldump -uroot -pwestos --no-data mariadb > `date +%Y_%m_%d`_mariadb.dump
備份所有的數據庫的所有表到文件mariadbdump
mysqldump -uroot -pwestos --all-databases >mariadb4.dump

恢復:

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

-----> 在用python的MysqlDB包對Mysql進行insert操作時出現了問題，程序運行不報錯，insert語句也沒有問題，但是新的數據一直沒有插入到數據庫中。在查閱了資料之后，發現是未提交的原因。
數據庫語言可分為四種，

    數據操作語言（Date Manipulation Language）DML語言，實現對數據的基本操作，”增刪改”。
        UPDATE DELETE INSERT

    數據定義語言（Data Definition Language ）DDL語言，可以實現對數據庫結構、操作方法等的定義：
        create table 創建表
        alter table 修改表
        drop table 刪除表
        truncate table 刪除表中所有行
        create index 創建索引
        drop index 刪除索引**

    數據庫控制語言（Data Control Language）DCL授權，角色控制
        GRANT 授權
        REVOKE 取消授權

    事務控制語言（Transaction Control Language）事務控制語言
        SAVEPOINT 設置保存點
        ROLLBACK 回滾
        SET TRANSACTION

在這之中，DDL語句是自帶commit的，而執行DML命令如果沒有提交，將不會被其他會話看到。除非在DML命令之后執行了DDL命令或DCL命令，或用戶退出會話，或終止實例，此時系統會自動發出commit命令，使未提交的DML命令提交。

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

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

-----> mysql語句中----刪除表數據drop、truncate和delete的用法:
程度從強到弱

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

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

來自：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函數創建數據庫或者打開數據庫，返回一個數據庫的連接對象，程序員通過連接對象訪問數據庫
conn = mysql.connect(user=u,passwd=p,db=d,charset='utf8',autocommit=True)
#cursor函數創建一個游標
cur = conn.cursor()
#execute函數執行sql語句
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,"市場部"),(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來接收從數據庫查詢到的所有記錄。
tmp = cur.fetchall()
cur.close()
conn.close()
for i in tmp:
    print(i)