Pymysql增删改查

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125

1、mysql DDL（数据定义语言）、DML（数据操作语言）和DCL（数据控制语言）   查看服务器版本 select version();   查看所有数据库。 show databases;   切换到指定数据库。 use mysql;   查看数据库下所有表。 show tables;   安装PyMySQL。 pip install pymysql   import pymysql   #添加数据 def main(): no = int(input('编号: ')) name = input('名字: ') loc = input('所在地: ') # 1. 创建数据库连接对象 con = pymysql.connect(host='localhost', port=3306,       database='hrs', charset='utf8',       user='yourname', password='yourpass') try: # 2. 通过连接对象获取游标     with con.cursor() as cursor: # 3. 通过游标执行SQL并获得执行结果          result = cursor.execute(         'insert into tb_dept values (%s, %s, %s)',          (no, name, loc)          )         if result == 1:           print('添加成功!') # 4. 操作成功提交事务      con.commit() finally: # 5. 关闭连接释放资源      con.close()     if __name__ == '__main__': main()     #删除数据 import pymysql def main(): no = int(input('编号: ')) con = pymysql.connect(host='localhost', port=3306,         database='hrs', charset='utf8',         user='yourname', password='yourpass',         autocommit=True) try:     with con.cursor() as cursor:         result = cursor.execute(         'delete from tb_dept where dno=%s',         (no, )         )         if result == 1:             print('删除成功!') finally:         con.close()     if __name__ == '__main__':     main()     #更新数据 import pymysql def main(): no = int(input('编号: ')) name = input('名字: ') loc = input('所在地: ') con = pymysql.connect(host='localhost', port=3306,         database='hrs', charset='utf8',         user='yourname', password='yourpass',         autocommit=True) try:     with con.cursor() as cursor:         result = cursor.execute(         'update tb_dept set dname=%s, dloc=%s where dno=%s',         (name, loc, no)         )         if result == 1:             print('更新成功!') finally:     con.close()     if __name__ == '__main__':     main()       #查询数据 import pymysql from pymysql.cursors import DictCursor   def main(): con = pymysql.connect(host='localhost', port=3306,         database='hrs', charset='utf8',         user='yourname', password='yourpass') try:     with con.cursor(cursor=DictCursor) as cursor:         cursor.execute('select dno as no, dname as name, dloc as loc from tb_dept')         results = cursor.fetchall()         print(results)         print('编号\t名称\t\t所在地')         for dept in results:             print(dept['no'], end='\t')             print(dept['name'], end='\t')             print(dept['loc']) finally:     con.close()     if __name__ == '__main__':     main()