python 操作資料庫1--串連、執行sql語句

標籤：sel   commit   har   char   一個   config   format   cursor   ble   

#!/usr/bin/env python# -*- coding:utf-8 -*-# @Time   : 2017/11/20 16:03# @Author : lijunjiang# @File   : demo.pyimport MySQLdb# 串連資料庫# host 資料庫IP# port 資料庫監聽連接埠# user 資料庫使用者# passwd 使用者密碼# db 資料庫名# charset 字元集  預設uft-8# MySQLdb.Connect 方法#comn=MySQLdb.Connect(host="11.11.11.11",user="python",passwd="python",db="python",charset="utf8",port=3306)# 函數型式def connect_mysql():    db_config = dict(host="11.11.11.11", port=3306, db="python", charset="utf8", user="python", passwd="python")    try:        cnx = MySQLdb.connect(**db_config)    except Exception as err:        raise err    return cnxif __name__ == "__main__":    sql = "create table test(id int not null);"    cnx = connect_mysql()  # 串連mysql    cns = cnx.cursor()     # 建立一個遊標對象    # print(dir(cnx))    try:        cns.execute(sql)    # 執行 sql execute執行一條語句        cns.close()         # 關閉遊標        cnx.commit()        # 提交操作    except Exception as err:        raise err    finally:        cnx.close()         # 關閉串連# 執行多條語    sql_many = ‘insert into test(id) value (%s);‘    param = []    for i in xrange(90,101):        param.append([str(i)])    # print(param)    cnx = connect_mysql()      cus = cnx.cursor()    try:        cus.executemany(sql_many,param)   # executemany（）接收一個sql語句，一個列表        # print(dir(cus))        cus.close()    except Exception as err:        raise err    finally:        cnx.close()# 擷取執行結果    sql_select = ‘select * from test;‘    cnx = connect_mysql()    cus = cnx.cursor()    try:        cus.execute(sql_select)        result_one = cus.fetchone()     # fetchone()  擷取一條結果        print("resutl1 {0}",format(result_one))        result_many = cus.fetchmany(3)   # fetchmany(n) 擷取n條結果        print("resutl1 {0}", format(result_many))        result_all = cus.fetchall()      # fetchall()  擷取所有結果        print("resutl1 {0}", format(result_all))        cus.close()    except Exception as err:        raise err    finally:        cnx.close()

mysql> select * from test;+-----+| id  |+-----+|  90 ||  91 ||  92 ||  93 ||  94 ||  95 ||  96 ||  97 ||  98 ||  99 || 100 |+-----+11 rows in set (0.00 sec)

python 操作資料庫1--串連、執行sql語句