Python操作MySQL(1)

Python操作MySQL
Python DB-API
Python標準數(shù)據(jù)接口為Python DB-API，其提供了數(shù)據(jù)庫應(yīng)用編程接口.
Python DB-API使用流程：
引用API模塊
獲取與數(shù)據(jù)庫的連接
執(zhí)行sql語句與存儲過程
關(guān)閉數(shù)據(jù)庫連接

安裝pymysql
pymysql是用于Python連接mysql數(shù)據(jù)庫的接口，它實現(xiàn)了Python數(shù)據(jù)庫API規(guī)范V2.0，基于 MySQL C API 上建立的。

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

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

示例:
#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()獲取游標
要想操作數(shù)據(jù)庫，光連接數(shù)據(jù)庫是不夠，必須拿到操作數(shù)據(jù)庫的游標，才能進行后續(xù)的操作，比如讀取數(shù)據(jù)，添加數(shù)據(jù)等。通過獲取到的數(shù)據(jù)庫連接實例conn下的cursor()方法來創(chuàng)建游標。游標用來接收返回結(jié)果

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

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

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

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

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

注意：
用executemany()方法一次性批量執(zhí)行sql語句，固然很好，但是當數(shù)據(jù)一次傳入過多到
server端，可能造成server端的buffer溢出，也可能產(chǎn)生一些意想不到的麻煩。所以，合理、分批次使用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("數(shù)據(jù)庫創(chuàng)建成功！")
    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")
# 此處選擇數(shù)據(jù)庫python，connect函數(shù)就不需要指定db參數(shù)

cursor = conn.cursor()
#如果數(shù)據(jù)庫表存在則刪除
    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)
#先關(guān)閉游標
    cursor.close()
    #再關(guān)閉數(shù)據(jù)庫連接
    conn.close()
    print("數(shù)據(jù)表創(chuàng)建成功！")
except pymysql.Error as e:
    print("mysql Error %d : %s" %(e.args[0],e.args[1]))

插入單條數(shù)據(jù)：
#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)

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

批量插入數(shù)據(jù)

#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的參數(shù)可以傳入列表，返回受影響的行數(shù)

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

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

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

練習(xí)：插入100條數(shù)據(jù)
方式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("數(shù)據(jù)插入成功！")
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("數(shù)據(jù)插入成功！")
except pymysql.Error as e:
    print("mysql Error %d : %s" %(e.args[0],e.args[1]))