python连接mysql

admin

[mw_shl_code=python,true]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 用户名 密码 端口  要创建的数据库名称

[/mw_shl_code]