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

#!/usr/bin/env python
# -*- coding:utf-8 -*-
# @Time   : 2017/11/20 16:03
# @Author : lijunjiang
# @File   : demo.py

import 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 cnx

if __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)