python调用sql语句_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)