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进行查询、修改、将对应的值提取出来
# coding:utf-8
import pymysql mysql_info = {"host": "xx.xxx.xxx.x",
"port": 1234,
"user": "root",
"passwd": "root",
"db": "xxxx",
"charset": "utf8"} class TestMySQL(object):
"""连接MySQL，对MySQL进行增删改查操作"""
def __init__(self):
"""连接池方式"""
db_info = mysql_info
self.db = TestMySQL.__get_concent(db_info) def __get_concent(db_info):
"""从连接池中取出连接"""
try:
# 打开数据库链接
db = pymysql.connect(host=mysql_info["host"],
port=mysql_info["port"],
user=mysql_info["user"],
passwd=mysql_info["passwd"],
db=mysql_info["db"],
charset=mysql_info["charset"])
return db
except Exception as a:
print("链接数据库异常：%s" % a) def select_mysql(self, sql):
"""查询MySQL数据"""
# 使用 cursor() 方法创建一个游标对象 cur
cur = self.db.cursor()
try:
# 使用 execute() 方法执行 SQL 查询
cur.execute(sql)
except Exception as a:
print("执行MySQL语句出现异常：%s" % a)
else:
# 使用 fetchall() 方法获取查询结果
data = cur.fetchall()
cur.close() # 关闭游标
return data def modify_mysql(self, sql):
"""修改MySQL数据，可以进行插入、更新、删除操作"""
# 使用 cursor() 方法创建一个游标对象 cur
cur = self.db.cursor()
try:
cur.execute(sql)
except Exception as a:
print("执行MySQL语句出现异常：%s" % a)
else:
# 一定要提交，否则不生效
self.db.commit()
cur.close() def getstring_mysql(self, sql):
"""从查询结果取出某个对应字段的值"""
data = self.select_mysql(sql)
if data is not None:
for i in data:
for j in i:
return j def close_mysql(self):
"""断开MySQL连接"""
try:
self.db.close()
except Exception as a:
print("关闭数据库异常：%s" % a) if __name__ == '__main__':
insert_sql = "insert student (id, name, age, sex) VALUES (7, '珠儿a', 20, 1)"
update_sql = "update student set name='王语嫣', sex=1 where id = 7"
delete_sql = "delete from student where id = 7"
select_sql = "select * from student"
getdata_sql = "select name from student where id=1" test_mysql = TestMySQL()
test_mysql.modify_mysql(insert_sql)
insert = test_mysql.select_mysql(select_sql)
print(insert) test_mysql.modify_mysql(update_sql)
update = test_mysql.select_mysql(select_sql)
print(update) test_mysql.modify_mysql(delete_sql)
delete = test_mysql.select_mysql(select_sql)
print(delete) getdata = test_mysql.getstring_mysql(getdata_sql)
print(getdata)