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)

巴特西

python 操作数据库1--连接、执行sql语句

最新文章

热门文章