{ "cells": [ { "cell_type": "markdown", "metadata": { "toc": true }, "source": [ "

Table of Contents

\n", "
" ] }, { "cell_type": "code", "execution_count": 1, "metadata": {}, "outputs": [], "source": [ "# 设置全部行输出\n", "from IPython.core.interactiveshell import InteractiveShell\n", "InteractiveShell.ast_node_interactivity = \"all\"" ] }, { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [], "source": [ "import pandas as pd" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# python连接mysql数据库" ] }, { "cell_type": "code", "execution_count": 3, "metadata": {}, "outputs": [], "source": [ "# 导包\n", "import pymysql \n", "\n", "# 创建连接对象\n", "mydb = pymysql.connect( \n", " host=\"localhost\", # 数据库主机地址\n", " user=\"root\", # 数据库用户名\n", " password=\"1234\" , # 数据库密码\n", " database=None, # 可以指定连接某个数据库\n", " port=3306, # 端口号, 默认是3306\n", " charset='utf8' # 使用的编码\n", " ) \n", "\n", "# 创建游标对象, 这里创建的游标是 可以返回带字段名的字典， 比较好用\n", "mycursor = mydb.cursor(cursor=pymysql.cursors.DictCursor)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# SQL" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## 库操作" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 查看数据库" ] }, { "cell_type": "code", "execution_count": 4, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "9" ] }, "execution_count": 4, "metadata": {}, "output_type": "execute_result" }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
Database
0information_schema
1学生管理系统
2mysql
3pandas_to_sql
4performance_schema
5python
6school
7sys
8test
\n", "
" ], "text/plain": [ " Database\n", "0 information_schema\n", "1 学生管理系统\n", "2 mysql\n", "3 pandas_to_sql\n", "4 performance_schema\n", "5 python\n", "6 school\n", "7 sys\n", "8 test" ] }, "execution_count": 4, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# 通过游标对象的execute方法执行sql语句\n", "mycursor.execute('''\n", " show databases;\n", "''')\n", "pd.DataFrame(mycursor)" ] }, { "cell_type": "code", "execution_count": 5, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "(('Database', 253, None, 64, 64, 0, False),)" ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "mycursor.description # 列名是'Database\n", "\n", "# 这里也可以查询出列名" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 创建数据库 \n", "新创建的数据库名字不能和sql中的关键字一样， 也不能和已经存在的数据库重名" ] }, { "cell_type": "code", "execution_count": 6, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "1" ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
\n", "
" ], "text/plain": [ "Empty DataFrame\n", "Columns: []\n", "Index: []" ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "mycursor.execute('''\n", " create database 数据库;\n", "''')\n", "pd.DataFrame(mycursor) # 创建数据库不会有返回结果" ] }, { "cell_type": "code", "execution_count": 7, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "10" ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
Database
0information_schema
1学生管理系统
2数据库
3mysql
4pandas_to_sql
5performance_schema
6python
7school
8sys
9test
\n", "
" ], "text/plain": [ " Database\n", "0 information_schema\n", "1 学生管理系统\n", "2 数据库\n", "3 mysql\n", "4 pandas_to_sql\n", "5 performance_schema\n", "6 python\n", "7 school\n", "8 sys\n", "9 test" ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# 再次查询以便就有了 数据库\n", "mycursor.execute('''\n", " show databases;\n", "''')\n", "pd.DataFrame(mycursor)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 删除数据库" ] }, { "cell_type": "code", "execution_count": 8, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "0" ] }, "execution_count": 8, "metadata": {}, "output_type": "execute_result" }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
\n", "
" ], "text/plain": [ "Empty DataFrame\n", "Columns: []\n", "Index: []" ] }, "execution_count": 8, "metadata": {}, "output_type": "execute_result" } ], "source": [ "mycursor.execute('''\n", " drop database 数据库;\n", "''')\n", "pd.DataFrame(mycursor)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 查看已经创建好的数据库 \n", "返回该数据的一些信息" ] }, { "cell_type": "code", "execution_count": 9, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "1" ] }, "execution_count": 9, "metadata": {}, "output_type": "execute_result" }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
DatabaseCreate Database
0学生管理系统CREATE DATABASE `学生管理系统` /*!40100 DEFAULT CHAR...
\n", "
" ], "text/plain": [ " Database Create Database\n", "0 学生管理系统 CREATE DATABASE `学生管理系统` /*!40100 DEFAULT CHAR..." ] }, "execution_count": 9, "metadata": {}, "output_type": "execute_result" } ], "source": [ "mycursor.execute('''\n", " show create database 学生管理系统;\n", "''')\n", "a = pd.DataFrame(mycursor)\n", "a" ] }, { "cell_type": "code", "execution_count": 10, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "array([['学生管理系统',\n", " 'CREATE DATABASE `学生管理系统` /*!40100 DEFAULT CHARACTER SET utf8 */']],\n", " dtype=object)" ] }, "execution_count": 10, "metadata": {}, "output_type": "execute_result" } ], "source": [ "a.values # 可以看出该数据库编码是utf8" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 修改数据库编码" ] }, { "cell_type": "code", "execution_count": 11, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "1" ] }, "execution_count": 11, "metadata": {}, "output_type": "execute_result" } ], "source": [ "mycursor.execute('''\n", " alter database 学生管理系统 character set gbk;\n", "''')" ] }, { "cell_type": "code", "execution_count": 12, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
\n", "
" ], "text/plain": [ "Empty DataFrame\n", "Columns: []\n", "Index: []" ] }, "execution_count": 12, "metadata": {}, "output_type": "execute_result" } ], "source": [ "pd.DataFrame(mycursor)" ] }, { "cell_type": "code", "execution_count": 13, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "1" ] }, "execution_count": 13, "metadata": {}, "output_type": "execute_result" }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
DatabaseCreate Database
0学生管理系统CREATE DATABASE `学生管理系统` /*!40100 DEFAULT CHAR...
\n", "
" ], "text/plain": [ " Database Create Database\n", "0 学生管理系统 CREATE DATABASE `学生管理系统` /*!40100 DEFAULT CHAR..." ] }, "execution_count": 13, "metadata": {}, "output_type": "execute_result" } ], "source": [ "mycursor.execute('''\n", " show create database 学生管理系统;\n", "''')\n", "a = pd.DataFrame(mycursor)\n", "a" ] }, { "cell_type": "code", "execution_count": 15, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "array([['学生管理系统',\n", " 'CREATE DATABASE `学生管理系统` /*!40100 DEFAULT CHARACTER SET gbk */']],\n", " dtype=object)" ] }, "execution_count": 15, "metadata": {}, "output_type": "execute_result" } ], "source": [ "a.values # 编码已经修改成gbk" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 使用某个数据库" ] }, { "cell_type": "code", "execution_count": 16, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "0" ] }, "execution_count": 16, "metadata": {}, "output_type": "execute_result" }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
\n", "
" ], "text/plain": [ "Empty DataFrame\n", "Columns: []\n", "Index: []" ] }, "execution_count": 16, "metadata": {}, "output_type": "execute_result" } ], "source": [ "mycursor.execute('''\n", " use 学生管理系统;\n", "''')\n", "pd.DataFrame(mycursor)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## 表操作对应DataFrame的属性和方法" ] }, { "cell_type": "code", "execution_count": 6, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "0" ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
\n", "
" ], "text/plain": [ "Empty DataFrame\n", "Columns: []\n", "Index: []" ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "mycursor.execute('''\n", " use school;\n", "''')\n", "pd.DataFrame(mycursor)" ] }, { "cell_type": "code", "execution_count": 9, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "1" ] }, "execution_count": 9, "metadata": {}, "output_type": "execute_result" }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
Tables_in_school
0stu
\n", "
" ], "text/plain": [ " Tables_in_school\n", "0 stu" ] }, "execution_count": 9, "metadata": {}, "output_type": "execute_result" } ], "source": [ "mycursor.execute('''\n", " show tables;\n", "''')\n", "pd.DataFrame(mycursor)" ] }, { "cell_type": "code", "execution_count": 10, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
人物姓名语文数学英语
0小明886078
1小高904089
2小婉6710087
\n", "
" ], "text/plain": [ " 人物姓名 语文 数学 英语\n", "0 小明 88 60 78\n", "1 小高 90 40 89\n", "2 小婉 67 100 87" ] }, "execution_count": 10, "metadata": {}, "output_type": "execute_result" } ], "source": [ "#使用包含列表的字典创建DataFrame时,各个列表内元素个数必须一致\n", "data = {'人物姓名':['小明','小高','小婉'], #默认字典的键为dataframe的字段名,不设定索引的时候,自动给出默认索引,从0到len(list)-1\n", " '语文':[88,90,67],\n", " '数学':[60,40,100],\n", " '英语':[78, 89, 87]\n", " }\n", "data = pd.DataFrame(data)\n", "data" ] }, { "cell_type": "code", "execution_count": 11, "metadata": {}, "outputs": [], "source": [ "# 创建数据库引擎\n", "from sqlalchemy import create_engine\n", "engine = create_engine(\"mysql+mysqlconnector://root:1234@localhost:3306/school?charset=utf8\")\n", "\n", "# 这里需要先安装：pip install mysql-connector-python -i https://pypi.douban.com/simple\n", "# 这里如果使用pymysql会有一点问题， 建议就使用mysql-connector-python" ] }, { "cell_type": "code", "execution_count": 12, "metadata": {}, "outputs": [], "source": [ "data.to_sql(name='data', con=engine, index=False)\n", "\n", "# name是表名\n", "# con是数据库引擎\n", "# index=False忽略原行索引" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### df.head(2)近似操作" ] }, { "cell_type": "code", "execution_count": 43, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
人物姓名语文数学英语
0小明886078
1小高904089
\n", "
" ], "text/plain": [ " 人物姓名 语文 数学 英语\n", "0 小明 88 60 78\n", "1 小高 90 40 89" ] }, "execution_count": 43, "metadata": {}, "output_type": "execute_result" } ], "source": [ "data.head(2)" ] }, { "cell_type": "code", "execution_count": 60, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "2" ] }, "execution_count": 60, "metadata": {}, "output_type": "execute_result" }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
人物姓名语文数学英语
0小明886078
1小高904089
\n", "
" ], "text/plain": [ " 人物姓名 语文 数学 英语\n", "0 小明 88 60 78\n", "1 小高 90 40 89" ] }, "execution_count": 60, "metadata": {}, "output_type": "execute_result" } ], "source": [ "mycursor.execute('''\n", " select * from data\n", " limit 2;\n", "''')\n", "pd.DataFrame(mycursor)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### df.shape的近似操作" ] }, { "cell_type": "code", "execution_count": 14, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
人物姓名语文数学英语
0小明886078
1小高904089
2小婉6710087
\n", "
" ], "text/plain": [ " 人物姓名 语文 数学 英语\n", "0 小明 88 60 78\n", "1 小高 90 40 89\n", "2 小婉 67 100 87" ] }, "execution_count": 14, "metadata": {}, "output_type": "execute_result" } ], "source": [ "data" ] }, { "cell_type": "code", "execution_count": 13, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "1" ] }, "execution_count": 13, "metadata": {}, "output_type": "execute_result" }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
行数
03
\n", "
" ], "text/plain": [ " 行数\n", "0 3" ] }, "execution_count": 13, "metadata": {}, "output_type": "execute_result" } ], "source": [ "mycursor.execute('''\n", " select count(*) as 行数 from data;\n", "\n", "''')\n", "pd.DataFrame(mycursor) " ] }, { "cell_type": "code", "execution_count": 15, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "1" ] }, "execution_count": 15, "metadata": {}, "output_type": "execute_result" }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
列数
04
\n", "
" ], "text/plain": [ " 列数\n", "0 4" ] }, "execution_count": 15, "metadata": {}, "output_type": "execute_result" } ], "source": [ "mycursor.execute('''\n", " select count(COLUMN_NAME) as 列数 from information_schema.columns \n", " where table_schema='school' and table_name='data';\n", "\n", "''')\n", "pd.DataFrame(mycursor) " ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### df.index的近似操作-无 \n", "数据库中是没有index的" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### df.columns的近似操作" ] }, { "cell_type": "code", "execution_count": 17, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "4" ] }, "execution_count": 17, "metadata": {}, "output_type": "execute_result" }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
列名
0人物姓名
1语文
2数学
3英语
\n", "
" ], "text/plain": [ " 列名\n", "0 人物姓名\n", "1 语文\n", "2 数学\n", "3 英语" ] }, "execution_count": 17, "metadata": {}, "output_type": "execute_result" } ], "source": [ "mycursor.execute('''\n", " select COLUMN_NAME as 列名 from information_schema.columns \n", " where table_schema='school' and table_name='data';\n", "\n", "''')\n", "pd.DataFrame(mycursor) " ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### df.info()近似操作" ] }, { "cell_type": "code", "execution_count": 18, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "4" ] }, "execution_count": 18, "metadata": {}, "output_type": "execute_result" }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
FieldTypeNullKeyDefaultExtra
0人物姓名textYESNone
1语文bigint(20)YESNone
2数学bigint(20)YESNone
3英语bigint(20)YESNone
\n", "
" ], "text/plain": [ " Field Type Null Key Default Extra\n", "0 人物姓名 text YES None \n", "1 语文 bigint(20) YES None \n", "2 数学 bigint(20) YES None \n", "3 英语 bigint(20) YES None " ] }, "execution_count": 18, "metadata": {}, "output_type": "execute_result" } ], "source": [ "mycursor.execute('''\n", " desc data;\n", "''')\n", "pd.DataFrame(mycursor) " ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### df.describe()的近似操作" ] }, { "cell_type": "code", "execution_count": 20, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
人物姓名语文数学英语
0小明886078
1小高904089
2小婉6710087
\n", "
" ], "text/plain": [ " 人物姓名 语文 数学 英语\n", "0 小明 88 60 78\n", "1 小高 90 40 89\n", "2 小婉 67 100 87" ] }, "execution_count": 20, "metadata": {}, "output_type": "execute_result" } ], "source": [ "data" ] }, { "cell_type": "code", "execution_count": 27, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
countmeanstdmin25%50%75%max
语文3.081.66666712.74101067.077.588.089.090.0
数学3.066.66666730.55050540.050.060.080.0100.0
英语3.084.6666675.85946578.082.587.088.089.0
\n", "
" ], "text/plain": [ " count mean std min 25% 50% 75% max\n", "语文 3.0 81.666667 12.741010 67.0 77.5 88.0 89.0 90.0\n", "数学 3.0 66.666667 30.550505 40.0 50.0 60.0 80.0 100.0\n", "英语 3.0 84.666667 5.859465 78.0 82.5 87.0 88.0 89.0" ] }, "execution_count": 27, "metadata": {}, "output_type": "execute_result" } ], "source": [ "data.describe().T" ] }, { "cell_type": "code", "execution_count": 32, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "1" ] }, "execution_count": 32, "metadata": {}, "output_type": "execute_result" }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
countmeanminmax
0381.66676790
\n", "
" ], "text/plain": [ " count mean min max\n", "0 3 81.6667 67 90" ] }, "execution_count": 32, "metadata": {}, "output_type": "execute_result" } ], "source": [ "mycursor.execute('''\n", " select \n", " count(语文) count, avg(语文) mean, min(语文) min , max(语文) max\n", " from data;\n", "''')\n", "pd.DataFrame(mycursor) " ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### df.sort_values()近似操作" ] }, { "cell_type": "code", "execution_count": 36, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
人物姓名语文数学英语
2小婉6710087
0小明886078
1小高904089
\n", "
" ], "text/plain": [ " 人物姓名 语文 数学 英语\n", "2 小婉 67 100 87\n", "0 小明 88 60 78\n", "1 小高 90 40 89" ] }, "execution_count": 36, "metadata": {}, "output_type": "execute_result" } ], "source": [ "data.sort_values('数学', ascending=False)" ] }, { "cell_type": "code", "execution_count": 40, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "3" ] }, "execution_count": 40, "metadata": {}, "output_type": "execute_result" }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
人物姓名语文数学英语
0小婉6710087
1小明886078
2小高904089
\n", "
" ], "text/plain": [ " 人物姓名 语文 数学 英语\n", "0 小婉 67 100 87\n", "1 小明 88 60 78\n", "2 小高 90 40 89" ] }, "execution_count": 40, "metadata": {}, "output_type": "execute_result" } ], "source": [ "mycursor.execute('''\n", " select * from data\n", " order by 数学 desc;\n", "''')\n", "pd.DataFrame(mycursor) " ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### df.drop_duplicates()近似操作" ] }, { "cell_type": "code", "execution_count": 41, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
人物姓名语文数学英语
0小明886078
1小高904089
2小婉6710087
\n", "
" ], "text/plain": [ " 人物姓名 语文 数学 英语\n", "0 小明 88 60 78\n", "1 小高 90 40 89\n", "2 小婉 67 100 87" ] }, "execution_count": 41, "metadata": {}, "output_type": "execute_result" } ], "source": [ "data.drop_duplicates()" ] }, { "cell_type": "code", "execution_count": 68, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "3" ] }, "execution_count": 68, "metadata": {}, "output_type": "execute_result" }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
人物姓名语文数学英语
0小明886078
1小高904089
2小婉6710087
\n", "
" ], "text/plain": [ " 人物姓名 语文 数学 英语\n", "0 小明 88 60 78\n", "1 小高 90 40 89\n", "2 小婉 67 100 87" ] }, "execution_count": 68, "metadata": {}, "output_type": "execute_result" } ], "source": [ "mycursor.execute('''\n", " select distinct* from data;\n", " \n", "''')\n", "pd.DataFrame(mycursor) " ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### df.isna()近似操作" ] }, { "cell_type": "code", "execution_count": 72, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
人物姓名语文数学英语
\n", "
" ], "text/plain": [ "Empty DataFrame\n", "Columns: [人物姓名, 语文, 数学, 英语]\n", "Index: []" ] }, "execution_count": 72, "metadata": {}, "output_type": "execute_result" } ], "source": [ "data[data.数学.isna()]" ] }, { "cell_type": "code", "execution_count": 73, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "0" ] }, "execution_count": 73, "metadata": {}, "output_type": "execute_result" }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
\n", "
" ], "text/plain": [ "Empty DataFrame\n", "Columns: []\n", "Index: []" ] }, "execution_count": 73, "metadata": {}, "output_type": "execute_result" } ], "source": [ "mycursor.execute('''\n", " select * from data\n", " where 数学 is null\n", " ;\n", " \n", "''')\n", "pd.DataFrame(mycursor) " ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## DataFrame对象的列操作和行操作" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 增加列" ] }, { "cell_type": "code", "execution_count": 74, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
人物姓名语文数学英语
0小明886078
1小高904089
2小婉6710087
\n", "
" ], "text/plain": [ " 人物姓名 语文 数学 英语\n", "0 小明 88 60 78\n", "1 小高 90 40 89\n", "2 小婉 67 100 87" ] }, "execution_count": 74, "metadata": {}, "output_type": "execute_result" } ], "source": [ "data" ] }, { "cell_type": "code", "execution_count": 75, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "0" ] }, "execution_count": 75, "metadata": {}, "output_type": "execute_result" }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
\n", "
" ], "text/plain": [ "Empty DataFrame\n", "Columns: []\n", "Index: []" ] }, "execution_count": 75, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# 增加一列字段 性别\n", "mycursor.execute('''\n", " alter table data add 性别 varchar(10);\n", "''')\n", "pd.DataFrame(mycursor) " ] }, { "cell_type": "code", "execution_count": 76, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "3" ] }, "execution_count": 76, "metadata": {}, "output_type": "execute_result" }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
人物姓名语文数学英语性别
0小明886078None
1小高904089None
2小婉6710087None
\n", "
" ], "text/plain": [ " 人物姓名 语文 数学 英语 性别\n", "0 小明 88 60 78 None\n", "1 小高 90 40 89 None\n", "2 小婉 67 100 87 None" ] }, "execution_count": 76, "metadata": {}, "output_type": "execute_result" } ], "source": [ "mycursor.execute('''\n", " select*from data;\n", "''')\n", "pd.DataFrame(mycursor) " ] }, { "cell_type": "code", "execution_count": 87, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "3" ] }, "execution_count": 87, "metadata": {}, "output_type": "execute_result" }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
\n", "
" ], "text/plain": [ "Empty DataFrame\n", "Columns: []\n", "Index: []" ] }, "execution_count": 87, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# 给 性别 传入值\n", "mycursor.execute('''\n", " update data set 性别='男';\n", "''')\n", "pd.DataFrame(mycursor) " ] }, { "cell_type": "code", "execution_count": 88, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "3" ] }, "execution_count": 88, "metadata": {}, "output_type": "execute_result" }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
人物姓名语文数学英语性别
0小明886078男
1小高904089男
2小婉6710087男
\n", "
" ], "text/plain": [ " 人物姓名 语文 数学 英语 性别\n", "0 小明 88 60 78 男\n", "1 小高 90 40 89 男\n", "2 小婉 67 100 87 男" ] }, "execution_count": 88, "metadata": {}, "output_type": "execute_result" } ], "source": [ "mycursor.execute('''\n", " select*from data;\n", "''')\n", "pd.DataFrame(mycursor) " ] }, { "cell_type": "code", "execution_count": 89, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "1" ] }, "execution_count": 89, "metadata": {}, "output_type": "execute_result" }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
\n", "
" ], "text/plain": [ "Empty DataFrame\n", "Columns: []\n", "Index: []" ] }, "execution_count": 89, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# 给 性别 传入值\n", "mycursor.execute('''\n", " update data set 性别='女'\n", " where 人物姓名='小婉'\n", " ;\n", "''')\n", "pd.DataFrame(mycursor) " ] }, { "cell_type": "code", "execution_count": 90, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "3" ] }, "execution_count": 90, "metadata": {}, "output_type": "execute_result" }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
人物姓名语文数学英语性别
0小明886078男
1小高904089男
2小婉6710087女
\n", "
" ], "text/plain": [ " 人物姓名 语文 数学 英语 性别\n", "0 小明 88 60 78 男\n", "1 小高 90 40 89 男\n", "2 小婉 67 100 87 女" ] }, "execution_count": 90, "metadata": {}, "output_type": "execute_result" } ], "source": [ "mycursor.execute('''\n", " select*from data;\n", "''')\n", "pd.DataFrame(mycursor) " ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 移动列" ] }, { "cell_type": "code", "execution_count": 92, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "0" ] }, "execution_count": 92, "metadata": {}, "output_type": "execute_result" }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
\n", "
" ], "text/plain": [ "Empty DataFrame\n", "Columns: []\n", "Index: []" ] }, "execution_count": 92, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# 把 性别 移动到 人物姓名 之后\n", "mycursor.execute('''\n", " alter table data modify 性别 varchar(10) after 人物姓名;\n", "''')\n", "pd.DataFrame(mycursor) " ] }, { "cell_type": "code", "execution_count": 93, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "3" ] }, "execution_count": 93, "metadata": {}, "output_type": "execute_result" }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
人物姓名性别语文数学英语
0小明男886078
1小高男904089
2小婉女6710087
\n", "
" ], "text/plain": [ " 人物姓名 性别 语文 数学 英语\n", "0 小明 男 88 60 78\n", "1 小高 男 90 40 89\n", "2 小婉 女 67 100 87" ] }, "execution_count": 93, "metadata": {}, "output_type": "execute_result" } ], "source": [ "mycursor.execute('''\n", " select*from data;\n", "''')\n", "pd.DataFrame(mycursor) " ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 修改列" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### 修改列的名" ] }, { "cell_type": "code", "execution_count": 94, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "3" ] }, "execution_count": 94, "metadata": {}, "output_type": "execute_result" }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
\n", "
" ], "text/plain": [ "Empty DataFrame\n", "Columns: []\n", "Index: []" ] }, "execution_count": 94, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# 把 人物姓名 修改为 姓名\n", "mycursor.execute('''\n", " alter table data change 人物姓名 姓名 varchar(10);\n", "''')\n", "pd.DataFrame(mycursor) " ] }, { "cell_type": "code", "execution_count": 95, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "3" ] }, "execution_count": 95, "metadata": {}, "output_type": "execute_result" }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
姓名性别语文数学英语
0小明男886078
1小高男904089
2小婉女6710087
\n", "
" ], "text/plain": [ " 姓名 性别 语文 数学 英语\n", "0 小明 男 88 60 78\n", "1 小高 男 90 40 89\n", "2 小婉 女 67 100 87" ] }, "execution_count": 95, "metadata": {}, "output_type": "execute_result" } ], "source": [ "mycursor.execute('''\n", " select*from data;\n", "''')\n", "pd.DataFrame(mycursor) " ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### 修改列的值" ] }, { "cell_type": "code", "execution_count": 96, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "1" ] }, "execution_count": 96, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# 将小明的数学改为100\n", "mycursor.execute('''\n", " update data set 数学=100\n", " where 姓名='小明';\n", "\n", "''')" ] }, { "cell_type": "code", "execution_count": 97, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "3" ] }, "execution_count": 97, "metadata": {}, "output_type": "execute_result" }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
姓名性别语文数学英语
0小明男8810078
1小高男904089
2小婉女6710087
\n", "
" ], "text/plain": [ " 姓名 性别 语文 数学 英语\n", "0 小明 男 88 100 78\n", "1 小高 男 90 40 89\n", "2 小婉 女 67 100 87" ] }, "execution_count": 97, "metadata": {}, "output_type": "execute_result" } ], "source": [ "mycursor.execute('''\n", " select*from data;\n", "''')\n", "pd.DataFrame(mycursor) " ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 删除列" ] }, { "cell_type": "code", "execution_count": 98, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "0" ] }, "execution_count": 98, "metadata": {}, "output_type": "execute_result" } ], "source": [ "mycursor.execute('''\n", " alter table data drop 性别; \n", "''')" ] }, { "cell_type": "code", "execution_count": 99, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "3" ] }, "execution_count": 99, "metadata": {}, "output_type": "execute_result" }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
姓名语文数学英语
0小明8810078
1小高904089
2小婉6710087
\n", "
" ], "text/plain": [ " 姓名 语文 数学 英语\n", "0 小明 88 100 78\n", "1 小高 90 40 89\n", "2 小婉 67 100 87" ] }, "execution_count": 99, "metadata": {}, "output_type": "execute_result" } ], "source": [ "mycursor.execute('''\n", " select*from data;\n", "''')\n", "pd.DataFrame(mycursor) " ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.7.4" }, "toc": { "base_numbering": 1, "nav_menu": {}, "number_sections": true, "sideBar": true, "skip_h1_title": false, "title_cell": "Table of Contents", "title_sidebar": "Contents", "toc_cell": true, "toc_position": { "height": "calc(100% - 180px)", "left": "10px", "top": "150px", "width": "210.323px" }, "toc_section_display": true, "toc_window_display": true } }, "nbformat": 4, "nbformat_minor": 2 }