avatar

Python数据库操作

主要是利用pymysql库来进行操作

样例数据库字段如下

image-20200711144041240

数据库连接

def get_database():
truereturn pymysql.connect(host='localhost',
truetruetruetruetruetruetruetruetruetrueuser='root',
truetruetruetruetruetruetruetruetruetruepassword='***',
truetruetruetruetruetruetruetruetruetruedatabase='***',
truetruetruetruetruetruetruetruetruetruecharset='utf8')

这里连接的是本地的root用户

C 增加

def db_insert(name):
trueconnection = get_database()

truetry:
# 创建游标
truetruewith connection.cursor() as cursor:
truetruetruesql = 'insert into user (userid, name) values (%s, %s)'
truetruetruemax_id = db_max_userid()
truetruetruenextid = max_id + 1
truetruetruecursor.execute(sql, [nextid, name])
truetruetrue# 数据库更新
truetruetrueconnection.commit()
trueexcept pymysql.DatabaseError:
# 数据库回滚至连接状态
truetrueconnection.rollback()
truetruetrue
truefinally:
# 数据库更新
print("数据库关闭")
truetrueconnection.close()

其中用到的db_max_userid()定义如下

def db_max_userid():
trueconnection = get_database()

truetry:
truetruewith connection.cursor() as cursor:

truetruetruesql = 'select max(userid) from user'
truetruetruecursor.execute(sql)
truetruetrue
truetruetrueresult = cursor.fetchall()
# 这里返回的是一个row数组(很多个记录),row里面则是字段的属性
# 因为返回的只有userid,所以是row[0]
truetruetrueif result is not None:
truetruetruetruefor row in result:
truetruetruetruetruereturn row[0]
truefinally:
# 数据库关闭
truetrueconnection.close()

R 读

def db_read():
trueconnection = get_database()
truetry:
truetruewith connection.cursor() as cursor:
truetruetruesql = 'select * from user'
truetruetruecursor.execute(sql)
truetruetrue
truetruetrueresult = cursor.fetchall()
truetruetrueif result is not None:
truetruetruetrueprint('name | userid |')
truetruetruetruefor row in result:
truetruetruetruetruefor value in row:
truetruetruetruetruetrueprint(value, end=' | ')
truetruetruetruetrueprint()
truefinally:
print("数据库关闭")
truetrueconnection.close()

U 改

def db_update(row):
trueconnection = get_database()
true
truetry:
truetruewith connection.cursor() as cursor:
truetruetruesql = 'update user set name = %s where userid = %s'
truetruetrueaffectedcount = cursor.execute(sql, row)
truetruetrue
truetruetrueprint('影响行数', affectedcount)
truetruetrueconnection.commit()
trueexcept pymysql.DatabaseError as e:
truetrueconnection.rollback()
truetrueprint(e)
truefinally:
truetrueprint("数据库关闭")
truetrueconnection.close()

D 删

def db_delete(id):
trueconnection = get_database()
true
truetry:
truetruewith connection.cursor() as cursor:
truetruetruesql = 'delete from user where id = %s'
truetruetrueaffectedcount = cursor.execute(sql, id)
truetruetrue
truetruetrueprint('影响行数', affectedcount)
truetruetrueconnection.commit()
trueexcept pymysql.DatabaseError as e:
truetrueconnection.rollback()
truetrueprint(e)
truefinally:
truetrueprint("数据库关闭")
truetrueconnection.close()
文章作者: X Mεl0n
文章链接: http://www.zrzz.site/2020/07/11/Python%E6%95%B0%E6%8D%AE%E5%BA%93%E6%93%8D%E4%BD%9C/
版权声明: 本博客所有文章除特别声明外,均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来自 X Mεl0n | 随手记

评论