pandas数据分析给力教程【完整版】（二）

Pandas数据加载

上一篇：pandas数据分析给力教程【完整版】（一）
下一篇：pandas数据分析给力教程【完整版】（三）

pandas提供了一些用于将表格型数据读取为DataFrame对象的函数，期中read_csv和read_table这两个使用最多

使用read_csv将其读入

import pandas as pd
from pandas import Series,DataFrame

import numpy as np

pd.read_csv('./data/SMSSpamCollection', sep='\t', header=None) # separator 默认 是,

	0	1
0	ham	Go until jurong point, crazy.. Available only ...
1	ham	Ok lar... Joking wif u oni...
2	spam	Free entry in 2 a wkly comp to win FA Cup fina...
3	ham	U dun say so early hor... U c already then say...
4	ham	Nah I don't think he goes to usf, he lives aro...
5	spam	FreeMsg Hey there darling it's been 3 week's n...
6	ham	Even my brother is not like to speak with me. ...
7	ham	As per your request 'Melle Melle (Oru Minnamin...
8	spam	WINNER!! As a valued network customer you have...
9	spam	Had your mobile 11 months or more? U R entitle...
10	ham	I'm gonna be home soon and i don't want to tal...
11	spam	SIX chances to win CASH! From 100 to 20,000 po...
12	spam	URGENT! You have won a 1 week FREE membership ...
13	ham	I've been searching for the right words to tha...
14	ham	I HAVE A DATE ON SUNDAY WITH WILL!!
15	spam	XXXMobileMovieClub: To use your credit, click ...
16	ham	Oh k...i'm watching here:)
17	ham	Eh u remember how 2 spell his name... Yes i di...
18	ham	Fine if that’s the way u feel. That’s the way ...
19	spam	England v Macedonia - dont miss the goals/team...
20	ham	Is that seriously how you spell his name?
21	ham	I‘m going to try for 2 months ha ha only joking
22	ham	So ü pay first lar... Then when is da stock co...
23	ham	Aft i finish my lunch then i go str down lor. ...
24	ham	Ffffffffff. Alright no way I can meet up with ...
25	ham	Just forced myself to eat a slice. I'm really ...
26	ham	Lol your always so convincing.
27	ham	Did you catch the bus ? Are you frying an egg ...
28	ham	I'm back & we're packing the car now, I'll...
29	ham	Ahhh. Work. I vaguely remember that! What does...
...	...	...
5542	ham	Armand says get your ass over to epsilon
5543	ham	U still havent got urself a jacket ah?
5544	ham	I'm taking derek & taylor to walmart, if I...
5545	ham	Hi its in durban are you still on this number
5546	ham	Ic. There are a lotta childporn cars then.
5547	spam	Had your contract mobile 11 Mnths? Latest Moto...
5548	ham	No, I was trying it all weekend ;V
5549	ham	You know, wot people wear. T shirts, jumpers, ...
5550	ham	Cool, what time you think you can get here?
5551	ham	Wen did you get so spiritual and deep. That's ...
5552	ham	Have a safe trip to Nigeria. Wish you happines...
5553	ham	Hahaha..use your brain dear
5554	ham	Well keep in mind I've only got enough gas for...
5555	ham	Yeh. Indians was nice. Tho it did kane me off ...
5556	ham	Yes i have. So that's why u texted. Pshew...mi...
5557	ham	No. I meant the calculation is the same. That ...
5558	ham	Sorry, I'll call later
5559	ham	if you aren't here in the next <#> hou...
5560	ham	Anything lor. Juz both of us lor.
5561	ham	Get me out of this dump heap. My mom decided t...
5562	ham	Ok lor... Sony ericsson salesman... I ask shuh...
5563	ham	Ard 6 like dat lor.
5564	ham	Why don't you wait 'til at least wednesday to ...
5565	ham	Huh y lei...
5566	spam	REMINDER FROM O2: To get 2.50 pounds free call...
5567	spam	This is the 2nd time we have tried 2 contact u...
5568	ham	Will ü b going to esplanade fr home?
5569	ham	Pity, * was in mood for that. So...any other s...
5570	ham	The guy did some bitching but I acted like i'd...
5571	ham	Rofl. Its true to its name

5572 rows × 2 columns

使用read_table读取

pd.read_table('./data/SMSSpamCollection', header=None)

	0	1
0	ham	Go until jurong point, crazy.. Available only ...
1	ham	Ok lar... Joking wif u oni...
2	spam	Free entry in 2 a wkly comp to win FA Cup fina...
3	ham	U dun say so early hor... U c already then say...
4	ham	Nah I don't think he goes to usf, he lives aro...
5	spam	FreeMsg Hey there darling it's been 3 week's n...
6	ham	Even my brother is not like to speak with me. ...
7	ham	As per your request 'Melle Melle (Oru Minnamin...
8	spam	WINNER!! As a valued network customer you have...
9	spam	Had your mobile 11 months or more? U R entitle...
10	ham	I'm gonna be home soon and i don't want to tal...
11	spam	SIX chances to win CASH! From 100 to 20,000 po...
12	spam	URGENT! You have won a 1 week FREE membership ...
13	ham	I've been searching for the right words to tha...
14	ham	I HAVE A DATE ON SUNDAY WITH WILL!!
15	spam	XXXMobileMovieClub: To use your credit, click ...
16	ham	Oh k...i'm watching here:)
17	ham	Eh u remember how 2 spell his name... Yes i di...
18	ham	Fine if that’s the way u feel. That’s the way ...
19	spam	England v Macedonia - dont miss the goals/team...
20	ham	Is that seriously how you spell his name?
21	ham	I‘m going to try for 2 months ha ha only joking
22	ham	So ü pay first lar... Then when is da stock co...
23	ham	Aft i finish my lunch then i go str down lor. ...
24	ham	Ffffffffff. Alright no way I can meet up with ...
25	ham	Just forced myself to eat a slice. I'm really ...
26	ham	Lol your always so convincing.
27	ham	Did you catch the bus ? Are you frying an egg ...
28	ham	I'm back & we're packing the car now, I'll...
29	ham	Ahhh. Work. I vaguely remember that! What does...
...	...	...
5542	ham	Armand says get your ass over to epsilon
5543	ham	U still havent got urself a jacket ah?
5544	ham	I'm taking derek & taylor to walmart, if I...
5545	ham	Hi its in durban are you still on this number
5546	ham	Ic. There are a lotta childporn cars then.
5547	spam	Had your contract mobile 11 Mnths? Latest Moto...
5548	ham	No, I was trying it all weekend ;V
5549	ham	You know, wot people wear. T shirts, jumpers, ...
5550	ham	Cool, what time you think you can get here?
5551	ham	Wen did you get so spiritual and deep. That's ...
5552	ham	Have a safe trip to Nigeria. Wish you happines...
5553	ham	Hahaha..use your brain dear
5554	ham	Well keep in mind I've only got enough gas for...
5555	ham	Yeh. Indians was nice. Tho it did kane me off ...
5556	ham	Yes i have. So that's why u texted. Pshew...mi...
5557	ham	No. I meant the calculation is the same. That ...
5558	ham	Sorry, I'll call later
5559	ham	if you aren't here in the next <#> hou...
5560	ham	Anything lor. Juz both of us lor.
5561	ham	Get me out of this dump heap. My mom decided t...
5562	ham	Ok lor... Sony ericsson salesman... I ask shuh...
5563	ham	Ard 6 like dat lor.
5564	ham	Why don't you wait 'til at least wednesday to ...
5565	ham	Huh y lei...
5566	spam	REMINDER FROM O2: To get 2.50 pounds free call...
5567	spam	This is the 2nd time we have tried 2 contact u...
5568	ham	Will ü b going to esplanade fr home?
5569	ham	Pity, * was in mood for that. So...any other s...
5570	ham	The guy did some bitching but I acted like i'd...
5571	ham	Rofl. Its true to its name

5572 rows × 2 columns

pd.read_excel('./test.xlsx', sheet_name=2)

---------------------------------------------------------------------------

ImportError                               Traceback (most recent call last)

<ipython-input-8-98d54798daac> in <module>
----> 1 pd.read_excel('./test.xlsx', sheet_name=2)


d:\1903\.venv\lib\site-packages\pandas\util\_decorators.py in wrapper(*args, **kwargs)
    206                 else:
    207                     kwargs[new_arg_name] = new_arg_value
--> 208             return func(*args, **kwargs)
    209 
    210         return wrapper


d:\1903\.venv\lib\site-packages\pandas\io\excel\_base.py in read_excel(io, sheet_name, header, names, index_col, usecols, squeeze, dtype, engine, converters, true_values, false_values, skiprows, nrows, na_values, keep_default_na, verbose, parse_dates, date_parser, thousands, comment, skip_footer, skipfooter, convert_float, mangle_dupe_cols, **kwds)
    308 
    309     if not isinstance(io, ExcelFile):
--> 310         io = ExcelFile(io, engine=engine)
    311     elif engine and engine != io.engine:
    312         raise ValueError(


d:\1903\.venv\lib\site-packages\pandas\io\excel\_base.py in __init__(self, io, engine)
    817         self._io = _stringify_path(io)
    818 
--> 819         self._reader = self._engines[engine](self._io)
    820 
    821     def __fspath__(self):


d:\1903\.venv\lib\site-packages\pandas\io\excel\_xlrd.py in __init__(self, filepath_or_buffer)
     18         """
     19         err_msg = "Install xlrd >= 1.0.0 for Excel support"
---> 20         import_optional_dependency("xlrd", extra=err_msg)
     21         super().__init__(filepath_or_buffer)
     22 


d:\1903\.venv\lib\site-packages\pandas\compat\_optional.py in import_optional_dependency(name, extra, raise_on_missing, on_version)
     91     except ImportError:
     92         if raise_on_missing:
---> 93             raise ImportError(message.format(name=name, extra=extra)) from None
     94         else:
     95             return None


ImportError: Missing optional dependency 'xlrd'. Install xlrd >= 1.0.0 for Excel support Use pip or conda to install xlrd.

!pip install xlrd

Collecting xlrd
  Using cached https://files.pythonhosted.org/packages/b0/16/63576a1a001752e34bf8ea62e367997530dc553b689356b9879339cf45a4/xlrd-1.2.0-py2.py3-none-any.whl
Installing collected packages: xlrd
Successfully installed xlrd-1.2.0

pd.read_excel('./test.xlsx', sheet_name=2)

	Unnamed: 0	Unnamed: 1	期中	期中.1	期中.2	期末	期末.1	期末.2
0	NaN	NaN	语文	数学	英语	语文	数学	英语
1	一班	张三	12	34	66	73	12	34
2	一班	李四	56	35	67	74	13	35
3	一班	王老五	56	36	68	75	14	36
4	二班	张三	45	37	69	76	15	37
5	二班	李四	34	38	70	77	16	38
6	二班	王老五	65	39	71	78	17	39

读取sqlite文件

导包sqlite3

import sqlite3

读取table
pd.read_sql(“sql语句”, con)

# 先创建一个sqllite的连接
conn = sqlite3.connect('./data.sqlite')
conn

<sqlite3.Connection at 0x1346e030>

weather_2017 = pd.read_sql('select * from Weather_2017 limit 30', conn, index_col='index')
weather_2017

	Date/Time	Temp (C)	Dew Point Temp (C)	Rel Hum (%)	Wind Spd (km/h)	Visibility (km)	Stn Press (kPa)	Weather
index
0	2012-01-01 00:00:00	-1.8	-3.9	86	4	8.0	101.24	Fog
1	2012-01-01 01:00:00	-1.8	-3.7	87	4	8.0	101.24	Fog
2	2012-01-01 02:00:00	-1.8	-3.4	89	7	4.0	101.26	Freezing Drizzle,Fog
3	2012-01-01 03:00:00	-1.5	-3.2	88	6	4.0	101.27	Freezing Drizzle,Fog
4	2012-01-01 04:00:00	-1.5	-3.3	88	7	4.8	101.23	Fog
5	2012-01-01 05:00:00	-1.4	-3.3	87	9	6.4	101.27	Fog
6	2012-01-01 06:00:00	-1.5	-3.1	89	7	6.4	101.29	Fog
7	2012-01-01 07:00:00	-1.4	-3.6	85	7	8.0	101.26	Fog
8	2012-01-01 08:00:00	-1.4	-3.6	85	9	8.0	101.23	Fog
9	2012-01-01 09:00:00	-1.3	-3.1	88	15	4.0	101.20	Fog
10	2012-01-01 10:00:00	-1.0	-2.3	91	9	1.2	101.15	Fog
11	2012-01-01 11:00:00	-0.5	-2.1	89	7	4.0	100.98	Fog
12	2012-01-01 12:00:00	-0.2	-2.0	88	9	4.8	100.79	Fog
13	2012-01-01 13:00:00	0.2	-1.7	87	13	4.8	100.58	Fog
14	2012-01-01 14:00:00	0.8	-1.1	87	20	4.8	100.31	Fog
15	2012-01-01 15:00:00	1.8	-0.4	85	22	6.4	100.07	Fog
16	2012-01-01 16:00:00	2.6	-0.2	82	13	12.9	99.93	Mostly Cloudy
17	2012-01-01 17:00:00	3.0	0.0	81	13	16.1	99.81	Cloudy
18	2012-01-01 18:00:00	3.8	1.0	82	15	12.9	99.74	Rain
19	2012-01-01 19:00:00	3.1	1.3	88	15	12.9	99.68	Rain
20	2012-01-01 20:00:00	3.2	1.3	87	19	25.0	99.50	Cloudy
21	2012-01-01 21:00:00	4.0	1.7	85	20	25.0	99.39	Cloudy
22	2012-01-01 22:00:00	4.4	1.9	84	24	19.3	99.32	Rain Showers
23	2012-01-01 23:00:00	5.3	2.0	79	30	25.0	99.31	Cloudy
24	2012-01-02 00:00:00	5.2	1.5	77	35	25.0	99.26	Rain Showers
25	2012-01-02 01:00:00	4.6	0.0	72	39	25.0	99.26	Cloudy
26	2012-01-02 02:00:00	3.9	-0.9	71	32	25.0	99.26	Mostly Cloudy
27	2012-01-02 03:00:00	3.7	-1.5	69	33	25.0	99.30	Mostly Cloudy
28	2012-01-02 04:00:00	2.9	-2.3	69	32	25.0	99.26	Mostly Cloudy
29	2012-01-02 05:00:00	2.6	-2.3	70	32	25.0	99.21	Mostly Cloudy

设置行索引index_col

写入sqlite文件

weather_2017.to_csv('./weather_2017.csv')

weather_2017.to_dict()

{'Date/Time': {0: '2012-01-01 00:00:00',
  1: '2012-01-01 01:00:00',
  2: '2012-01-01 02:00:00',
  3: '2012-01-01 03:00:00',
  4: '2012-01-01 04:00:00',
  5: '2012-01-01 05:00:00',
  6: '2012-01-01 06:00:00',
  7: '2012-01-01 07:00:00',
  8: '2012-01-01 08:00:00',
  9: '2012-01-01 09:00:00',
  10: '2012-01-01 10:00:00',
  11: '2012-01-01 11:00:00',
  12: '2012-01-01 12:00:00',
  13: '2012-01-01 13:00:00',
  14: '2012-01-01 14:00:00',
  15: '2012-01-01 15:00:00',
  16: '2012-01-01 16:00:00',
  17: '2012-01-01 17:00:00',
  18: '2012-01-01 18:00:00',
  19: '2012-01-01 19:00:00',
  20: '2012-01-01 20:00:00',
  21: '2012-01-01 21:00:00',
  22: '2012-01-01 22:00:00',
  23: '2012-01-01 23:00:00',
  24: '2012-01-02 00:00:00',
  25: '2012-01-02 01:00:00',
  26: '2012-01-02 02:00:00',
  27: '2012-01-02 03:00:00',
  28: '2012-01-02 04:00:00',
  29: '2012-01-02 05:00:00'},
 'Temp (C)': {0: -1.8,
  1: -1.8,
  2: -1.8,
  3: -1.5,
  4: -1.5,
  5: -1.4,
  6: -1.5,
  7: -1.4,
  8: -1.4,
  9: -1.3,
  10: -1.0,
  11: -0.5,
  12: -0.2,
  13: 0.2,
  14: 0.8,
  15: 1.8,
  16: 2.6,
  17: 3.0,
  18: 3.8,
  19: 3.1,
  20: 3.2,
  21: 4.0,
  22: 4.4,
  23: 5.3,
  24: 5.2,
  25: 4.6,
  26: 3.9,
  27: 3.7,
  28: 2.9,
  29: 2.6},
 'Dew Point Temp (C)': {0: -3.9,
  1: -3.7,
  2: -3.4,
  3: -3.2,
  4: -3.3,
  5: -3.3,
  6: -3.1,
  7: -3.6,
  8: -3.6,
  9: -3.1,
  10: -2.3,
  11: -2.1,
  12: -2.0,
  13: -1.7,
  14: -1.1,
  15: -0.4,
  16: -0.2,
  17: 0.0,
  18: 1.0,
  19: 1.3,
  20: 1.3,
  21: 1.7,
  22: 1.9,
  23: 2.0,
  24: 1.5,
  25: 0.0,
  26: -0.9,
  27: -1.5,
  28: -2.3,
  29: -2.3},
 'Rel Hum (%)': {0: 86,
  1: 87,
  2: 89,
  3: 88,
  4: 88,
  5: 87,
  6: 89,
  7: 85,
  8: 85,
  9: 88,
  10: 91,
  11: 89,
  12: 88,
  13: 87,
  14: 87,
  15: 85,
  16: 82,
  17: 81,
  18: 82,
  19: 88,
  20: 87,
  21: 85,
  22: 84,
  23: 79,
  24: 77,
  25: 72,
  26: 71,
  27: 69,
  28: 69,
  29: 70},
 'Wind Spd (km/h)': {0: 4,
  1: 4,
  2: 7,
  3: 6,
  4: 7,
  5: 9,
  6: 7,
  7: 7,
  8: 9,
  9: 15,
  10: 9,
  11: 7,
  12: 9,
  13: 13,
  14: 20,
  15: 22,
  16: 13,
  17: 13,
  18: 15,
  19: 15,
  20: 19,
  21: 20,
  22: 24,
  23: 30,
  24: 35,
  25: 39,
  26: 32,
  27: 33,
  28: 32,
  29: 32},
 'Visibility (km)': {0: 8.0,
  1: 8.0,
  2: 4.0,
  3: 4.0,
  4: 4.8,
  5: 6.4,
  6: 6.4,
  7: 8.0,
  8: 8.0,
  9: 4.0,
  10: 1.2,
  11: 4.0,
  12: 4.8,
  13: 4.8,
  14: 4.8,
  15: 6.4,
  16: 12.9,
  17: 16.1,
  18: 12.9,
  19: 12.9,
  20: 25.0,
  21: 25.0,
  22: 19.3,
  23: 25.0,
  24: 25.0,
  25: 25.0,
  26: 25.0,
  27: 25.0,
  28: 25.0,
  29: 25.0},
 'Stn Press (kPa)': {0: 101.24,
  1: 101.24,
  2: 101.26,
  3: 101.27,
  4: 101.23,
  5: 101.27,
  6: 101.29,
  7: 101.26,
  8: 101.23,
  9: 101.2,
  10: 101.15,
  11: 100.98,
  12: 100.79,
  13: 100.58,
  14: 100.31,
  15: 100.07,
  16: 99.93,
  17: 99.81,
  18: 99.74,
  19: 99.68,
  20: 99.5,
  21: 99.39,
  22: 99.32,
  23: 99.31,
  24: 99.26,
  25: 99.26,
  26: 99.26,
  27: 99.3,
  28: 99.26,
  29: 99.21},
 'Weather': {0: 'Fog',
  1: 'Fog',
  2: 'Freezing Drizzle,Fog',
  3: 'Freezing Drizzle,Fog',
  4: 'Fog',
  5: 'Fog',
  6: 'Fog',
  7: 'Fog',
  8: 'Fog',
  9: 'Fog',
  10: 'Fog',
  11: 'Fog',
  12: 'Fog',
  13: 'Fog',
  14: 'Fog',
  15: 'Fog',
  16: 'Mostly Cloudy',
  17: 'Cloudy',
  18: 'Rain',
  19: 'Rain',
  20: 'Cloudy',
  21: 'Cloudy',
  22: 'Rain Showers',
  23: 'Cloudy',
  24: 'Rain Showers',
  25: 'Cloudy',
  26: 'Mostly Cloudy',
  27: 'Mostly Cloudy',
  28: 'Mostly Cloudy',
  29: 'Mostly Cloudy'}}

weather_2017.to_json('./weather_2017.json')

weather_2017.to_html('./weahter_2017.html')

# 写入数据库
weather_2017.to_sql('Weather_2019', conn )

d:\1903\.venv\lib\site-packages\pandas\core\generic.py:2713: UserWarning: The spaces in these column names will not be changed. In pandas versions < 0.14, spaces were converted to underscores.
  method=method,

从mysql中读取 ,并写入mysql

!pip install pymysql

Collecting pymysql
  Using cached https://files.pythonhosted.org/packages/ed/39/15045ae46f2a123019aa968dfcba0396c161c20f855f11dea6796bcaae95/PyMySQL-0.9.3-py2.py3-none-any.whl
Installing collected packages: pymysql
Successfully installed pymysql-0.9.3

import pymysql

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='root', database='qiubai', charset='utf8')
conn

<pymysql.connections.Connection at 0xeb327b8>

qiushi = pd.read_sql('select * from qiushi limit  30', conn, index_col='index')
qiushi 

	id	name	headshot	content
index
0	315	放飞的柔情	https://pic.qiushibaike.com/system/avtnew/2720...	大学寝室姐妹里有一个广东妹子，纤细瘦高，两条美腿又细又长让人羡慕，可惜胸前除了两粒葡萄（巨峰...
1	316	烟若半夏	https://pic.qiushibaike.com/system/avtnew/3204...	前些年发生的事。姑奶奶坐火车去北京看病，她嫌弃卧铺车厢没人和她说话，就溜达去了硬座车厢，找了...
2	317	非死不可facebook	https://pic.qiushibaike.com/system/avtnew/2571...	寝室里一哥们，睡觉好梦游，一睡着就折腾人，起来说自己做梦了，有很多随从抬着轿子，还在大饭店吃...
3	318	超级大帅哥9	https://pic.qiushibaike.com/system/avtnew/3948...	有一次，在上厕所 我在用嘴吹气，把麦克风里面的灰尘吹掉，旁边的一个小屁孩。哥哥是不是屎烫嘴呀
4	319	锻炼身体只为等你	https://pic.qiushibaike.com/system/avtnew/2012...	知道糗百多人才，就想问下有没有肚子一受凉就拉肚子的，有没有好的方法求助，唉今天都拉了好几回了
5	320	道士下山会女神	https://pic.qiushibaike.com/system/avtnew/3020...	晚上老婆突然肚子疼，赶紧开车送医院，急诊医生检查后说，空调吹久了肚子受凉，让回家热敷保暖，再...
6	321	老巫婆～～	https://pic.qiushibaike.com/system/avtnew/3424...	老公最近不知道受啥刺激了，说要练成肌肉型男。买了一个臂力器，每天晚上练，练完就对着镜子秀他的...
7	322	骑舔大圣损污控	https://pic.qiushibaike.com/system/avtnew/1802...	以前游戏上网上经常有骂女的天天大姨妈，那时我就在想一个女的天天大姨妈一年下来是什么感觉呢？直...
8	323	蜀南熟男	https://pic.qiushibaike.com/system/avtnew/3816...	当代人真是对死亡一点敬畏都没有了，看到这样的工作竟然有一点点心动
9	324	蕅哦	https://pic.qiushibaike.com/system/avtnew/2459...	lz高中通校生，（上学期间）除了晚上都吃老妈做的饭＿＿＿＿＿＿＿＿＿有一天,老妈突然对lz ...
10	325	知一儿知一	https://pic.qiushibaike.com/system/avtnew/3355...	昨晚上我一女室友10点半了还没回宿舍、自习室也没找到她，我猜想她柔弱女子肯定是大晚上遇见坏人...
11	326	青青子衿忧我心	https://pic.qiushibaike.com/system/avtnew/3137...	班里有个小正太像个大人唉声叹气的，我关心问他咋啦。他背着手说:愁死人了，俗话说富不过三代，我...
12	327	鱼非鱼，鱼非鱼！	https://pic.qiushibaike.com/system/avtnew/1454...	练车我习惯一上车就关空调！结果直接关的压缩机！出风口没关！然后刚刚一个师弟跟教练说空调吹呢热...
13	328	二两小面加辣椒	https://static.qiushibaike.com/images/thumb/mi...	小时候暑假回老家，我爸爸给我买的一双人字拖被我堂哥穿走了，我和他说：穿坏了别丢，我修一修还能穿。
14	329	毛豆江湖	https://pic.qiushibaike.com/system/avtnew/767/...
15	330	狸土豆大人	https://pic.qiushibaike.com/system/avtnew/2521...	去古镇玩，路过摆摊算命的，看到有个小学生模样的孩子坐跟前，他问算命的大爷是不是什么都会算，大...
16	331	林晓九	https://pic.qiushibaike.com/system/avtnew/3007...	邻居有俩熊孩子总敲我们家门，找了家长几次也不管！今天又来敲 我就把他俩领楼下，指着一个泥坑教...
17	332	妄谈。	https://pic.qiushibaike.com/system/avtnew/3949...	一对夫妻是同一家企业的员工，平时工作都很忙。这天，丈夫感叹道：“上班最痛苦的事，莫过于老公和...
18	333	乖乖宝的胖子胖	https://pic.qiushibaike.com/system/avtnew/1827...	昨天下午出去办事，看到路边一美女在朝我招手。我心中狂喜，走到她跟前问：有什么事需要帮忙吗？美...
19	334	Sunner大树	https://pic.qiushibaike.com/system/avtnew/2191...	唱个歌送个这个什么意思
20	335	狗改不了喝酒	https://pic.qiushibaike.com/system/avtnew/3012...	酒店停车场，一大哥喝完酒发现轮胎瘪了一只，就叫保安帮忙卸下来装备胎，结果俩人忙乎了一个小时，...
21	336	巫女～素素	https://pic.qiushibaike.com/system/avtnew/3050...	姐夫电梯修理工一枚，平时私下里也接些私活，挣点零花钱。前天接了一个教堂里的私活，忙忙碌碌一天...
22	337	若风89	https://pic.qiushibaike.com/system/avtnew/3940...	1.有个同事，懒惯了，不用标点符号，直接（回车）心情很糟她的包掉了不知道老婆会不上吊…………...
23	338	段不了。	https://pic.qiushibaike.com/system/avtnew/3771...	小时候我喜欢我的同桌，以为那就是爱情，因为她身上总有一股羊肉串的味道，觉得好闻，mmp长大以...
24	339	Jack259	https://pic.qiushibaike.com/system/avtnew/1725...	开春了，晒晒今天的收获，哈哈
25	340	匿名用户	https://static.qiushibaike.com/images/thumb/an...	不知道算不算糗，我们要去提车了，，外人都觉得我过得挺近好的，但是，首付的钱基本上都是我从娘家...
26	341	O今生为你偷	https://pic.qiushibaike.com/system/avtnew/3939...	高中同学聚会，我老婆是班花，孩子5岁多木有办法丢也带了。进包厢刚寒暄完，当年情敌突然来一句:...
27	342	灵龙家的白灵	https://pic.qiushibaike.com/system/avtnew/3948...	一天，体育老师让女生跑六圈，女生跑了四圈就跑不动了。这时我对体育老师说：我有办法。我站到她们...
28	343	虚竹大力神	https://pic.qiushibaike.com/system/avtnew/3913...	嗨起来啊！
29	344	你不懂蛋蛋的忧伤	https://pic.qiushibaike.com/system/avtnew/458/...	皮皮豹，我们走

qiushi.to_sql('qiushi_1903', conn)

---------------------------------------------------------------------------

TypeError                                 Traceback (most recent call last)

d:\1903\.venv\lib\site-packages\pandas\io\sql.py in execute(self, *args, **kwargs)
   1594             else:
-> 1595                 cur.execute(*args)
   1596             return cur


d:\1903\.venv\lib\site-packages\pymysql\cursors.py in execute(self, query, args)
    167 
--> 168         query = self.mogrify(query, args)
    169 


d:\1903\.venv\lib\site-packages\pymysql\cursors.py in mogrify(self, query, args)
    146         if args is not None:
--> 147             query = query % self._escape_args(args, conn)
    148 


TypeError: not all arguments converted during string formatting


During handling of the above exception, another exception occurred:


DatabaseError                             Traceback (most recent call last)

<ipython-input-27-ddd777897f2e> in <module>
----> 1 qiushi.to_sql('qiushi_1903', conn)


d:\1903\.venv\lib\site-packages\pandas\core\generic.py in to_sql(self, name, con, schema, if_exists, index, index_label, chunksize, dtype, method)
   2711             chunksize=chunksize,
   2712             dtype=dtype,
-> 2713             method=method,
   2714         )
   2715 


d:\1903\.venv\lib\site-packages\pandas\io\sql.py in to_sql(frame, name, con, schema, if_exists, index, index_label, chunksize, dtype, method)
    516         chunksize=chunksize,
    517         dtype=dtype,
--> 518         method=method,
    519     )
    520 


d:\1903\.venv\lib\site-packages\pandas\io\sql.py in to_sql(self, frame, name, if_exists, index, index_label, schema, chunksize, dtype, method)
   1747             dtype=dtype,
   1748         )
-> 1749         table.create()
   1750         table.insert(chunksize, method)
   1751 


d:\1903\.venv\lib\site-packages\pandas\io\sql.py in create(self)
    639 
    640     def create(self):
--> 641         if self.exists():
    642             if self.if_exists == "fail":
    643                 raise ValueError(


d:\1903\.venv\lib\site-packages\pandas\io\sql.py in exists(self)
    626 
    627     def exists(self):
--> 628         return self.pd_sql.has_table(self.name, self.schema)
    629 
    630     def sql_schema(self):


d:\1903\.venv\lib\site-packages\pandas\io\sql.py in has_table(self, name, schema)
   1760         ).format(wld=wld)
   1761 
-> 1762         return len(self.execute(query, [name]).fetchall()) > 0
   1763 
   1764     def get_table(self, table_name, schema=None):


d:\1903\.venv\lib\site-packages\pandas\io\sql.py in execute(self, *args, **kwargs)
   1608                 "Execution failed on sql '{sql}': {exc}".format(sql=args[0], exc=exc)
   1609             )
-> 1610             raise_with_traceback(ex)
   1611 
   1612     @staticmethod


d:\1903\.venv\lib\site-packages\pandas\compat\__init__.py in raise_with_traceback(exc, traceback)
     42     if traceback == Ellipsis:
     43         _, _, traceback = sys.exc_info()
---> 44     raise exc.with_traceback(traceback)
     45 
     46 


d:\1903\.venv\lib\site-packages\pandas\io\sql.py in execute(self, *args, **kwargs)
   1593                 cur.execute(*args, **kwargs)
   1594             else:
-> 1595                 cur.execute(*args)
   1596             return cur
   1597         except Exception as exc:


d:\1903\.venv\lib\site-packages\pymysql\cursors.py in execute(self, query, args)
    166             pass
    167 
--> 168         query = self.mogrify(query, args)
    169 
    170         result = self._query(query)


d:\1903\.venv\lib\site-packages\pymysql\cursors.py in mogrify(self, query, args)
    145 
    146         if args is not None:
--> 147             query = query % self._escape_args(args, conn)
    148 
    149         return query


DatabaseError: Execution failed on sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;': not all arguments converted during string formatting

from sqlalchemy import create_engine

engine = create_engine('mysql+pymysql://root:root@localhost/qiubai?charset=utf8')
engine

Engine(mysql+pymysql://root:***@localhost/qiubai?charset=utf8)

qiushi.to_sql('qiushi_1903', engine)

d:\1903\.venv\lib\site-packages\pymysql\cursors.py:170: Warning: (1366, "Incorrect string value: '\\xD6\\xD0\\xB9\\xFA\\xB1\\xEA...' for column 'VARIABLE_VALUE' at row 484")
  result = self._query(query)

根据url获取网络上的数据

pd.read_csv('https://raw.githubusercontent.com/datasets/investor-flow-of-funds-us/master/data/weekly.csv')

	Date	Total Equity	Domestic Equity	World Equity	Hybrid	Total Bond	Taxable Bond	Municipal Bond	Total
0	2012-12-05	-7426	-6060	-1367	-74	5317	4210	1107	-2183
1	2012-12-12	-8783	-7520	-1263	123	1818	1598	219	-6842
2	2012-12-19	-5496	-5470	-26	-73	103	3472	-3369	-5466
3	2012-12-26	-4451	-4076	-375	550	2610	3333	-722	-1291
4	2013-01-02	-11156	-9622	-1533	-158	2383	2103	280	-8931
5	2013-01-09	14817	7995	6821	2888	9766	7311	2455	27471
6	2014-04-02	3155	938	2217	265	3379	3129	250	6799
7	2014-04-09	5761	2080	3681	1482	1609	1448	161	8852
8	2014-04-16	2286	634	1652	1186	633	604	29	4105
9	2014-04-23	3530	1392	2138	1239	1984	1453	531	6753
10	2014-04-30	-3890	-3996	106	759	888	559	329	-2242
11	2014-05-07	632	-2006	2639	-340	5493	4417	1076	5785
12	2014-05-14	-1079	-2321	1242	1188	4037	3141	897	4146
13	2014-05-21	697	-1790	2487	1216	2196	1398	798	4109
14	2014-05-28	-2453	-2603	150	1108	2041	1236	805	696
15	2014-06-04	2098	-1148	3246	1123	188	-470	658	3409
16	2014-06-11	1236	-1840	3075	1159	2112	1587	524	4506
17	2014-06-18	-922	-2204	1282	1060	4159	3740	419	4297
18	2014-06-25	-93	-1354	1262	1246	3256	2694	562	4409
19	2014-07-02	-7835	-8887	1052	636	2979	2704	276	-4220
20	2014-07-09	666	-1070	1736	1006	2721	3203	-482	4393
21	2014-07-30	118	-1171	1290	1024	1806	1119	687	2949
22	2014-08-06	-471	-3073	2602	-375	-8193	-8658	465	-9040
23	2014-08-13	320	-974	1294	496	1436	539	897	2252
24	2014-08-20	2671	738	1933	821	4999	4185	814	8490
25	2014-08-27	-577	-2199	1623	943	3655	2921	734	4021
26	2014-09-03	-4024	-5305	1281	544	2430	1768	661	-1050
27	2014-09-10	1257	-1291	2548	1055	1554	711	843	3866
28	2014-11-05	-32	-1634	1602	-176	5813	5284	529	5604
29	2014-11-12	1464	61	1403	963	3596	2703	893	6023
30	2014-11-19	-3010	-3622	611	99	2529	1758	771	-383
31	2014-11-25	-1175	-2044	869	-157	2590	1821	769	1258
32	2015-01-07	-3913	-5438	1525	-1057	-3403	-4729	1326	-8373
33	2015-01-14	1774	-37	1811	248	3549	2582	967	5572
34	2015-01-21	1267	856	411	790	1258	220	1038	3315
35	2015-01-28	4343	3455	888	1748	5964	4689	1275	12055
36	2015-02-04	4240	3536	703	793	3237	2274	963	8270
37	2015-02-11	1268	-27	1296	959	5862	5169	693	8089
38	2015-03-04	999	-1933	2932	528	4984	4309	675	6511
39	2015-03-11	3911	-7	3918	851	1298	999	298	6059
40	2015-03-18	1948	-1758	3706	912	452	258	194	3312
41	2015-03-25	-1167	-4478	3311	538	2404	1701	703	1775
42	2015-04-01	-1527	-3307	1780	720	-1296	-1392	96	-2103
43	2015-04-08	1906	-1321	3227	250	1719	1906	-187	3875