其他
Python操作MySQL的几种方法
使用Python做数据处理时，经常会使用MySQL做数据存储。这里介绍几种Python操作MySQL的几个库模块。
MySQL-python
MySQL-python只支持Python2，使用范围还是较广的，虽然
DEPRECATION: Python 2.7 will reach the end of its life on January 1st, 2020. Please upgrade your Python as Python 2.7 won't be maintained after that date. A future version of pip will drop support for Python 2.7.
Python2在2019年1月1日起将不再更新，但是企业中很多旧的项目就是用的Python2开发的，就像Windows7操作系统微软不更新了，但是使用Windows7的用户仍然占70%以上，所以这个也是有必要的了解的。
安装方法
# pip2 install MySQL-python
# -*- coding:utf-8 -*-
# pip2 install MySQL-python
import MySQLdb
class MysqlClient:
def __init__(self, db, host = "localhost", user = "root", passwd = "root", charset='utf8'):
self.conn = MySQLdb.connect (host = host, user = user, passwd = passwd, db= db, charset=charset)
def insert(self, sql):
cursor = self.conn.cursor()
cursor.execute(sql)
cursor.close()
self.conn.commit()
def query(self, sql):
cursor = self.conn.cursor()
cursor.execute(sql)
result = cursor.fetchall()
cursor.close()
return result
# 执行删除表，清空表操作
def execute(self, sql):
cursor = self.conn.cursor()
cursor.execute(sql)
cursor.close()
self.conn.commit()
def close(self):
self.conn.close ()
注意：这个包，只支持Python2.
用法
def demo():
import mysql.MySQLdbClient as mysqldb
conn = mysqldb.MysqlClient(host = 'localhost', user = 'root', passwd = 'root', db= 'test')
# 查询
print('===============查询结果=============')
sql = 'select user_name,pass_word,email from user'
users = conn.query(sql)
print(users)
# 删除
sql = 'delete from user where user_name ="bb"'
conn.execute(sql)
# 查询删除后
print('================删除后结果============')
sql = 'select user_name,pass_word,email from user'
users = conn.query(sql)
print(users)
# 插入
sql = '''
insert into user(user_name, pass_word, email, nick_name, reg_time) values('bb', 'bb123456', 'bb@126.com', 'bb2', '2019年6月24日 下午11时26分57秒')
'''
conn.insert(sql)
# 查询插入后
print('==============插入后结果==============')
sql = 'select user_name,pass_word,email from user'
users = conn.query(sql)
print(users)
执行脚本：
python mysqlDemo.py
结果
********** demo() =>
===============查询结果=============
(('cc', 'cc123456', 'cc@126.com'), ('bb', 'bb123456', 'bb@126.com'))
================删除后结果============
(('cc', 'cc123456', 'cc@126.com'),)
==============插入后结果==============
(('cc', 'cc123456', 'cc@126.com'), ('bb', 'bb123456', 'bb@126.com'))
PyMySQL
安装方法
# pip3 install PyMySQL
使用方法
# -*- coding:utf-8 -*-
# pip3 install PyMySQL
import pymysql
class MysqlClient:
def __init__(self, db, host = "localhost", user = "root", passwd = "root", charset='utf8'):
self.conn = pymysql.connect (host = host, user = user, passwd = passwd, db= db, charset=charset)
def insert(self, sql):
cursor = self.conn.cursor()
cursor.execute(sql)
cursor.close()
self.conn.commit()
def query(self, sql):
cursor = self.conn.cursor()
cursor.execute(sql)
result = cursor.fetchall()
cursor.close()
return result
# 执行删除表，清空表操作
def execute(self, sql):
cursor = self.conn.cursor()
cursor.execute(sql)
cursor.close()
self.conn.commit()
def close(self):
self.conn.close ()
用法
def demo2():
import mysql.mysqlclint as mysqlclint
conn = mysqlclint.MysqlClient(host = 'localhost', user = 'root', passwd = 'root', db= 'test')
# 查询
print('===============查询结果=============')
sql = 'select user_name,pass_word,email from user'
users = conn.query(sql)
print(users)
# 删除
sql = 'delete from user where user_name ="bb"'
conn.execute(sql)
# 查询删除后
print('================删除后结果============')
sql = 'select user_name,pass_word,email from user'
users = conn.query(sql)
print(users)
# 插入
sql = '''
insert into user(user_name, pass_word, email, nick_name, reg_time) values('bb', 'bb123456', 'bb@126.com', 'bb2', '2019年6月24日 下午11时26分57秒')
'''
conn.insert(sql)
# 查询插入后
print('==============插入后结果==============')
sql = 'select user_name,pass_word,email from user'
users = conn.query(sql)
print(users)
执行脚本
python mysqlDemo.py
结果
********** demo2() =>
===============查询结果=============
(('cc', 'cc123456', 'cc@126.com'), ('bb', 'bb123456', 'bb@126.com'))
================删除后结果============
(('cc', 'cc123456', 'cc@126.com'),)
==============插入后结果==============
(('cc', 'cc123456', 'cc@126.com'), ('bb', 'bb123456', 'bb@126.com'))
mysqlclient用法
# -*- coding:utf-8 -*-
# pip3 install mysqlclient
import MySQLdb
class MysqlClient:
def __init__(self, db, host = "localhost", user = "root", passwd = "root", charset='utf8'):
self.conn = MySQLdb.connect (host = host, user = user, passwd = passwd, db= db, charset=charset)
def insert(self, sql):
cursor = self.conn.cursor()
cursor.execute(sql)
cursor.close()
self.conn.commit()
def query(self, sql):
cursor = self.conn.cursor()
cursor.execute(sql)
result = cursor.fetchall()
cursor.close()
return result
# 执行删除表，清空表操作
def execute(self, sql):
cursor = self.conn.cursor()
cursor.execute(sql)
cursor.close()
self.conn.commit()
def close(self):
self.conn.close ()
用法
def demo3():
import mysql.PyMSQLClient as pymysql
conn = pymysql.MysqlClient(host = 'localhost', user = 'root', passwd = 'root', db= 'test')
# 查询
print('===============查询结果=============')
sql = 'select user_name,pass_word,email from user'
users = conn.query(sql)
print(users)
# 删除
sql = 'delete from user where user_name ="bb"'
conn.execute(sql)
# 查询删除后
print('================删除后结果============')
sql = 'select user_name,pass_word,email from user'
users = conn.query(sql)
print(users)
# 插入
sql = '''
insert into user(user_name, pass_word, email, nick_name, reg_time) values('bb', 'bb123456', 'bb@126.com', 'bb2', '2019年6月24日 下午11时26分57秒')
'''
conn.insert(sql)
# 查询插入后
print('==============插入后结果==============')
sql = 'select user_name,pass_word,email from user'
users = conn.query(sql)
print(users)
执行脚本
python mysqlDemo.py
结果
********** demo3() =>
===============查询结果=============
(('cc', 'cc123456', 'cc@126.com'), ('bb', 'bb123456', 'bb@126.com'))
================删除后结果============
(('cc', 'cc123456', 'cc@126.com'),)
==============插入后结果==============
(('cc', 'cc123456', 'cc@126.com'), ('bb', 'bb123456', 'bb@126.com'))
程序入口
if __name__ == '__main__':
print('********** demo() => ')
demo()
print('********** demo2() => ')
demo2()
print('********** demo3() => ')
demo3()
猜你可能喜欢