pymysql模块是python3中用于操作mysql数据库的一个库,不是自带模块,需要运行pip install pymysql进行安装。数据库的操作无非连接、查询、增、删、改:
示例:
1 import pymysql 2 3 #打开数据库连接 4 conn = pymysql.connect( 5 host = "localhost", 6 user = "sq_test", 7 password = "**********", 8 port = 3306, 9 database = "sq_test", 10 charset = "utf8", 11 ) 12 #获取操作游标 13 cursor = conn.cursor() 14 #插入数据 15 def insert_data(): 16 sql = "insert into mytable(name,age,job,city) values('Donfag',20,'IT','ShangHai')" 17 try: 18 cursor.execute(sql) 19 conn.commit() 20 except Exception as e: 21 print(e) 22 conn.rollback() 23 finally: 24 conn.close() 25 #批量插入数据 26 def many_insert_data(): 27 sql = "insert into mytable(name,age,job,city) values(%s,%s,%s,%s)" 28 lst = [] 29 data1 = ('Donfag1',21,'Teacher','BeiJin') 30 data2 = ('Donfag2',22,'Worker', 'HeiFei') 31 lst.append(data1) 32 lst.append(data2) 33 try: 34 cursor.executemany(sql,lst) 35 conn.commit() 36 except Exception as e: 37 print(e) 38 conn.rollback() 39 finally: 40 conn.close() 41 #查询数据 42 def get_data(): 43 #fetchone():获取一个查询结果,结果集是一个对象 44 #fetchall():接收全部的返回结果 45 #rowcount:返回execute()方法影响的行数 46 sql = "select * from mytable" 47 try: 48 cursor.execute(sql) 49 results = cursor.fetchall() 50 record = cursor.rowcount 51 print(f'总共{record}条记录') 52 for row in results: 53 id = row[0] 54 name = row[1] 55 age = row[2] 56 job = row[3] 57 city = row[4] 58 print(f'id={id},name={name},age={age},job={job},city={city}') 59 except Exception as e: 60 print(e) 61 finally: 62 conn.close() 63 #更新数据 64 def update_data(): 65 sql = "update mytable set age=30 where name='Donfag'" 66 try: 67 cursor.execute(sql) 68 conn.commit() 69 except Exception as e: 70 print(e) 71 conn.rollback() 72 finally: 73 conn.close() 74 #删除数据 75 def delete_data(): 76 sql = "delete from mytable where id=3" 77 try: 78 cursor.execute(sql) 79 conn.commit() 80 except Exception as e: 81 print(e) 82 conn.rollback() 83 finally: 84 conn.close()
标签:pymysql,cursor,模块,sql,print,conn,row From: https://www.cnblogs.com/donfag/p/17768166.html