PyMySQL操作MySQL数据库

1.连接数据库

db = { 'host': '127.0.0.1', #主机 'port': 3306, #端口号 'user': 'root', #用户名 'password': 'root', #密码 'database': 'test', #数据库 } connection = pymysql.connect(**db) #返回一个Connection对象

2.CRUD操作

def selectAll(): connection = pymysql.connect(**db) try: with connection.cursor() as cursor: sql = 'SELECT * FROM employee;' cursor.execute(sql) result = cursor.fetchall() print(result) finally: connection.close() def selectByAge(name): connection = pymysql.connect(**db) try: with connection.cursor() as cursor: sql = 'SELECT * FROM employee WHERE age>=%s;' cursor.execute(sql, (30,)) result = cursor.fetchall() print(result) finally: connection.close() def addEmployee(employee): connection = pymysql.connect(**db) try: with connection.cursor() as cursor: sql = 'INSERT INTO employee VALUES(%s, %s, %s, %s, %s);' cursor.execute(sql, (employee['first_name'], employee['last_name'], employee['age'], employee['sex'], employee['salary'])) connection.commit() finally: connection.close() def updateEmployee(): connection = pymysql.connect(**db) try: with connection.cursor() as cursor: sql = 'UPDATE employee SET first_name=%s WHERE first_name=%s' cursor.execute(sql, ('Tony','zhy', )) connection.commit() finally: connection.close() def deleteEmployee(): connection = pymysql.connect(**db) try: with connection.cursor() as cursor: sql = 'DELETE FROM employee WHERE first_name=%s' cursor.execute(sql, ('tony',)) connection.commit() finally: connection.close()