python 连接MySQL数据库，进行简单操作
一、连接MySQL数据库，关闭连接
import pymysql
db = pymysql.connect(host="xxx.xxx.x.x", # 数据库ip地址 port=1233, # 端口号 user="root", # 用户名 passwd="*****", # 密码 db="数据库名称", # 数据库名称 charset="utf-8") # 编码 db.close() # 关闭MySQL连接
二、进行查询操作
import pymysql db = pymysql.connect(host="xxx.xxx.x.x", # 数据库ip地址 port=1233, # 端口号 user="root", # 用户名 passwd="*****", # 密码 db="数据库名称", # 数据库名称 charset="utf-8") # 编码 cur = db.cursor() # 创建一个游标对象 cur.execute("sql查询语句") # 执行MySQL语句 data = cur.fetchall() # 获取查询结果 print(data) cur.close() # 关闭游标
三、进行修改操作
import pymysql db = pymysql.connect(host="xxx.xxx.x.x", # 数据库ip地址 port=1233, # 端口号 user="root", # 用户名 passwd="*****", # 密码 db="数据库名称", # 数据库名称 charset="utf-8") # 编码 cur = db.cursor() # 创建一个游标对象 cur.execute("sql修改语句") # 执行MySQL修改语句 db.commit() # 一定要提交，不然数据库不会修改成功 cur.close() # 关闭游标
对MySQL进行查询、修改、将对应的值提取出来
1 # coding:utf-8 2 import pymysql 3 4 5 mysql_info = {"host": "xx.xxx.xxx.x", 6 "port": 1234, 7 "user": "root", 8 "passwd": "root", 9 "db": "xxxx", 10 "charset": "utf8"} 11 12 13 class TestMySQL(object): 14 """连接MySQL，对MySQL进行增删改查操作""" 15 def __init__(self): 16 """连接池方式""" 17 db_info = mysql_info 18 self.db = TestMySQL.__get_concent(db_info) 19 20 def __get_concent(db_info): 21 """从连接池中取出连接""" 22 try: 23 # 打开数据库链接 24 db = pymysql.connect(host=mysql_info["host"], 25 port=mysql_info["port"], 26 user=mysql_info["user"], 27 passwd=mysql_info["passwd"], 28 db=mysql_info["db"], 29 charset=mysql_info["charset"]) 30 return db 31 except Exception as a: 32 print("链接数据库异常：%s" % a) 33 34 def select_mysql(self, sql): 35 """查询MySQL数据""" 36 # 使用 cursor() 方法创建一个游标对象 cur 37 cur = self.db.cursor() 38 try: 39 # 使用 execute() 方法执行 SQL 查询 40 cur.execute(sql) 41 except Exception as a: 42 print("执行MySQL语句出现异常：%s" % a) 43 else: 44 # 使用 fetchall() 方法获取查询结果 45 data = cur.fetchall() 46 cur.close() # 关闭游标 47 return data 48 49 def modify_mysql(self, sql): 50 """修改MySQL数据，可以进行插入、更新、删除操作""" 51 # 使用 cursor() 方法创建一个游标对象 cur 52 cur = self.db.cursor() 53 try: 54 cur.execute(sql) 55 except Exception as a: 56 print("执行MySQL语句出现异常：%s" % a) 57 else: 58 # 一定要提交，否则不生效 59 self.db.commit() 60 cur.close() 61 62 def getstring_mysql(self, sql): 63 """从查询结果取出某个对应字段的值""" 64 data = self.select_mysql(sql) 65 if data is not None: 66 for i in data: 67 for j in i: 68 return j 69 70 def close_mysql(self): 71 """断开MySQL连接""" 72 try: 73 self.db.close() 74 except Exception as a: 75 print("关闭数据库异常：%s" % a) 76 77 78 if __name__ == '__main__': 79 insert_sql = "insert student (id, name, age, sex) VALUES (7, '珠儿a', 20, 1)" 80 update_sql = "update student set name='王语嫣', sex=1 where id = 7" 81 delete_sql = "delete from student where id = 7" 82 select_sql = "select * from student" 83 getdata_sql = "select name from student where id=1" 84 85 test_mysql = TestMySQL() 86 test_mysql.modify_mysql(insert_sql) 87 insert = test_mysql.select_mysql(select_sql) 88 print(insert) 89 90 test_mysql.modify_mysql(update_sql) 91 update = test_mysql.select_mysql(select_sql) 92 print(update) 93 94 test_mysql.modify_mysql(delete_sql) 95 delete = test_mysql.select_mysql(select_sql) 96 print(delete) 97 98 getdata = test_mysql.getstring_mysql(getdata_sql) 99 print(getdata)