本站消息

站长简介/公众号


站长简介:高级软件工程师,曾在阿里云,每日优鲜从事全栈开发工作,利用周末时间开发出本站,欢迎关注我的微信公众号:程序员总部,程序员的家,探索程序员的人生之路!分享IT最新技术,关注行业最新动向,让你永不落伍。了解同行们的工资,生活工作中的酸甜苦辣,谋求程序员的最终出路!

  价值13000svip视频教程,python大神匠心打造,零基础python开发工程师视频教程全套,基础+进阶+项目实战,包含课件和源码

  出租广告位,需要合作请联系站长

+关注
已关注

分类  

暂无分类

标签  

暂无标签

日期归档  

2020-12(251)

2021-01(44)

利用Python连接MySQL将表单转化为DataFrame

发布于2021-10-18 00:17     阅读(489)     评论(0)     点赞(27)     收藏(4)



利用Python连接MySQL将表单转化为DataFrame

表中数据来自于《统计学习方法》第二版P71页

1. 创建loan_application表单

CREATE TABLE loan_application(
	ID int NOT NULL AUTO_INCREMENT,
	Age char(2) NOT NULL,
	job_state char(1) NOT NULL,
	house_state char(1) NOT NULL,
	credit_state varchar(4) NOT NULL,
	category char(1) NOT NULL,
	PRIMARY KEY (ID)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

2. 插入多条数据

 INSERT INTO loan_application
    (Age,job_state,house_state,credit_state,category)
    VALUES
    ("青年","否","否","一般","否"),
    ("青年","否","否","好","否"),
    ("青年","是","否","好","是"),
    ("青年","是","是","一般","是"),
    ("青年","否","否","一般","否"),
    ("中年","否","否","一般","否"),
    ("中年","否","否","好","否"),
    ("中年","是","是","好","是"),
    ("中年","否","是","非常好","是"),
    ("中年","否","是","非常好","是"),
    ("老年","否","是","非常好","是"),
    ("老年","否","是","好","是"),
    ("老年","是","否","好","是"),
    ("老年","是","否","非常好","是"),
    ("老年","否","否","一般","否");

3. 查看表

select * from loan_application;

结果

+----+--------+-----------+-------------+--------------+----------+
| ID | Age    | job_state | house_state | credit_state | category |
+----+--------+-----------+-------------+--------------+----------+
|  1 | 青年   ||| 一般         ||
|  2 | 青年   |||||
|  3 | 青年   |||||
|  4 | 青年   ||| 一般         ||
|  5 | 青年   ||| 一般         ||
|  6 | 中年   ||| 一般         ||
|  7 | 中年   |||||
|  8 | 中年   |||||
|  9 | 中年   ||| 非常好       ||
| 10 | 中年   ||| 非常好       ||
| 11 | 老年   ||| 非常好       ||
| 12 | 老年   |||||
| 13 | 老年   |||||
| 14 | 老年   ||| 非常好       ||
| 15 | 老年   ||| 一般         ||
+----+--------+-----------+-------------+--------------+----------+
15 rows in set (0.00 sec)

4. 连接数据转化DataFrame

# 导包
import pandas as pd
import numpy as np
import pymysql
from sqlalchemy import create_engine

# 数据库类型+数据库驱动名称://用户名:口令@机器地址:端口号/数据库名
engine = create_engine('mysql+pymysql://root:*******@127.0.0.1:3306/mydatabase?charset=utf8')
df = pd.read_sql('select * from loan_application',engine) # 从数据库中导入数据表

******处应该输入自己设置的密码

表格预览:

pd.read_sql()用法

原文链接:https://blog.csdn.net/linjing_zyq/article/details/120768323







所属网站分类: 技术文章 > 博客

作者:fggfg

链接:https://www.pythonheidong.com/blog/article/1060650/d12ec72aeac3a8b5949e/

来源:python黑洞网

任何形式的转载都请注明出处,如有侵权 一经发现 必将追究其法律责任

27 0
收藏该文
已收藏

评论内容:(最多支持255个字符)