python连接mysql
import pymysql#创建数据库 打开数据库连接，不需要指定数据库，因为需要创建数据库 (ip 用户名 密码 端口要创建的数据库名称)
def mysql_chuang_jian_shu_ju_ku(localhoststr, userstr, passwordstr,portstr,sqlstr):
try:
conn = pymysql.connect(host=localhoststr, user=userstr, passwd=passwordstr,port=portstr)
#获取游标
cursor=conn.cursor()
#创建pythonBD数据库
cursor.execute('CREATE DATABASE IF NOT EXISTS ' + sqlstr + ' DEFAULT CHARSET utf8 COLLATE utf8_general_ci;')
cursor.close()#先关闭游标
conn.close()#再关闭数据库连接
return "mysql_chuang_jian_shu_ju_ku_ok"
except Exception as e:
cursor.close()
conn.commit()
conn.close()
return str(e)
#创建表(ip 用户名 密码 端口 数据库名要创建的表名 要创建的字段和属性)
def mysql_chuang_jian_biao(localhoststr, userstr, passwordstr,portstr,dbstr,tablestr,sqlstr):
try:
conn = pymysql.connect(host=localhoststr, user=userstr, passwd=passwordstr,port=portstr)
conn.select_db(dbstr)
cursor = conn.cursor()# 获取游标
cursor.execute('drop table if exists ' + tablestr)
cursor.execute(sqlstr)
cursor.close()#先关闭游标
conn.close()#再关闭数据库连接
return "mysql_chuang_jian_biao_ok"
except Exception as e:
cursor.close()
conn.commit()
conn.close()
return str(e)
#插入数据单条或多条(ip 用户名 密码 端口 数据库名要插入语句前部，要插入语句后部分)
def mysql_cha_ru(localhoststr, userstr, passwordstr,portstr,dbstr,sqlstra,sqlstrb):
try:
conn = pymysql.connect(host=localhoststr, user=userstr, passwd=passwordstr,port=portstr)
conn.select_db(dbstr)
cursor = conn.cursor()# 获取游标
insert = cursor.executemany(sqlstra,sqlstrb)
cursor.close()
conn.commit()
conn.close()
return insert
except Exception as e:
cursor.close()
conn.commit()
conn.close()
return str(e)
#查询数据(ip 用户名 密码 端口 数据库名查询语句)
def mysql_cha_xun(localhoststr, userstr, passwordstr,portstr,dbstr,sqlstr):
try:
conn = pymysql.connect(host=localhoststr, user=userstr, passwd=passwordstr,port=portstr)
conn.select_db(dbstr)
cursor = conn.cursor()# 获取游标
cursor.execute(sqlstr)
mysql_cha_xun_str = cursor.fetchall() #resTuple=cur.fetchmany(3) 取3条
cursor.close()
conn.commit()
conn.close()
return mysql_cha_xun_str
except Exception as e:
cursor.close()
conn.commit()
conn.close()
return str('mysql_cha_xun_no')
#更新删除数据单条(ip 用户名 密码 端口 数据库名更新语句前，更新语句后)
def mysql_geng_xin_shan_chu_dan_tiao(localhoststr, userstr, passwordstr,portstr,dbstr,sqlstr):
try:
conn = pymysql.connect(host=localhoststr, user=userstr, passwd=passwordstr,port=portstr)
conn.select_db(dbstr)
cursor = conn.cursor()# 获取游标
mysql_geng_xin_shan_chu_dan_tiao_str = cursor.execute(sqlstr)
cursor.close()
conn.commit()
conn.close()
return mysql_geng_xin_shan_chu_dan_tiao_str
except Exception as e:
cursor.close()
conn.commit()
conn.close()
return str('mysql_geng_xin_shan_chu_dan_tiao_no')#'mysql_geng_xin_no'
#更新删除数据多条(ip 用户名 密码 端口 数据库名更新语句前，更新语句后)
def mysql_geng_xin_shan_chu_duo_tiao(localhoststr, userstr, passwordstr,portstr,dbstr,sqlstra,sqlstrb):
try:
conn = pymysql.connect(host=localhoststr, user=userstr, passwd=passwordstr,port=portstr)
conn.select_db(dbstr)
cursor = conn.cursor()# 获取游标
mysql_geng_xin_shan_chu_duo_tiao_str = cursor.executemany(sqlstra,sqlstrb)
cursor.close()
conn.commit()
conn.close()
return mysql_geng_xin_shan_chu_duo_tiao_str
except Exception as e:
return str('mysql_geng_xin_shan_chu_duo_tiao_no')#'mysql_geng_xin_no'
#创建数据库 aa = mysql_chuang_jian_shu_ju_ku('223.26.38.2','name','密码',3306,'kylpydb') ip 用户名 密码 端口要创建的数据库名称
页:
[1]