MySQL_pymysql模块
安装:pip install pymysql
基本操作
import pymysql conn=pymysql.connect(host='localhost',user='root',password='123',database='lary',charset='utf8') #链接 cursor=conn.cursor() #执行完毕返回的结果集默认以元组显示 游标 #cursor=conn.cursor(cursor=pymysql.cursors.DictCursor) sql='select * from userinfo where name=%s and password=%s ' cursor.execute(sql,params) #执行sql语句,返回sql查询成功的记录数目 #fetchone()/fetchmany()/fetchall() #针对查询语句 #conn.commit() #针对增、删、改语句 cursor.close() conn.close()
pymysql操作数据库类
#!/usr/bin/env python #coding:utf-8 import pymysql import config class MysqlHelper(): def __init__(self): self.conn = config.conn_dict def get_one(self, sql, params): ''' 查询并获取一条记录 :param sql: :param params: :return: ''' conn = pymysql.connect(**self.conn) cur = conn.cursor(cursor=pymysql.cursors.DictCursor) retCount = cur.execute(sql, params) data = cur.fetchone() cur.close() conn.close() return data def get_many(self, sql, params): ''' 查询并获取所有记录 :param sql: :param params: :return: ''' conn = pymysql.connect(**self.conn) cur = conn.cursor(cursor=pymysql.cursors.DictCursor) retCount = cur.execute(sql, params) data = cur.fetchall() cur.close() conn.close() return data def insert_one(self, sql, params): ''' 插入一条数据 :param sql: :param params: :return: ''' conn = pymysql.connect(**self.conn) cur = conn.cursor() cur.execute(sql, params) conn.commit() cur.close() conn.close() return 'insert success' def insert_many(self,sql,params): ''' 插入多条数据 :param sql: :param params: :return: ''' conn = pymysql.connect(**self.conn) cur = conn.cursor() cur.executemany(sql, params) conn.commit() cur.close() conn.close() return 'insert success' def update_one(self,sql,params): ''' 修改一条数据 :param sql: :param params: :return: ''' conn = pymysql.connect(**self.conn) cur = conn.cursor(cursor=pymysql.cursors.DictCursor) cur.execute(sql, params) conn.commit() cur.close() conn.close() return 'update success' def delete_one(self,sql,params): ''' 删除一条数据 :param sql: :param params: :return: ''' conn = pymysql.connect(**self.conn) cur = conn.cursor(cursor=pymysql.cursors.DictCursor) cur.execute(sql, params) conn.commit() cur.close() conn.close() return 'delete success' pymysql操作数据库类
config文件配置
#!/usr/bin/env python #coding:utf-8 conn_dict=dict(host='127.0.0.1',user='root',passwd='123',db='db1')
获取插入的最后一条数据的自增ID
import pymysql conn=pymysql.connect(host='localhost',user='root',password='123',database='db1') cursor=conn.cursor() sql='insert into userinfo(name,password) values("xxx","123");' rows=cursor.execute(sql) print(cursor.lastrowid) #在插入语句后查看 conn.commit() cursor.close() conn.close() 获取插入的最后一条数据的自增ID