MySQL数据库的连接与数据库的基本操作

一：    导入数据库并连接

import pymysql.cursors #链接数据库 connect = pymysql.Connect( host='127.0.0.1', port=3306, user='root', passwd='root', db='sz1704', charset='utf8mb4' ) # print("连接成功")

二：  

 

#获取游标 cursor = connect.cursor()

三：

 

#删除表 sql = 'DROP TABLE IF EXISTS student' cursor.execute(sql) connect.commit()

四： 

# 创建表 sql = 'CREATE TABLE student(id INTEGER PRIMARY KEY, name TEXT)' try: cursor.execute(sql) connect.commit() except: print('该表名已经存在')

五：

#插入数据 sql = "UPDATE student SET name = '%s' WHERE id = %d " data = ('student2', 1) cursor.execute(sql % data) connect.commit() print('成功修改', cursor.rowcount, '条数据')

六：

#修改数据 sql = "DELETE FROM student WHERE id = %d LIMIT %d" data = (1, 2) cursor.execute(sql % data) connect.commit() print('成功删除', cursor.rowcount, '条数据')

七：

#事务处理 sql_l = "UPDATE student SET name = name + '1' WHERE id = 1" try: cursor.execute(sql_l) except Exception as e: connect.rollback() #事务回滚 print("事务处理失败", e) else: connect.commit() #事务提交 print('事务处理成功', cursor.rowcount)

八：    

#关闭链接 cursor.close() connect.close()

九：    整体代码

import pymysql.cursors #链接数据库 connect = pymysql.Connect( host='127.0.0.1', port=3306, user='root', passwd='root', db='sz1704', charset='utf8mb4' ) # print("连接成功") #获取游标 cursor = connect.cursor() #删除表 sql = 'DROP TABLE IF EXISTS student' cursor.execute(sql) connect.commit() # 创建表 sql = 'CREATE TABLE student(id INTEGER PRIMARY KEY, name TEXT)' try: cursor.execute(sql) connect.commit() except: print('该表名已经存在') #插入数据 sql = "UPDATE student SET name = '%s' WHERE id = %d " data = ('student2', 1) cursor.execute(sql % data) connect.commit() print('成功修改', cursor.rowcount, '条数据') #修改数据 sql = "DELETE FROM student WHERE id = %d LIMIT %d" data = (1, 2) cursor.execute(sql % data) connect.commit() print('成功删除', cursor.rowcount, '条数据') #事务处理 sql_l = "UPDATE student SET name = name + '1' WHERE id = 1" try: cursor.execute(sql_l) except Exception as e: connect.rollback() #事务回滚 print("事务处理失败", e) else: connect.commit() #事务提交 print('事务处理成功', cursor.rowcount) #关闭链接 cursor.close() connect.close()