Python操作MySQL(1)

Python操作MySQL
Python DB-API
Python標準數據接口為Python DB-API，其提供了數據庫應用編程接口.
Python DB-API使用流程：
引用API模塊
獲取與數據庫的連接
執行sql語句與存儲過程
關閉數據庫連接

安裝pymysql
pymysql是用于Python連接mysql數據庫的接口，它實現了Python數據庫API規范V2.0，基于 MySQL C API 上建立的。

連接數據庫
連接數據庫前，必須確保以下事項：
1. 已經安裝了mysql服務端；
2. 已經創建了數據庫、數據表、表中添加了數據；
3. 連接數據庫使用的用戶名、密碼，以及數據庫服務所在機器的ip，端口號、
用戶擁有訪問數據的權限；
4. 你的機器上已經安裝了Python pymysql模塊

pymysql.connect() 連接數據庫
conn = MySQLdb.connect(
    host = ip,
    user = username,
    passwd = password,
db = database
charset=encoding
)
host：mysql服務所在機器的ip；
port：表示連接所使用的端口號；
user：連接數據庫時所用的用戶名；
passwd：連接數據庫時所使用的用戶的密碼；
db：要連接數據庫的庫名；
charset：連接數據時使用的編碼；

示例:
#encoding=utf-8
import pymysql
conn = pymysql.connect(
    host = "127.0.0.1",
    port = 3306,
    user = "root",
    passwd = "123456",
    db = "hhq",
    charset = "utf8"
)

print(conn)
print(type(conn))                   

conn.cusor()獲取游標
要想操作數據庫，光連接數據庫是不夠，必須拿到操作數據庫的游標，才能進行后續的操作，比如讀取數據，添加數據等。通過獲取到的數據庫連接實例conn下的cursor()方法來創建游標。游標用來接收返回結果

#encoding=utf-8
import pymysql
conn = pymysql.connect(
    host = "127.0.0.1",
    port = 3306,
    user = "root",
    passwd = "123456",
    db = "hhq",
    charset = "utf8"
)
# 使用cursor()方法獲取數據庫的操作游標

cursor = conn.cursor()
print(cursor)
print(type(cursor))               

注意：
cursor()返回一個游標實例對象，其中內置了很多操作數據的方法，比如執行sql語句；

執行sql語句方法execute和executemany。
execute(query, args=None)
execute執行單條sql語句，成功后返回受影響的行數，整型；
參數說明：
? query：要執行的sql語句，字符串類型。
? args：可選的序列或映射，用于query的參數值。如果args為序列，query中必須使用%s做占位符；如果args為映射，query中必須使用%(key)s做占位符

executemany (query, args)
該方法用于批量執行sql語句，比如批量插入數據，返回受影響的行數，整型。
參數說明：
? query：要執行的sql語句，字符串類型。
? args：嵌套的序列或映射，用于query的參數值。
數據庫性能瓶頸很大一部份就在于網絡IO和磁盤IO，將多個sql語句放在一起，只執行一次IO，可以有效的提升數據庫性能。推薦此方法

注意：
用executemany()方法一次性批量執行sql語句，固然很好，但是當數據一次傳入過多到
server端，可能造成server端的buffer溢出，也可能產生一些意想不到的麻煩。所以，合理、分批次使用executemany是個合理的辦法

建庫
#encoding=utf-8
import pymysql
try:
    conn = pymysql.connect(
        host = "127.0.0.1",
        port = 3306,
        user = "root",
        passwd = "123456",
        #db = "hhq",
        charset = "utf8"
    )

    cursor = conn.cursor()

    sql = "create database if not exists testdb default charset utf8 collate utf8_general_ci;"
    cursor.execute(sql)
    cursor.close()
    print("數據庫創建成功！")
    conn.close()
except pymysql.Error as e:
    print("mysql Error %d : %s" %(e.args[0],e.args[1]))

建表
#encoding=utf-8
import pymysql
try:
    conn = pymysql.connect(
        host = "127.0.0.1",
        port = 3306,
        user = "root",
        passwd = "123456",
        #db = "hhq",
        charset = "utf8"
    )
    conn.select_db("pydb")
# 此處選擇數據庫python，connect函數就不需要指定db參數

cursor = conn.cursor()
#如果數據庫表存在則刪除
    cursor.execute("drop table if exists user;")

    sql ="""
        create table user(
           id int default null,
           name varchar(10) default null,
           age int default null
       )"""

cursor.execute(sql)
#先關閉游標
    cursor.close()
    #再關閉數據庫連接
    conn.close()
    print("數據表創建成功！")
except pymysql.Error as e:
    print("mysql Error %d : %s" %(e.args[0],e.args[1]))

插入單條數據：
#encoding=utf-8
import pymysql
try:
    conn = pymysql.connect(
        host = "127.0.0.1",
        port = 3306,
        user = "root",
        passwd = "123456",
        #db = "hhq",
        charset = "utf8"
    )
    conn.select_db("pydb")

    cursor = conn.cursor()

    sql = "insert into user values(2,'張三',20);"

    cursor.execute(sql)

     # 另一種插入數據方法，通過格式字符串傳入值
    sql2 = "insert into user values(%s,%s,%s)"
    cursor.execute(sql2,(3,"李四",25))#這里需要傳入參數組成的元組
    cursor.close()
    conn.commit()
    conn.close()
    print("數據插入成功！")
except pymysql.Error as e:
    print("mysql Error %d : %s" %(e.args[0],e.args[1]))

批量插入數據

#encoding=utf-8
import pymysql
try:
    conn = pymysql.connect(
        host = "127.0.0.1",
        port = 3306,
        user = "root",
        passwd = "123456",
        #db = "hhq",
        charset = "utf8"
    )
    conn.select_db("pydb")

    cursor = conn.cursor()

    sql = "insert into user values(%s,%s,%s)"

#executemany的參數可以傳入列表，返回受影響的行數

    cursor.executemany(sql,
       [(4,"tom",21),
        (5,"lucy",22),
        (6,"robert",25),
        (7,"mimi",18)])

    cursor.close()
    conn.commit()
    conn.close()
    print("數據插入成功！")
except pymysql.Error as e:
    print("mysql Error %d : %s" %(e.args[0],e.args[1]))

批量執行sql語句使用的是executemany(sql, args)函數，返回受影響的行數。Args參數是一個包含多個元組的列表，每個元組對應mysql中的一條數據。注意，這里的%s不需要加引號，否則插入數據的數據會類型錯誤

練習：插入100條數據
方式1:
#encoding=utf-8
import random
import pymysql
try:
    conn = pymysql.connect(
        host = "127.0.0.1",
        port = 3306,
        user = "root",
        passwd = "123456",
        #db = "hhq",
        charset = "utf8"
    )
    conn.select_db("pydb")

    cursor = conn.cursor()

    sql = "insert into user values(%s,%s,%s)"

    for i in range(100):

        cursor.execute(sql,(i,"tom" + str(i),random.randint(20,30)))

    cursor.close()
    conn.commit()
    conn.close()
    print("數據插入成功！")
except pymysql.Error as e:
print("mysql Error %d : %s" %(e.args[0],e.args[1]))

方式2:
#encoding=utf-8
import random
import pymysql
try:
    conn = pymysql.connect(
        host = "127.0.0.1",
        port = 3306,
        user = "root",
        passwd = "123456",
        #db = "hhq",
        charset = "utf8"
    )
    conn.select_db("pydb")

    cursor = conn.cursor()

    sql = "insert into user values(%s,%s,%s)"

    res = cursor.executemany(sql,[(i+100,"lucy" + str(i),random.randint(20,30))  for i in range(100)])

    cursor.close()
    conn.commit()
conn.close()
print("插入成功%s 條" %res)
    print("數據插入成功！")
except pymysql.Error as e:
    print("mysql Error %d : %s" %(e.args[0],e.args[1]))