pip insatll pymysql
更多细节参考:Python操作Oracle详解!
import pymysql db = pymysql.connect(host='localhost',user='root',password='123456',port=3306,db='spiders',charset=' utf8')
这里面有六个参数,需要为大家一一介绍一下:
参数host:mysql服务器所在的主机的ip; 参数user:用户名; 参数password:密码; 参数port:连接的mysql主机的端口,默认是3306; 参数db:连接的数据库名; 参数charset:当读取数据出现中文会乱码的时候,需要我们设置一下编码;我们使用python操作数据库的时候,那么python就相当于是client,我们是用这个client来操作mysql的server服务器,python3默认采用的utf8字符集,我的mysql服务器默认采用latin1字符集,因此mysql中创建的每张表,都是建表的时候加了utf8编码的,因此这里设置的应该就是connection连接器的编码;
Ⅰ fetchone():一次获取一条记录; import pymysql db = pymysql.connect(host='localhost',user='root',db='huangwei',password='123456',port=3306,charset='utf8') cursor = db.cursor() cursor.execute('select count(*) from person') aa = cursor.fetchone() print(aa) cursor.execute('select name,age from person') for i in range(aa[0]): a,b = cursor.fetchone() c = "我的名字叫{},今年{}岁".format(a,b) display(c) db.close()
结果如下:
import pymysql db = pymysql.connect(host='localhost',user='root',db='huangwei',password='123456',port=3306,charset='utf8') cursor = db.cursor() cursor.execute('select name,age from person') aa = cursor.fetchall() # print(aa) for a,b in aa: c = "我的名字叫{},今年{}岁".format(a,b) display(c) db.close()
结果如下:
import pymysql import pandas as pd db = pymysql.connect(host='localhost',user='root',db='huangwei',password='123456',port=3306,charset='utf8') cursor = db.cursor() df1 = pd.read_sql("select * from student where ssex='男'",db) display(df1) df2 = pd.read_sql("select * from student where ssex='女'",db) display(df2)
结果如下:
这一部分主要带大家对比学习:关系型数据和非关系型数据库的不同之处。咱们了解一下即可,不必过深研究,因为数据分析师基本不会使用这种数据库。
Python操作MongoDB使用的是pymongo库。需要我们使用如下命令提前安装:
pip insatll pymongo
更多细节参考:Python操作MongoDB详解!
from pymongo import MongoClient conn = MongoClient("localhost",27017)
res = collection.find({"age": {"$gte": 19}}) for row in res: print(row)
res = collection.find() for row in res: print(row)
res = collection.find().count() print(res)
from bson.objectid import ObjectId res = collection.find({"_id":ObjectId("5cc506289e1d88c95465488e")}) print(res[0])
res = collection.find().sort("age") for row in res: print(row)
import pymongo res = collection.find().sort("age",pymongo.DESCENDING) for row in res: print(row)
res = collection.find().limit(3).skip(5) for row in res: print(row)
pip insatll cx_Oracle
# ① 用户名、密码和监听写在一起 import cx_Oracle db = cx_Oracle.connect('scott/a123456@DESKTOP-V4LKB10:1521/orcl') # ② 用户名、密码和监听分开写 import cx_Oracle db = cx_Oracle.connect("scott","a123456","192.168.2.1:1521/orcl") # ③ 配置监听并连接 import cx_Oracle moniter = cx_Oracle.makedsn('192.168.2.1',1521,'orcl') db = cx_Oracle.connect('scott','a123456',moniter)
这里有三种常用的方法,分别为大家进行介绍。
Ⅰ fetchone():一次获取一条记录; import cx_Oracle # 注意:一定要加下面这两行代码,负责会中文乱码; import os os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8' db = cx_Oracle.connect("scott","a123456","192.168.2.1:1521/orcl") cursor = db.cursor() cursor.execute('select count(*) from emp1') aa = cursor.fetchone() print(aa) cursor.execute('select ename,deptno,sal from emp1') for i in range(aa[0]): a,b,c = cursor.fetchone() d = "我的名字叫{},所在部门是{},工资是{}美元".format(a,b,c) display(d) db.close()
结果如下:
import cx_Oracle # 注意:一定要加下面这两行代码,负责会中文乱码; import os os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8' db = cx_Oracle.connect("scott","a123456","192.168.2.1:1521/orcl") cursor = db.cursor() cursor.execute('select ename,deptno,sal from emp1') aa = cursor.fetchall() # print(aa) for a,b,c in aa: d = "我的名字叫{},所在部门是{},工资是{}美元".format(a,b,c) display(d) db.close()
结果如下:
import cx_Oracle import pandas as pd import os os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8' db = cx_Oracle.connect("scott","a123456","192.168.2.1:1521/orcl") cursor = db.cursor() df1 = pd.read_sql("select * from emp where deptno=20",db) display(df1) df2 = pd.read_sql("select * from emp where deptno=30",db) display(df2)
结果如下: