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 pymysqldb = 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 pymysqldb = 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_info18         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 db31         except Exception as a:32             print("链接数据库异常：%s" % a)33 34     def select_mysql(self, sql):35         """查询MySQL数据"""36         # 使用 cursor() 方法创建一个游标对象 cur37         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 data48 49     def modify_mysql(self, sql):50         """修改MySQL数据，可以进行插入、更新、删除操作"""51         # 使用 cursor() 方法创建一个游标对象 cur52         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 j69 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)