Python数据库操作

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

样例数据库字段如下

数据库连接

def get_database():
	return pymysql.connect(host='localhost',
										user='root',
										password='***',
										database='***',
										charset='utf8')

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

C 增加

def db_insert(name):
	connection = get_database()

	try:
    # 创建游标
		with connection.cursor() as cursor:
			sql = 'insert into user (userid, name) values (%s, %s)'
			max_id = db_max_userid()
			nextid = max_id + 1
			cursor.execute(sql, [nextid, name])
			# 数据库更新
			connection.commit()
	except pymysql.DatabaseError:
    # 数据库回滚至连接状态
		connection.rollback()
			
	finally:
    # 数据库更新
    print("数据库关闭")
		connection.close()

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

def db_max_userid():
	connection = get_database()

	try:
		with connection.cursor() as cursor:
      
			sql = 'select max(userid) from user'
			cursor.execute(sql)
			
			result = cursor.fetchall()
      # 这里返回的是一个row数组（很多个记录），row里面则是字段的属性
      # 因为返回的只有userid，所以是row[0]
			if result is not None:
				for row in result:
					return row[0]
	finally:
    # 数据库关闭
		connection.close()

R 读

def db_read():
	connection = get_database()
	try:
		with connection.cursor() as cursor:
			sql = 'select * from user'
			cursor.execute(sql)
			
			result = cursor.fetchall()
			if result is not None:
				print('name | userid |')
				for row in result:
					for value in row:
						print(value, end=' | ')
					print()
	finally:
    print("数据库关闭")
		connection.close()

U 改

def db_update(row):
	connection = get_database()
	
	try:
		with connection.cursor() as cursor:
			sql = 'update user set name = %s where userid = %s'
			affectedcount = cursor.execute(sql, row)
			
			print('影响行数', affectedcount)
			connection.commit()
	except pymysql.DatabaseError as e:
		connection.rollback()
		print(e)
	finally:
		print("数据库关闭")
		connection.close()

D 删

def db_delete(id):
	connection = get_database()
	
	try:
		with connection.cursor() as cursor:
			sql = 'delete from user where id = %s'
			affectedcount = cursor.execute(sql, id)
			
			print('影响行数', affectedcount)
			connection.commit()
	except pymysql.DatabaseError as e:
		connection.rollback()
		print(e)
	finally:
		print("数据库关闭")
		connection.close()