安装 pymysql 模块
pip3 install pymysql# python2, 默认无 pip 命令# python3, 默认自带 pip3 命令
mysql基本操作
# 导入 pymysql 模块import pymysql# 创建连接conn = pymysql.connect(host='192.168.0.214', port=3306, user='root', passwd='123456', db='tmpdb')# 获取光标cursor = conn.cursor()# 以字典的形式返回结果# cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)# 插入一行result = cursor.execute("insert into tmp(name) values (%s)",("jim"))# 插入多行result = cursor.executemany("insert into tmp(name) values (%s)",[("james1"),("james2"),("james2")])# 修改后必须提交conn.commit()# 获取一行数据cursor.execute('select * from tmp')r = cursor.fetchone()print(r)# 获取4行数据cursor.execute('select * from tmp')r = cursor.fetchmany(4)print(r)# 获取影响的行数result = cursor.execute('select * from tmp')print(result)# 获取所有的值values = cursor.fetchall()print(values)# 把光标移到最开始处cursor.scroll(0, mode='absolute')# 关闭光标cursor.close()# 关闭连接conn.close()
在操作mysql中应当禁止使用字符串拼接sql 的方式
# 禁止例子 :inp = input('please input name: ')sql = 'insert into tmp(name) values("%s")'sql = sql %(inp,)r = cursor.execute(sql)conn.commit()# 推荐例子:inp = input('please input name: ')print(inp, type(inp))r = cursor.execute('insert into tmp(name) values(%s)', inp)conn.commit()# 获取所插入数据的主键id值,注意表的 id 必须是 AUTO_INCREMENTnid = cursor.lastrowidprint(nid)
补充
# 数据库也可以通过以下方式连接import pymysqlPY_MYSQL_CONN_DICT = { "host" : '192.168.0.214', "port" : 3306, "user" : 'root', "passwd" : '123456', "db" : 'tmpdb'}conn = pymysql.connect(**PY_MYSQL_CONN_DICT)