test1-对数据库表格 增 内容
import pymysql
def main():
no = int(input('部门编号: '))
name = input('部门名称: ')
loc = input('部门所在地: ')
# 1. 创建连接（主机、端口、用户名、口令、数据库名、字符集、……）
con = pymysql.connect(host='localhost', port=3306,
user='root', password='123456',
database='HRS', charset='utf8',
autocommit=True)
try:
# 2. 通过连接对象的cursor方法获取游标
with con.cursor() as cursor:
# 3. 通过游标对象的execute方法向数据库服务器发出SQL
result = cursor.execute(
'insert into TbDept values (%s, %s, %s)',
(no, name, loc)
)
# 4. 处理服务器返回的信息
if result == 1:
print('添加成功')
except pymysql.MySQLError as e:
print('恭喜你又卵了:', e)
finally:
# 5. 关闭连接释放资源
con.close()
if __name__ == '__main__':
main()
test2-对数据库表格 删 内容
import pymysql
def main():
no = int(input('请输入要删除的部门编号: '))
# 1. 创建连接（主机、端口、用户名、口令、数据库名、字符集、……）
con = pymysql.connect(host='localhost', port=3306,
user='root', password='123456',
database='HRS', charset='utf8',
autocommit=True)
try:
# 2. 通过连接对象的cursor方法获取游标
with con.cursor() as cursor:
# 3. 通过游标对象的execute方法向数据库服务器发出SQL
result = cursor.execute(
'delete from TbDept where dno=%s',
(no, )
)
# 4. 处理服务器返回的信息
if result == 1:
print('删除成功')
except pymysql.MySQLError as e:
print('恭喜你又卵了:', e)
finally:
# 5. 关闭连接释放资源
con.close()
if __name__ == '__main__':
main()
test3-对数据库表格 改 内容
import pymysql
def main():
no = int(input('部门编号: '))
name = input('新的名称: ')
loc = input('新的所在地: ')
con = pymysql.connect(host='localhost', port=3306,
user='root', password='123456',
database='HRS', charset='utf8',
autocommit=True)
try:
with con.cursor() as cursor:
result = cursor.execute(
'update TbDept set dname=%s, dloc=%s where dno=%s',
(name, loc, no)
)
if result == 1:
print('更新成功')
except pymysql.MySQLError as e:
print('恭喜你又卵了:', e)
finally:
con.close()
if __name__ == '__main__':
main()
test4-对数据库表格 查 内容
import pymysql
class Dept(object):
def __init__(self, no, name, loc):
self.no = no
self.name = name
self.loc = loc
def __str__(self):
return f'{self.no}\t{self.name}\t{self.loc}'
def main():
con = pymysql.connect(host='localhost',
port=3306,
user='root',
password='123456',
database='HRS',
charset='utf8',
cursorclass=pymysql.cursors.DictCursor)
try:
with con.cursor() as cursor:
cursor.execute('select dno no, dname name, dloc loc from TbDept')
depts = cursor.fetchall()
for dept_dict in depts:
dept = Dept(**dept_dict)
print(dept)
except pymysql.MySQLError:
pass
finally:
con.close()
if __name__ == '__main__':
main()
test5-对数据库表格 批量增 内容
import pymysql
def main():
con = pymysql.connect(host='localhost', port=3306,
user='root', password='123456',
database='HRS', charset='utf8',
autocommit=True)
try:
with con.cursor() as cursor:
# 游标对象的executemany方法可以一次性执行多个SQL操作
# 相当于是以批处理的方式执行SQL（比一个个执行单条SQL效率更高）
result = cursor.executemany(
'insert into TbDept values (%s, %s, %s)',
((50, '研发2部', '上海'),
(60, '销售3部', '深圳'),
(70, '运维2部', '重庆'))
)
print(result)
except pymysql.MySQLError as e:
print('恭喜你又卵了:', e)
finally:
con.close()
if __name__ == '__main__':
main()
