原文地址: http://mp.weixin.qq.com/s/6H4n_ZpsuBSUPZmlD9FhzA
作者:数据取经团-王大伟
前言
最近学了一下 sql ,因为做数据分析不会sql真不行。
平时学的都是 Python,所以如果要用 pandas 做数据分析,数据除了导入 excel 和 csv 文件,应该还要会从数据库中导入数据到 Python 中,于是我进行了以下的学习和探索。
环境
Python 3.X
IDE : juyter notebook
安装必要的模块
进入:
http://www.lfd.uci.edu/~gohlke/pythonlibs/#mysqlclient
找到适合自己的版本
下载到本地(我的下载到e:盘根目录)
然后打开 cmd 如下图安装
这里推荐本地安装
使用Python连接数据库
import MySQLdb
如果导入模块没报错,恭喜你,安装模块成功了!
conn = MySQLdb.connect(
host = '127.0.0.1',#本地地址
user = 'root',#一般默认用户名
passwd = '********',#本地数据库登录密码
db = 'test',#数据库名称
port = 3306,#安装mysql默认的端口号
charset = 'utf8'#设置数据库统一编码
)
通过connect 方法连接本地 mysql 数据库,这里要注意你要修改的或许是登录密码和数据库名称
创建游标
首先,我们要创建一个游标
cursor = conn.cursor()
什么是游标呢?
游标就相当于一个缓冲区,存放暂时的结果(这是我的理解)
就像去超市买买买,你可能要推一个手推车,这里的手推车就像游标,推着手推车去对应的货架完成挑选操作,而手推车最终会在出超市时候清空
观察数据库数据
我本地的数据库 test 中有个 tdb_goods 的表
表的内容如上图所示
有商品的 id,名称,种类 id,品牌 id,是否在售和是否下架几个字段
一共有23条记录
获取数据
我们使用 select 语句可以获取数据
cursor.execute('SELECT * FROM `tdb_goods`;')
data = cursor.fetchone()#取一条数据
print(data)
首先我们用游标的 execute 方法执行一句 sql 语句获取 tdb_goods 中的所有数据
然后使用游标的 fetchone 方法取出其中一条记录并且展现出来
如上图,我们取出的数据是元组形式
当然,我们应该养成打开后关闭的好习惯,所以,在操作结束,应该断开与数据库的连接
conn.close()
异常处理
当我们在操作时,希望和在 mysql 数据库中一样,异常时会提示异常信息
那么我们就要加入异常处理模块
try:
conn = MySQLdb.connect(
host = '127.0.0.2222',
user = 'root',
passwd = '********',
db = 'test',
port = 3306,
charset = 'utf8'
)
except MySQLdb.Error as e:
print('Error:%s' % e)
这里异常时,就将异常的信息告知我们了
我们将以上代码整理一下:
import MySQLdb
try:
conn = MySQLdb.connect(
host = '127.0.0.1',
user = 'root',
passwd = '********',
db = 'test',
port = 3306,
charset = 'utf8'
)
cursor = conn.cursor()
cursor.execute('SELECT * FROM `tdb_goods`;')
data = cursor.fetchone()
print(data)
conn.close()
except MySQLdb.Error as e:
print('Error:%s' % e)
当我们将 fetchone 改成 fetchall 时候,就是取出所有信息了
使用面向对象编程
import MySQLdb
class Mysql(object):
def __init__(self):
self.connect()
def connect(self):
try:
self.conn = MySQLdb.connect(
host = '127.0.0.1',
user = 'root',
passwd = '********',
db = 'test',
port = 3306,
charset = 'utf8'
)
except MySQLdb.Error as e:
print('Error:%s' % e)
def close_conn(self):
try:
if self.conn:
self.conn.close()
except MySQLdb.Error as e:
print('Error:%s' % e)
def get_a(self):
sql = 'select * from `tdb_goods` where `cate_id` = %s;'
cursor = self.conn.cursor()
cursor.execute(sql,('1',))
data = cursor.fetchone()
print(data)
cursor.close()
self.close_conn()
def main():
object = Mysql()
object.get_a()
if __name__ == '__main__':
main()
注:这里定义了一个类叫 Mysql,然后用类创建一个对象名为 object,在创建对象的时候,类的初始化时调用连接数据库函数
创建了一个叫 object 的对象,使用对象的 get_a 方法从数据库 tdb_goods 中选出 cate_id=1 的一条记录,在 get_a 函数最后位置断开数据库连接
插入记录到数据库
import MySQLdb
class Mysql(object):
def __init__(self):
self.connect()
def connect(self):
try:
self.conn = MySQLdb.connect(
host = '127.0.0.1',
user = 'root',
passwd = '********',
db = 'test',
port = 3306,
charset = 'utf8'
)
except MySQLdb.Error as e:
print('Error:%s' % e)
def close_conn(self):
try:
if self.conn:
self.conn.close()
except MySQLdb.Error as e:
print('Error:%s' % e)
def get_a(self):
sql = 'select * from `tdb_goods` where `cate_id` = %s;'
cursor = self.conn.cursor()
cursor.execute(sql,('1',))
data = cursor.fetchone()
print(data)
cursor.close()
self.close_conn()
def add_a(self):
sql = "insert into `tdb_goods`(`goods_name`,`cate_id`,`brand_id`,`goods_price`,`is_show`,`is_saleoff`) value (%s,%s,%s,%s,%s,%s);"
cursor = self.conn.cursor()
cursor.execute(sql,('伟哥牌notebook','8','1','66666','1','0'))
cursor.close()
self.close_conn()
def main():
object = Mysql()
object.add_a()
if __name__ == '__main__':
main()
注:这里,将一条记录插入了数据库,但是当我执行完这条语句(没报错)
刷新数据库并没有新增一条记录
划重点:在这里,有一个 self.conn.commit(),这个叫提交,如果不写这句,就无法将所做修改保存的数据库中
加上这个之后(加在以下两句之间):
可以看到,数据成功写入数据库了~
数据导入pandas(数据分析准备)
如果是用 pandas 做数据分析
简单的几行代码即可将 mysql 的数据导入 pandas 进行分析
import MySQLdb
import pandas as pd
#使用python连接数据库
conn = MySQLdb.connect(
host = '127.0.0.1',#本地地址
user = 'root',#一般默认用户名
passwd = '********',#本地数据库登录密码(这里用你自己的密码!!!)
db = 'test',#数据库名称(这里用你自己的数据库名称!!!)
port = 3306,#安装mysql默认的端口号
charset = 'utf8'#设置数据库统一编码
)
sql = 'SELECT * FROM `tdb_goods`;'
df = pd.read_sql(sql,conn)
导入表中所有数据并查看前五行
sql = 'SELECT * FROM `tdb_goods`;'
df = pd.read_sql(sql,conn)
df.head()
选你所想
一般来说,学 sql 增删查改基础中,查用的比较多,接下来我们简单使用一些 sql 查询
查找 cate_id=3 的记录:
sql1 = 'SELECT * FROM `tdb_goods` WHERE `cate_id` = 3;'
df1 = pd.read_sql(sql1,conn)
df1
查找价格大于5000的商品,注意这里默认按照 goods_id 排序的:
sql2 = 'SELECT * FROM `tdb_goods` WHERE `goods_price` > 5000;'
df2 = pd.read_sql(sql2,conn)
df2
加上 ORDER BY goods_prices
之后,就是按照价格升序排列:
sql2 = 'SELECT * FROM `tdb_goods` WHERE `goods_price` > 5000 ORDER BY `goods_price;'
df2 = pd.read_sql(sql2,conn)
df2
sql 默认的是按照升序排列,如果想按照降序排列,可以这样写:
sql2 = 'SELECT * FROM `tdb_goods` WHERE `goods_price` > 5000 ORDER BY `goods_price` DESC;'
df2 = pd.read_sql(sql2,conn)
df2
查找所有的平板电脑:
sql3 = 'SELECT * FROM `tdb_goods` WHERE `goods_name` like "%平板电脑%";'
df3 = pd.read_sql(sql3,conn)
df3
将数据库文件导出成csv
df.to_csv('E:\goods_info.csv', encoding = 'utf-8', index = False)
可以看到,数据成功导出成 csv 了~
最后养成好习惯,断开与数据库的连接:
conn.close()
我们再次查询,发现报错了,说明确实已经断开了连接:
如果运行代码报错,很可能是你的数据库名和数据库登录密码没有修改~
文中如有错误和叙述不妥之处,望指正。