# 设置全部行输出
from IPython.core.interactiveshell import InteractiveShell
InteractiveShell.ast_node_interactivity = "all"
import pandas as pd
# 导包
import pymysql
# 创建连接对象
mydb = pymysql.connect(
host="localhost", # 数据库主机地址
user="root", # 数据库用户名
password="1234" , # 数据库密码
database=None, # 可以指定连接某个数据库
port=3306, # 端口号, 默认是3306
charset='utf8' # 使用的编码
)
# 创建游标对象, 这里创建的游标是 可以返回带字段名的字典, 比较好用
mycursor = mydb.cursor(cursor=pymysql.cursors.DictCursor)
# 通过游标对象的execute方法执行sql语句
mycursor.execute('''
show databases;
''')
pd.DataFrame(mycursor)
9
Database | |
---|---|
0 | information_schema |
1 | 学生管理系统 |
2 | mysql |
3 | pandas_to_sql |
4 | performance_schema |
5 | python |
6 | school |
7 | sys |
8 | test |
mycursor.description # 列名是'Database
# 这里也可以查询出列名
(('Database', 253, None, 64, 64, 0, False),)
新创建的数据库名字不能和sql中的关键字一样, 也不能和已经存在的数据库重名
mycursor.execute('''
create database 数据库;
''')
pd.DataFrame(mycursor) # 创建数据库不会有返回结果
1
# 再次查询以便就有了 数据库
mycursor.execute('''
show databases;
''')
pd.DataFrame(mycursor)
10
Database | |
---|---|
0 | information_schema |
1 | 学生管理系统 |
2 | 数据库 |
3 | mysql |
4 | pandas_to_sql |
5 | performance_schema |
6 | python |
7 | school |
8 | sys |
9 | test |
mycursor.execute('''
drop database 数据库;
''')
pd.DataFrame(mycursor)
0
返回该数据的一些信息
mycursor.execute('''
show create database 学生管理系统;
''')
a = pd.DataFrame(mycursor)
a
1
Database | Create Database | |
---|---|---|
0 | 学生管理系统 | CREATE DATABASE `学生管理系统` /*!40100 DEFAULT CHAR... |
a.values # 可以看出该数据库编码是utf8
array([['学生管理系统', 'CREATE DATABASE `学生管理系统` /*!40100 DEFAULT CHARACTER SET utf8 */']], dtype=object)
mycursor.execute('''
alter database 学生管理系统 character set gbk;
''')
1
pd.DataFrame(mycursor)
mycursor.execute('''
show create database 学生管理系统;
''')
a = pd.DataFrame(mycursor)
a
1
Database | Create Database | |
---|---|---|
0 | 学生管理系统 | CREATE DATABASE `学生管理系统` /*!40100 DEFAULT CHAR... |
a.values # 编码已经修改成gbk
array([['学生管理系统', 'CREATE DATABASE `学生管理系统` /*!40100 DEFAULT CHARACTER SET gbk */']], dtype=object)
mycursor.execute('''
use 学生管理系统;
''')
pd.DataFrame(mycursor)
0
mycursor.execute('''
use school;
''')
pd.DataFrame(mycursor)
0
mycursor.execute('''
show tables;
''')
pd.DataFrame(mycursor)
1
Tables_in_school | |
---|---|
0 | stu |
#使用包含列表的字典创建DataFrame时,各个列表内元素个数必须一致
data = {'人物姓名':['小明','小高','小婉'], #默认字典的键为dataframe的字段名,不设定索引的时候,自动给出默认索引,从0到len(list)-1
'语文':[88,90,67],
'数学':[60,40,100],
'英语':[78, 89, 87]
}
data = pd.DataFrame(data)
data
人物姓名 | 语文 | 数学 | 英语 | |
---|---|---|---|---|
0 | 小明 | 88 | 60 | 78 |
1 | 小高 | 90 | 40 | 89 |
2 | 小婉 | 67 | 100 | 87 |
# 创建数据库引擎
from sqlalchemy import create_engine
engine = create_engine("mysql+mysqlconnector://root:1234@localhost:3306/school?charset=utf8")
# 这里需要先安装:pip install mysql-connector-python -i https://pypi.douban.com/simple
# 这里如果使用pymysql会有一点问题, 建议就使用mysql-connector-python
data.to_sql(name='data', con=engine, index=False)
# name是表名
# con是数据库引擎
# index=False忽略原行索引
data.head(2)
人物姓名 | 语文 | 数学 | 英语 | |
---|---|---|---|---|
0 | 小明 | 88 | 60 | 78 |
1 | 小高 | 90 | 40 | 89 |
mycursor.execute('''
select * from data
limit 2;
''')
pd.DataFrame(mycursor)
2
人物姓名 | 语文 | 数学 | 英语 | |
---|---|---|---|---|
0 | 小明 | 88 | 60 | 78 |
1 | 小高 | 90 | 40 | 89 |
data
人物姓名 | 语文 | 数学 | 英语 | |
---|---|---|---|---|
0 | 小明 | 88 | 60 | 78 |
1 | 小高 | 90 | 40 | 89 |
2 | 小婉 | 67 | 100 | 87 |
mycursor.execute('''
select count(*) as 行数 from data;
''')
pd.DataFrame(mycursor)
1
行数 | |
---|---|
0 | 3 |
mycursor.execute('''
select count(COLUMN_NAME) as 列数 from information_schema.columns
where table_schema='school' and table_name='data';
''')
pd.DataFrame(mycursor)
1
列数 | |
---|---|
0 | 4 |
数据库中是没有index的
mycursor.execute('''
select COLUMN_NAME as 列名 from information_schema.columns
where table_schema='school' and table_name='data';
''')
pd.DataFrame(mycursor)
4
列名 | |
---|---|
0 | 人物姓名 |
1 | 语文 |
2 | 数学 |
3 | 英语 |
mycursor.execute('''
desc data;
''')
pd.DataFrame(mycursor)
4
Field | Type | Null | Key | Default | Extra | |
---|---|---|---|---|---|---|
0 | 人物姓名 | text | YES | None | ||
1 | 语文 | bigint(20) | YES | None | ||
2 | 数学 | bigint(20) | YES | None | ||
3 | 英语 | bigint(20) | YES | None |
data
人物姓名 | 语文 | 数学 | 英语 | |
---|---|---|---|---|
0 | 小明 | 88 | 60 | 78 |
1 | 小高 | 90 | 40 | 89 |
2 | 小婉 | 67 | 100 | 87 |
data.describe().T
count | mean | std | min | 25% | 50% | 75% | max | |
---|---|---|---|---|---|---|---|---|
语文 | 3.0 | 81.666667 | 12.741010 | 67.0 | 77.5 | 88.0 | 89.0 | 90.0 |
数学 | 3.0 | 66.666667 | 30.550505 | 40.0 | 50.0 | 60.0 | 80.0 | 100.0 |
英语 | 3.0 | 84.666667 | 5.859465 | 78.0 | 82.5 | 87.0 | 88.0 | 89.0 |
mycursor.execute('''
select
count(语文) count, avg(语文) mean, min(语文) min , max(语文) max
from data;
''')
pd.DataFrame(mycursor)
1
count | mean | min | max | |
---|---|---|---|---|
0 | 3 | 81.6667 | 67 | 90 |
data.sort_values('数学', ascending=False)
人物姓名 | 语文 | 数学 | 英语 | |
---|---|---|---|---|
2 | 小婉 | 67 | 100 | 87 |
0 | 小明 | 88 | 60 | 78 |
1 | 小高 | 90 | 40 | 89 |
mycursor.execute('''
select * from data
order by 数学 desc;
''')
pd.DataFrame(mycursor)
3
人物姓名 | 语文 | 数学 | 英语 | |
---|---|---|---|---|
0 | 小婉 | 67 | 100 | 87 |
1 | 小明 | 88 | 60 | 78 |
2 | 小高 | 90 | 40 | 89 |
data.drop_duplicates()
人物姓名 | 语文 | 数学 | 英语 | |
---|---|---|---|---|
0 | 小明 | 88 | 60 | 78 |
1 | 小高 | 90 | 40 | 89 |
2 | 小婉 | 67 | 100 | 87 |
mycursor.execute('''
select distinct* from data;
''')
pd.DataFrame(mycursor)
3
人物姓名 | 语文 | 数学 | 英语 | |
---|---|---|---|---|
0 | 小明 | 88 | 60 | 78 |
1 | 小高 | 90 | 40 | 89 |
2 | 小婉 | 67 | 100 | 87 |
data[data.数学.isna()]
人物姓名 | 语文 | 数学 | 英语 |
---|
mycursor.execute('''
select * from data
where 数学 is null
;
''')
pd.DataFrame(mycursor)
0
data
人物姓名 | 语文 | 数学 | 英语 | |
---|---|---|---|---|
0 | 小明 | 88 | 60 | 78 |
1 | 小高 | 90 | 40 | 89 |
2 | 小婉 | 67 | 100 | 87 |
# 增加一列字段 性别
mycursor.execute('''
alter table data add 性别 varchar(10);
''')
pd.DataFrame(mycursor)
0
mycursor.execute('''
select*from data;
''')
pd.DataFrame(mycursor)
3
人物姓名 | 语文 | 数学 | 英语 | 性别 | |
---|---|---|---|---|---|
0 | 小明 | 88 | 60 | 78 | None |
1 | 小高 | 90 | 40 | 89 | None |
2 | 小婉 | 67 | 100 | 87 | None |
# 给 性别 传入值
mycursor.execute('''
update data set 性别='男';
''')
pd.DataFrame(mycursor)
3
mycursor.execute('''
select*from data;
''')
pd.DataFrame(mycursor)
3
人物姓名 | 语文 | 数学 | 英语 | 性别 | |
---|---|---|---|---|---|
0 | 小明 | 88 | 60 | 78 | 男 |
1 | 小高 | 90 | 40 | 89 | 男 |
2 | 小婉 | 67 | 100 | 87 | 男 |
# 给 性别 传入值
mycursor.execute('''
update data set 性别='女'
where 人物姓名='小婉'
;
''')
pd.DataFrame(mycursor)
1
mycursor.execute('''
select*from data;
''')
pd.DataFrame(mycursor)
3
人物姓名 | 语文 | 数学 | 英语 | 性别 | |
---|---|---|---|---|---|
0 | 小明 | 88 | 60 | 78 | 男 |
1 | 小高 | 90 | 40 | 89 | 男 |
2 | 小婉 | 67 | 100 | 87 | 女 |
# 把 性别 移动到 人物姓名 之后
mycursor.execute('''
alter table data modify 性别 varchar(10) after 人物姓名;
''')
pd.DataFrame(mycursor)
0
mycursor.execute('''
select*from data;
''')
pd.DataFrame(mycursor)
3
人物姓名 | 性别 | 语文 | 数学 | 英语 | |
---|---|---|---|---|---|
0 | 小明 | 男 | 88 | 60 | 78 |
1 | 小高 | 男 | 90 | 40 | 89 |
2 | 小婉 | 女 | 67 | 100 | 87 |
# 把 人物姓名 修改为 姓名
mycursor.execute('''
alter table data change 人物姓名 姓名 varchar(10);
''')
pd.DataFrame(mycursor)
3
mycursor.execute('''
select*from data;
''')
pd.DataFrame(mycursor)
3
姓名 | 性别 | 语文 | 数学 | 英语 | |
---|---|---|---|---|---|
0 | 小明 | 男 | 88 | 60 | 78 |
1 | 小高 | 男 | 90 | 40 | 89 |
2 | 小婉 | 女 | 67 | 100 | 87 |
# 将小明的数学改为100
mycursor.execute('''
update data set 数学=100
where 姓名='小明';
''')
1
mycursor.execute('''
select*from data;
''')
pd.DataFrame(mycursor)
3
姓名 | 性别 | 语文 | 数学 | 英语 | |
---|---|---|---|---|---|
0 | 小明 | 男 | 88 | 100 | 78 |
1 | 小高 | 男 | 90 | 40 | 89 |
2 | 小婉 | 女 | 67 | 100 | 87 |
mycursor.execute('''
alter table data drop 性别;
''')
0
mycursor.execute('''
select*from data;
''')
pd.DataFrame(mycursor)
3
姓名 | 语文 | 数学 | 英语 | |
---|---|---|---|---|
0 | 小明 | 88 | 100 | 78 |
1 | 小高 | 90 | 40 | 89 |
2 | 小婉 | 67 | 100 | 87 |