{
"cells": [
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# 6.10 pandas读写数据"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"列表类型经常被用于数据处理，而数据的存在形式主要有两种，一种是文件形式，一种是数据库形式。对于文本文件中数据，可以用遍历的方法简单的读取，对于其他格式的数据文件或存储在数据库中的数据，可以借助Pandas库来读取。"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Pandas是基于Numpy的一个开源库，提供了高性能和高可用性的数据结构用于解决数据分析问题，他纳入了大量的库和一些标准的数据模型，提供了可用于高效操作大型数据集的工具，是使Python成为强大而高效的数据分析工具的重要因素之一。"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Pandas兼容所有Python的数据类型，除此外，还支持两种数据结构： \n",
"一维数组Series \n",
"二维表格型数据结构DataFrame \n",
"本章，我们只介绍利用Pandas读取数据的相关知识。"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Pandas是第三方库，使用之前需要先安装，安装命令：\n",
"\n",
"`pip install pandas`"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"通常，Pandas的引用方式为：\n",
"```python\n",
"import pandas as pd\n",
"```"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Pandas输入输出API提供了对文本、二进制和结构化查询语言（SQL）等不同格式类型文件的读写函数，可以方便快速的读取本地文件，如csv、txt、json和html等文本文件、Excel文件以及关系型数据库中的数据。 \n",
"其主要方法如表6.2所示。\n",
"表 6.2 Pandas常用输入输出API"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"| 格式类型 | 数据描述 | 读 | 写 |\n",
"| --- | --- | --- | --- |\n",
"| 文本 | CSV | read_csv() | to_csv() |\n",
"| 文本 | JSON | read_json() | to_json() |\n",
"| 文本 | HTML | read_html() | to_html() |\n",
"| 二进制 | MS Excel | read_excel() | to_excel() |\n",
"| 二进制 | Python Pickle Format | read_pickle() | to_pickle() |\n",
"| SQL | SQL | read_sql() | to_sql() |"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"这些API可以方便的把各种类型的数据读取为Dataframe格式的数据，再用利用tolist()函数便可将其转为列表类型，这样就可以利用本章学习的方法进行数据分析和处理了。"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### 6.10.1 读Excel文件中数据"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"用Pandas可以读取Excel文件中的数据为Dataframe类型，Excel文件的读取主要应用read_excel()方法，使用时可能需要先用“pip install xlrd”安装xlrd模块。 \n",
"read_excel()方法大部分参数都有默认值，只需要设置少量的参数便可以完成大部分的数据读取工作。 \n",
"其主要参数及其意义如下：\n",
"```python\n",
"pd.read_excel(io, sheet_name=0, header=0, names=None, usecols=None, squeeze=False, converters=None, skiprows=None, nrows=None, skipfooter=0)\n",
"```"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"1. io：Excel的存储路径\n",
"2. sheet_name：要读取的工作表名称，默认读取第一个工作表。可以是整型数字、列表名或SheetN。整型数字：目标sheet所在的位置，以0为起始，比如sheet_name = 1代表第2个工作表。列表名：目标sheet的名称，中英文皆可。SheetN：代表第N个sheet，S要大写，注意与整型数字的区别。\n",
"3. header：用哪一行作列名。默认为0 ，如果设置为[0,1]，则表示将前两行作为多重索引。\n",
"4. names：自定义最终的列名。一般适用于Excel缺少列名，或者需要重新定义列名的情况。names的长度必须和Excel列长度一致，否则会报错。\n",
"5. index_col：用作索引的列\n",
"6. usecols：需要读取哪些列。可以使用整型，从0开始，如[0,2,3]；也可以使用Excel传统的列名A、B等字母，如\"A：C, E\" = \"A, B, C, E\"，注意两边都包括。usecols 可避免读取全量数据，而是以分析需求为导向选择特定数据，提高效率。\n",
"7. squeeze：当数据仅包含一列。squeeze为True时，返回Series，反之返回DataFrame。\n",
"8. converters：强制规定列数据类型，主要用途是保留以文本形式存储的数字。\n",
"pandas默认将文本类的数据读取为整型，converters 参数可以指定各列数据的类型，如converters = {'出货量':float, '月份':str }， 将“出货量”列数据类型规定为浮点数，“月份”列规定为字符串类型。\n",
"9. skiprows：跳过特定行。skiprows= n 跳过前n行； skiprows = [a, b, c] 跳过第a+1,b+1,c+1行（索引从0开始）。\n",
"10. nrows：需要读取的行数，nrows = n 读取前n行。\n",
"11. skipfooter： 跳过末尾行数，skipfooter = n 跳过末尾的n行。"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### 实例 6.6 读取Excel文件中的证券数据"
]
},
{
"cell_type": "code",
"execution_count": 2,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" 时间 ETF 华夏 博时 广发 券商 创业板\n",
"0 2018-01-15 4.265 4.560 1.650 1.736 0.920 1.634\n",
"1 2018-01-16 4.308 4.595 1.700 1.750 0.941 1.631\n",
"2 2018-01-17 4.295 4.590 1.699 1.740 0.973 1.639\n",
"3 2018-01-18 4.323 4.621 1.675 1.744 0.980 1.636\n",
"4 2018-01-19 4.335 4.632 1.683 1.740 1.002 1.630\n"
]
}
],
"source": [
"import pandas as pd\n",
" \n",
"data_df = pd.read_excel('images/ch6/6.6 stock.xlsx') # 读取数据为dataframe类型\n",
"print(data_df)"
]
},
{
"cell_type": "code",
"execution_count": 5,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"输出列表类型的表头\n",
" ['时间', 'ETF', '华夏', '博时', '广发', '券商', '创业板']\n",
"\n",
"输出列表类型的数据\n",
" [[Timestamp('2018-01-15 00:00:00'), 4.265, 4.56, 1.65, 1.736, 0.92, 1.634], [Timestamp('2018-01-16 00:00:00'), 4.308, 4.595, 1.7, 1.75, 0.941, 1.631], [Timestamp('2018-01-17 00:00:00'), 4.295, 4.59, 1.699, 1.74, 0.973, 1.639], [Timestamp('2018-01-18 00:00:00'), 4.323, 4.621, 1.675, 1.744, 0.98, 1.636], [Timestamp('2018-01-19 00:00:00'), 4.335, 4.632, 1.683, 1.74, 1.002, 1.63]]\n"
]
}
],
"source": [
"import pandas as pd\n",
" \n",
"data_df = pd.read_excel('images/ch6/6.6 stock.xlsx') # 读取数据为dataframe类型\n",
"title = data_df.columns.tolist() # dataframe数据中标题行转为列表类型\n",
"print('输出列表类型的表头\\n',title) # 输出列表类型数据\n",
"print()\n",
"\n",
"data_ls = data_df.values.tolist() # dataframe数据转为列表类型\n",
"print('输出列表类型的数据\\n',data_ls) # 输出列表类型数据"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"```python\n",
"[[Timestamp('2018-01-15 00:00:00'),4.265, 4.56, 1.65, 1.736, 0.92, 1.634], \n",
"[Timestamp('2018-01-16 00:00:00'), 4.308, 4.595, 1.7, 1.75, 0.941, 1.631], \n",
"[Timestamp('2018-01-17 00:00:00'), 4.295, 4.59, 1.699, 1.74, 0.973, 1.639], \n",
"[Timestamp('2018-01-18 00:00:00'), 4.323, 4.621, 1.675, 1.744, 0.98, 1.636], \n",
"[Timestamp('2018-01-19 00:00:00'), 4.335, 4.632, 1.683, 1.74, 1.002, 1.63]]\n",
"```"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"值得注意的是，与遍历方法读取文件不同，用缺省参数读取的数据中，数值类型的数据直接被转为数值型，可以直接参与数值运算和统计分析。 \n",
"日期读取为日期时间戳类型，可以用strftime()转为字符串。"
]
},
{
"cell_type": "code",
"execution_count": 6,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"输出列表类型的数据\n",
" [['2018-01-15', 4.265, 4.56, 1.65, 1.736, 0.92, 1.634], ['2018-01-16', 4.308, 4.595, 1.7, 1.75, 0.941, 1.631], ['2018-01-17', 4.295, 4.59, 1.699, 1.74, 0.973, 1.639], ['2018-01-18', 4.323, 4.621, 1.675, 1.744, 0.98, 1.636], ['2018-01-19', 4.335, 4.632, 1.683, 1.74, 1.002, 1.63]]\n"
]
}
],
"source": [
"import pandas as pd\n",
" \n",
"data_df = pd.read_excel('images/ch6/6.6 stock.xlsx') # 读取数据为dataframe类型\n",
"\n",
"data_ls = data_df.values.tolist() # dataframe数据转为列表类型\n",
"\n",
"for lst in data_ls:\n",
" lst[0] = lst[0].strftime(\"%Y-%m-%d\") # 日期时间格式化为年-月-日形式\n",
"print('输出列表类型的数据\\n', data_ls) # 输出列表类型数据"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"```python\n",
" [['2018-01-15', 4.265, 4.56, 1.65, 1.736, 0.92, 1.634], \n",
"['2018-01-16', 4.308, 4.595, 1.7, 1.75, 0.941, 1.631], \n",
"['2018-01-17', 4.295, 4.59, 1.699, 1.74, 0.973, 1.639], \n",
"['2018-01-18', 4.323, 4.621, 1.675, 1.744, 0.98, 1.636], \n",
"['2018-01-19', 4.335, 4.632, 1.683, 1.74, 1.002, 1.63]]\n",
"```"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"pip install openpyxl"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### 6.10.2 读文本文件中数据"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"读文本文件和csv文件进列表，对列表中的数据进行统计分析。将用常规分隔符分隔的文本文件读取到DataFrame可以使用read_csv()方法"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"其主要参数及意义如下：\n",
"```python\n",
"pandas.read_csv(filepath_or_buffer, sep='\\t', delimiter=None, header='infer', names=None, engine=None，encoding=None)\n",
"```"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"1. filepath_or_buffer：带路径文件名或URL，字符串类型。\n",
"2. sep：分隔符，缺省值为'\\t'，当文本中的分隔符不是制表符时，可用sep=’分隔符’来指定。Python可自动检测分隔符。\n",
"3. delimiter：参数sep的替代参数，缺省值为None。\n",
"4. header：整型或整型列表，用作列名的行号和数据的开头。\n",
"5. names：要使用的列名的列表，如果文件不包含标题行，则应显式传递header = None。\n",
"6. engine：使用解析器引擎，其值可为'c'或'python'。c引擎速度更快，而Python引擎目前功能更加完善。\n",
"7. encoding：默认None，编码在读/写时用UTF（例如'utf-8'）"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### 实例 6.7读取csv文件中的数据"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"<a href=\"images/ch6/6.7 score.csv\" target=\"_blank\">6.7 score.csv</a>"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"import pandas as pd\n",
" \n",
"score_df = pd.read_csv('images/ch6/6.7 score.csv',encoding='utf-8') # dataframe\n",
"print(score_df) # 查看数据格式\n"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"import pandas as pd\n",
" \n",
"score_df = pd.read_csv('images/ch6/6.7 score.csv',encoding='utf-8') # dataframe\n",
"title = score_df.columns.tolist() # dataframe数据中标题行转为列表类型\n",
"score_ls = [title] + score_df.values.tolist() # 转为列表类型\n",
"print('输出列表类型的数据\\n',score_ls) # 输出列表数据"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"```python\n",
"[['姓名', 'C语言', 'Java', 'Python', 'C#']\n",
"['罗明', 95, 96, 85, 63, 91], \n",
"['朱佳', 75, 93, 66, 85, 88], \n",
"['李思', 86, 76, 96, 93, 67], \n",
"['郑君', 88, 98, 76, 90, 89], \n",
"['王雪', 99, 96, 91, 88, 86]]\n",
"```"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### 6.3.3 读数据库中数据"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"在实际应用中，使用文本文件或Excel存储数据并不是最好的方式，我们能够对这些类型的文件中的数据能的操作非常有限，数据处理效率也不高，更常用的方式是将数据存储到数据库中，通过连接数据库进行相关操作。\n",
"目前应用最多的是关系型数据库，关系型数据库的主要构成是二维表。 \n",
"二维表包含多行多列，把一个表中的数据用Python表现出来，可以用一个列表表示多行，列表的每一个元素用一个元组表示二维表中的一行记录。"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"比如一个二维表包含ID、姓名、年龄、籍贯、薪水，可以用以下形式表示：\n",
"```python\n",
"[(1, '李明', 23, '吉林', 20000.00), \n",
"(2, '韩雷', 26, '湖北', 25000.00), \n",
"(3, '肖红', 30, '江西', 30000.00)]\n",
"```"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"这种表示方法无法直观的展示关系数据库的表结构，可以使用对象-关系映射 (ORM：Object-Relational Mapping)技术把关系数据库的表结构映射到对象上。在Python中，广泛应用的一个对象-关系映射框架是SQLAlchemy，这个框架可以为开发者提供高效的数据库访问设计和高性能的数据访问方法，实现了完整的企业级持久模型。"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"SQLAlchemy支持大部分主流数据库，如SQLite、MySQL、Postgres、Oracle、MS SQLServer 和 Firebird等。在使用之前，需要通过pip install sqlalchemy安装这个库。"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"SQLite是Python 内置的一个轻量级数据库，可以直接使用。使用其他数据库时，需要pip安装与数据库匹配的驱动，例如mysqlclient、 pymssql、 psycopg2、 cx-Oracle或 fdb等。只有安装数据库的驱动之后，才可以连接数据库对数据进行操作。本书以SQLite数据库作为范例进行讲解。"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"sqlalchemy.create_engine(*args, **kwargs)函数可被用于创建数据库引擎，数据库位置可用本地路径，也可用网络URL。"
]
},
{
"cell_type": "code",
"execution_count": 18,
"metadata": {},
"outputs": [],
"source": [
"from sqlalchemy import *\n",
"import pandas as pd\n",
" \n",
"# 定义元信息，绑定到引擎，test.db为数据库名，./表示当前路径。\n",
"engine = create_engine('sqlite:////images/ch6/6.8 test.db', echo=True)\n",
"metadata = MetaData(engine) # 绑定元信息\n"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Pandas中的read_sql()方法可以查询数据库中的数据并直接返回DateFrame，在方法的参数中可以传入SQL语句。read_sql()方法的主要参数及意义如下： \n",
"```python\n",
"pandas.read_sql(sql, con, index_col=None, coerce_float=True, params=None, parse_dates=None, columns=None, chunksize=None)\n",
"```"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"1. sql : 表名或查询语句\n",
"2. con : 连接数据库的引擎，一般可以用SQLAlchemy之类的模块创建\n",
"3. columns : 需要从表中查询的列名的列表"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### 实例 6.8 读取SQLite数据库中的数据"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"SQLite数据库中文件“6.8 test.db”的score表中存储了学生的成绩数据，请将数据库中的数据读入到列表中。"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"[6.8 test.db](images/ch6/6.8 test.db)"
]
},
{
"cell_type": "code",
"execution_count": 20,
"metadata": {
"scrolled": true
},
"outputs": [
{
"ename": "OperationalError",
"evalue": "(sqlite3.OperationalError) unable to open database file\n(Background on this error at: https://sqlalche.me/e/14/e3q8)",
"output_type": "error",
"traceback": [
"\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
"\u001b[0;31mOperationalError\u001b[0m Traceback (most recent call last)",
"\u001b[0;32m~/.virtualenvs/basenv/lib/python3.7/site-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36m_wrap_pool_connect\u001b[0;34m(self, fn, connection)\u001b[0m\n\u001b[1;32m 3279\u001b[0m \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 3280\u001b[0;31m \u001b[0;32mreturn\u001b[0m \u001b[0mfn\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m 3281\u001b[0m \u001b[0;32mexcept\u001b[0m \u001b[0mdialect\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mdbapi\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mError\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0me\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
"\u001b[0;32m~/.virtualenvs/basenv/lib/python3.7/site-packages/sqlalchemy/pool/base.py\u001b[0m in \u001b[0;36mconnect\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m 309\u001b[0m \"\"\"\n\u001b[0;32m--> 310\u001b[0;31m \u001b[0;32mreturn\u001b[0m \u001b[0m_ConnectionFairy\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_checkout\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m 311\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
"\u001b[0;32m~/.virtualenvs/basenv/lib/python3.7/site-packages/sqlalchemy/pool/base.py\u001b[0m in \u001b[0;36m_checkout\u001b[0;34m(cls, pool, threadconns, fairy)\u001b[0m\n\u001b[1;32m 867\u001b[0m \u001b[0;32mif\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0mfairy\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 868\u001b[0;31m \u001b[0mfairy\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0m_ConnectionRecord\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcheckout\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mpool\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m 869\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
"\u001b[0;32m~/.virtualenvs/basenv/lib/python3.7/site-packages/sqlalchemy/pool/base.py\u001b[0m in \u001b[0;36mcheckout\u001b[0;34m(cls, pool)\u001b[0m\n\u001b[1;32m 475\u001b[0m \u001b[0;32mdef\u001b[0m \u001b[0mcheckout\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mcls\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mpool\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 476\u001b[0;31m \u001b[0mrec\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mpool\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_do_get\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m 477\u001b[0m \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
"\u001b[0;32m~/.virtualenvs/basenv/lib/python3.7/site-packages/sqlalchemy/pool/impl.py\u001b[0m in \u001b[0;36m_do_get\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m 255\u001b[0m \u001b[0;32mdef\u001b[0m \u001b[0m_do_get\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 256\u001b[0;31m \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_create_connection\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m 257\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
"\u001b[0;32m~/.virtualenvs/basenv/lib/python3.7/site-packages/sqlalchemy/pool/base.py\u001b[0m in \u001b[0;36m_create_connection\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m 255\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 256\u001b[0;31m \u001b[0;32mreturn\u001b[0m \u001b[0m_ConnectionRecord\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m 257\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
"\u001b[0;32m~/.virtualenvs/basenv/lib/python3.7/site-packages/sqlalchemy/pool/base.py\u001b[0m in \u001b[0;36m__init__\u001b[0;34m(self, pool, connect)\u001b[0m\n\u001b[1;32m 370\u001b[0m \u001b[0;32mif\u001b[0m \u001b[0mconnect\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 371\u001b[0;31m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m__connect\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m 372\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mfinalize_callback\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mdeque\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
"\u001b[0;32m~/.virtualenvs/basenv/lib/python3.7/site-packages/sqlalchemy/pool/base.py\u001b[0m in \u001b[0;36m__connect\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m 665\u001b[0m \u001b[0;32mwith\u001b[0m \u001b[0mutil\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0msafe_reraise\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 666\u001b[0;31m \u001b[0mpool\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mlogger\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mdebug\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"Error on connect(): %s\"\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0me\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m 667\u001b[0m \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
"\u001b[0;32m~/.virtualenvs/basenv/lib/python3.7/site-packages/sqlalchemy/util/langhelpers.py\u001b[0m in \u001b[0;36m__exit__\u001b[0;34m(self, type_, value, traceback)\u001b[0m\n\u001b[1;32m 71\u001b[0m \u001b[0mexc_value\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 72\u001b[0;31m \u001b[0mwith_traceback\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mexc_tb\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m 73\u001b[0m )\n",
"\u001b[0;32m~/.virtualenvs/basenv/lib/python3.7/site-packages/sqlalchemy/util/compat.py\u001b[0m in \u001b[0;36mraise_\u001b[0;34m(***failed resolving arguments***)\u001b[0m\n\u001b[1;32m 206\u001b[0m \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 207\u001b[0;31m \u001b[0;32mraise\u001b[0m \u001b[0mexception\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m 208\u001b[0m \u001b[0;32mfinally\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
"\u001b[0;32m~/.virtualenvs/basenv/lib/python3.7/site-packages/sqlalchemy/pool/base.py\u001b[0m in \u001b[0;36m__connect\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m 660\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mstarttime\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mtime\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mtime\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 661\u001b[0;31m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mdbapi_connection\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mconnection\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mpool\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_invoke_creator\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m 662\u001b[0m \u001b[0mpool\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mlogger\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mdebug\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"Created new connection %r\"\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mconnection\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
"\u001b[0;32m~/.virtualenvs/basenv/lib/python3.7/site-packages/sqlalchemy/engine/create.py\u001b[0m in \u001b[0;36mconnect\u001b[0;34m(connection_record)\u001b[0m\n\u001b[1;32m 589\u001b[0m \u001b[0;32mreturn\u001b[0m \u001b[0mconnection\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 590\u001b[0;31m \u001b[0;32mreturn\u001b[0m \u001b[0mdialect\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mconnect\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m*\u001b[0m\u001b[0mcargs\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mcparams\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m 591\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
"\u001b[0;32m~/.virtualenvs/basenv/lib/python3.7/site-packages/sqlalchemy/engine/default.py\u001b[0m in \u001b[0;36mconnect\u001b[0;34m(self, *cargs, **cparams)\u001b[0m\n\u001b[1;32m 596\u001b[0m \u001b[0;31m# inherits the docstring from interfaces.Dialect.connect\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 597\u001b[0;31m \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mdbapi\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mconnect\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m*\u001b[0m\u001b[0mcargs\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mcparams\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m 598\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
"\u001b[0;31mOperationalError\u001b[0m: unable to open database file",
"\nThe above exception was the direct cause of the following exception:\n",
"\u001b[0;31mOperationalError\u001b[0m Traceback (most recent call last)",
"\u001b[0;32m/tmp/ipykernel_391/2569192401.py\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m 4\u001b[0m \u001b[0;31m# 定义引擎，6.8 test.db为数据库名，./表示当前路径\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 5\u001b[0m \u001b[0mengine\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mcreate_engine\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'sqlite:////images/ch6/6.8 test.db'\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;31m# , echo=True)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 6\u001b[0;31m \u001b[0mscore_df\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mpd\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mread_sql\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'score'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mengine\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;31m# 从score表读数据dataframe\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m 7\u001b[0m \u001b[0mprint\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mscore_df\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;31m# 查看输出dataframe格式数据\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
"\u001b[0;32m~/.virtualenvs/basenv/lib/python3.7/site-packages/pandas/io/sql.py\u001b[0m in \u001b[0;36mread_sql\u001b[0;34m(sql, con, index_col, coerce_float, params, parse_dates, columns, chunksize)\u001b[0m\n\u001b[1;32m 632\u001b[0m \u001b[0mcoerce_float\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mcoerce_float\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 633\u001b[0m \u001b[0mparse_dates\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mparse_dates\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 634\u001b[0;31m \u001b[0mchunksize\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mchunksize\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m 635\u001b[0m )\n\u001b[1;32m 636\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
"\u001b[0;32m~/.virtualenvs/basenv/lib/python3.7/site-packages/pandas/io/sql.py\u001b[0m in \u001b[0;36mread_query\u001b[0;34m(self, sql, index_col, coerce_float, parse_dates, params, chunksize, dtype)\u001b[0m\n\u001b[1;32m 1577\u001b[0m \u001b[0margs\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0m_convert_params\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0msql\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparams\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 1578\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1579\u001b[0;31m \u001b[0mresult\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m*\u001b[0m\u001b[0margs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m 1580\u001b[0m \u001b[0mcolumns\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mresult\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mkeys\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 1581\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
"\u001b[0;32m~/.virtualenvs/basenv/lib/python3.7/site-packages/pandas/io/sql.py\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, *args, **kwargs)\u001b[0m\n\u001b[1;32m 1422\u001b[0m \u001b[0;32mdef\u001b[0m \u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m*\u001b[0m\u001b[0margs\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 1423\u001b[0m \u001b[0;34m\"\"\"Simple passthrough to SQLAlchemy connectable\"\"\"\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1424\u001b[0;31m \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mconnectable\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecution_options\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m*\u001b[0m\u001b[0margs\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m 1425\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 1426\u001b[0m def read_table(\n",
"\u001b[0;32m<string>\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, statement, *multiparams, **params)\u001b[0m\n",
"\u001b[0;32m~/.virtualenvs/basenv/lib/python3.7/site-packages/sqlalchemy/util/deprecations.py\u001b[0m in \u001b[0;36mwarned\u001b[0;34m(fn, *args, **kwargs)\u001b[0m\n\u001b[1;32m 399\u001b[0m \u001b[0;32mif\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0mskip_warning\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 400\u001b[0m \u001b[0m_warn_with_version\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mmessage\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mversion\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mwtype\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstacklevel\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;36m3\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 401\u001b[0;31m \u001b[0;32mreturn\u001b[0m \u001b[0mfn\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m*\u001b[0m\u001b[0margs\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m 402\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 403\u001b[0m \u001b[0mdoc\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mfunc\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m__doc__\u001b[0m \u001b[0;32mis\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0;32mNone\u001b[0m \u001b[0;32mand\u001b[0m \u001b[0mfunc\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m__doc__\u001b[0m \u001b[0;32mor\u001b[0m \u001b[0;34m\"\"\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
"\u001b[0;32m~/.virtualenvs/basenv/lib/python3.7/site-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, statement, *multiparams, **params)\u001b[0m\n\u001b[1;32m 3173\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 3174\u001b[0m \"\"\"\n\u001b[0;32m-> 3175\u001b[0;31m \u001b[0mconnection\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mconnect\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mclose_with_result\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mTrue\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m 3176\u001b[0m \u001b[0;32mreturn\u001b[0m \u001b[0mconnection\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m*\u001b[0m\u001b[0mmultiparams\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mparams\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 3177\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
"\u001b[0;32m~/.virtualenvs/basenv/lib/python3.7/site-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36mconnect\u001b[0;34m(self, close_with_result)\u001b[0m\n\u001b[1;32m 3232\u001b[0m \"\"\"\n\u001b[1;32m 3233\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 3234\u001b[0;31m \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_connection_cls\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mclose_with_result\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mclose_with_result\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m 3235\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 3236\u001b[0m @util.deprecated(\n",
"\u001b[0;32m~/.virtualenvs/basenv/lib/python3.7/site-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36m__init__\u001b[0;34m(self, engine, connection, close_with_result, _branch_from, _execution_options, _dispatch, _has_events, _allow_revalidate)\u001b[0m\n\u001b[1;32m 94\u001b[0m \u001b[0mconnection\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 95\u001b[0m \u001b[0;32mif\u001b[0m \u001b[0mconnection\u001b[0m \u001b[0;32mis\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 96\u001b[0;31m \u001b[0;32melse\u001b[0m \u001b[0mengine\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mraw_connection\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m 97\u001b[0m )\n\u001b[1;32m 98\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
"\u001b[0;32m~/.virtualenvs/basenv/lib/python3.7/site-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36mraw_connection\u001b[0;34m(self, _connection)\u001b[0m\n\u001b[1;32m 3311\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 3312\u001b[0m \"\"\"\n\u001b[0;32m-> 3313\u001b[0;31m \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_wrap_pool_connect\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mpool\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mconnect\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0m_connection\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m 3314\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 3315\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
"\u001b[0;32m~/.virtualenvs/basenv/lib/python3.7/site-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36m_wrap_pool_connect\u001b[0;34m(self, fn, connection)\u001b[0m\n\u001b[1;32m 3282\u001b[0m \u001b[0;32mif\u001b[0m \u001b[0mconnection\u001b[0m \u001b[0;32mis\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 3283\u001b[0m Connection._handle_dbapi_exception_noconnection(\n\u001b[0;32m-> 3284\u001b[0;31m \u001b[0me\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mdialect\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m 3285\u001b[0m )\n\u001b[1;32m 3286\u001b[0m \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
"\u001b[0;32m~/.virtualenvs/basenv/lib/python3.7/site-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36m_handle_dbapi_exception_noconnection\u001b[0;34m(cls, e, dialect, engine)\u001b[0m\n\u001b[1;32m 2116\u001b[0m \u001b[0;32melif\u001b[0m \u001b[0mshould_wrap\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 2117\u001b[0m util.raise_(\n\u001b[0;32m-> 2118\u001b[0;31m \u001b[0msqlalchemy_exception\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mwith_traceback\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mexc_info\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m2\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mfrom_\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0me\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m 2119\u001b[0m )\n\u001b[1;32m 2120\u001b[0m \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
"\u001b[0;32m~/.virtualenvs/basenv/lib/python3.7/site-packages/sqlalchemy/util/compat.py\u001b[0m in \u001b[0;36mraise_\u001b[0;34m(***failed resolving arguments***)\u001b[0m\n\u001b[1;32m 205\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 206\u001b[0m \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 207\u001b[0;31m \u001b[0;32mraise\u001b[0m \u001b[0mexception\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m 208\u001b[0m \u001b[0;32mfinally\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 209\u001b[0m \u001b[0;31m# credit to\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
"\u001b[0;32m~/.virtualenvs/basenv/lib/python3.7/site-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36m_wrap_pool_connect\u001b[0;34m(self, fn, connection)\u001b[0m\n\u001b[1;32m 3278\u001b[0m \u001b[0mdialect\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mdialect\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 3279\u001b[0m \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 3280\u001b[0;31m \u001b[0;32mreturn\u001b[0m \u001b[0mfn\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m 3281\u001b[0m \u001b[0;32mexcept\u001b[0m \u001b[0mdialect\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mdbapi\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mError\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0me\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 3282\u001b[0m \u001b[0;32mif\u001b[0m \u001b[0mconnection\u001b[0m \u001b[0;32mis\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
"\u001b[0;32m~/.virtualenvs/basenv/lib/python3.7/site-packages/sqlalchemy/pool/base.py\u001b[0m in \u001b[0;36mconnect\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m 308\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 309\u001b[0m \"\"\"\n\u001b[0;32m--> 310\u001b[0;31m \u001b[0;32mreturn\u001b[0m \u001b[0m_ConnectionFairy\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_checkout\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m 311\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 312\u001b[0m \u001b[0;32mdef\u001b[0m \u001b[0m_return_conn\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mrecord\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
"\u001b[0;32m~/.virtualenvs/basenv/lib/python3.7/site-packages/sqlalchemy/pool/base.py\u001b[0m in \u001b[0;36m_checkout\u001b[0;34m(cls, pool, threadconns, fairy)\u001b[0m\n\u001b[1;32m 866\u001b[0m \u001b[0;32mdef\u001b[0m \u001b[0m_checkout\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mcls\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mpool\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mthreadconns\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mNone\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mfairy\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mNone\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 867\u001b[0m \u001b[0;32mif\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0mfairy\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 868\u001b[0;31m \u001b[0mfairy\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0m_ConnectionRecord\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcheckout\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mpool\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m 869\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 870\u001b[0m \u001b[0mfairy\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_pool\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mpool\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
"\u001b[0;32m~/.virtualenvs/basenv/lib/python3.7/site-packages/sqlalchemy/pool/base.py\u001b[0m in \u001b[0;36mcheckout\u001b[0;34m(cls, pool)\u001b[0m\n\u001b[1;32m 474\u001b[0m \u001b[0;34m@\u001b[0m\u001b[0mclassmethod\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 475\u001b[0m \u001b[0;32mdef\u001b[0m \u001b[0mcheckout\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mcls\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mpool\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 476\u001b[0;31m \u001b[0mrec\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mpool\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_do_get\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m 477\u001b[0m \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 478\u001b[0m \u001b[0mdbapi_connection\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mrec\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mget_connection\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
"\u001b[0;32m~/.virtualenvs/basenv/lib/python3.7/site-packages/sqlalchemy/pool/impl.py\u001b[0m in \u001b[0;36m_do_get\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m 254\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 255\u001b[0m \u001b[0;32mdef\u001b[0m \u001b[0m_do_get\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 256\u001b[0;31m \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_create_connection\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m 257\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 258\u001b[0m \u001b[0;32mdef\u001b[0m \u001b[0mrecreate\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
"\u001b[0;32m~/.virtualenvs/basenv/lib/python3.7/site-packages/sqlalchemy/pool/base.py\u001b[0m in \u001b[0;36m_create_connection\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m 254\u001b[0m \u001b[0;34m\"\"\"Called by subclasses to create a new ConnectionRecord.\"\"\"\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 255\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 256\u001b[0;31m \u001b[0;32mreturn\u001b[0m \u001b[0m_ConnectionRecord\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m 257\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 258\u001b[0m \u001b[0;32mdef\u001b[0m \u001b[0m_invalidate\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mconnection\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexception\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mNone\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0m_checkin\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mTrue\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
"\u001b[0;32m~/.virtualenvs/basenv/lib/python3.7/site-packages/sqlalchemy/pool/base.py\u001b[0m in \u001b[0;36m__init__\u001b[0;34m(self, pool, connect)\u001b[0m\n\u001b[1;32m 369\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m__pool\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mpool\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 370\u001b[0m \u001b[0;32mif\u001b[0m \u001b[0mconnect\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 371\u001b[0;31m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m__connect\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m 372\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mfinalize_callback\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mdeque\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 373\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
"\u001b[0;32m~/.virtualenvs/basenv/lib/python3.7/site-packages/sqlalchemy/pool/base.py\u001b[0m in \u001b[0;36m__connect\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m 664\u001b[0m \u001b[0;32mexcept\u001b[0m \u001b[0mException\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0me\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 665\u001b[0m \u001b[0;32mwith\u001b[0m \u001b[0mutil\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0msafe_reraise\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 666\u001b[0;31m \u001b[0mpool\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mlogger\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mdebug\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"Error on connect(): %s\"\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0me\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m 667\u001b[0m \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 668\u001b[0m \u001b[0;31m# in SQLAlchemy 1.4 the first_connect event is not used by\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
"\u001b[0;32m~/.virtualenvs/basenv/lib/python3.7/site-packages/sqlalchemy/util/langhelpers.py\u001b[0m in \u001b[0;36m__exit__\u001b[0;34m(self, type_, value, traceback)\u001b[0m\n\u001b[1;32m 70\u001b[0m compat.raise_(\n\u001b[1;32m 71\u001b[0m \u001b[0mexc_value\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 72\u001b[0;31m \u001b[0mwith_traceback\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mexc_tb\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m 73\u001b[0m )\n\u001b[1;32m 74\u001b[0m \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
"\u001b[0;32m~/.virtualenvs/basenv/lib/python3.7/site-packages/sqlalchemy/util/compat.py\u001b[0m in \u001b[0;36mraise_\u001b[0;34m(***failed resolving arguments***)\u001b[0m\n\u001b[1;32m 205\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 206\u001b[0m \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 207\u001b[0;31m \u001b[0;32mraise\u001b[0m \u001b[0mexception\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m 208\u001b[0m \u001b[0;32mfinally\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 209\u001b[0m \u001b[0;31m# credit to\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
"\u001b[0;32m~/.virtualenvs/basenv/lib/python3.7/site-packages/sqlalchemy/pool/base.py\u001b[0m in \u001b[0;36m__connect\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m 659\u001b[0m \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 660\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mstarttime\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mtime\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mtime\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 661\u001b[0;31m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mdbapi_connection\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mconnection\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mpool\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_invoke_creator\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m 662\u001b[0m \u001b[0mpool\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mlogger\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mdebug\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"Created new connection %r\"\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mconnection\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 663\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mfresh\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;32mTrue\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
"\u001b[0;32m~/.virtualenvs/basenv/lib/python3.7/site-packages/sqlalchemy/engine/create.py\u001b[0m in \u001b[0;36mconnect\u001b[0;34m(connection_record)\u001b[0m\n\u001b[1;32m 588\u001b[0m \u001b[0;32mif\u001b[0m \u001b[0mconnection\u001b[0m \u001b[0;32mis\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 589\u001b[0m \u001b[0;32mreturn\u001b[0m \u001b[0mconnection\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 590\u001b[0;31m \u001b[0;32mreturn\u001b[0m \u001b[0mdialect\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mconnect\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m*\u001b[0m\u001b[0mcargs\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mcparams\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m 591\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 592\u001b[0m \u001b[0mcreator\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mpop_kwarg\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"creator\"\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mconnect\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
"\u001b[0;32m~/.virtualenvs/basenv/lib/python3.7/site-packages/sqlalchemy/engine/default.py\u001b[0m in \u001b[0;36mconnect\u001b[0;34m(self, *cargs, **cparams)\u001b[0m\n\u001b[1;32m 595\u001b[0m \u001b[0;32mdef\u001b[0m \u001b[0mconnect\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m*\u001b[0m\u001b[0mcargs\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mcparams\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 596\u001b[0m \u001b[0;31m# inherits the docstring from interfaces.Dialect.connect\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 597\u001b[0;31m \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mdbapi\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mconnect\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m*\u001b[0m\u001b[0mcargs\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mcparams\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m 598\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 599\u001b[0m \u001b[0;32mdef\u001b[0m \u001b[0mcreate_connect_args\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0murl\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
"\u001b[0;31mOperationalError\u001b[0m: (sqlite3.OperationalError) unable to open database file\n(Background on this error at: https://sqlalche.me/e/14/e3q8)"
]
}
],
"source": [
"from sqlalchemy import *\n",
"import pandas as pd\n",
" \n",
"# 定义引擎，6.8 test.db为数据库名，./表示当前路径\n",
"engine = create_engine('sqlite:////images/ch6/6.8 test.db') # , echo=True)\n",
"score_df = pd.read_sql('score', engine) # 从score表读数据dataframe\n",
"print(score_df) # 查看输出dataframe格式数据"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"```python\n",
" 姓名 C语言 Java Python C# C++\n",
"0 罗明 95 96 85 63 91\n",
"1 朱佳 75 93 66 85 88\n",
"2 李思 86 76 96 93 67\n",
"3 郑君 88 98 76 90 89\n",
"4 王雪 99 96 91 88 86\n",
"```"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"from sqlalchemy import *\n",
"import pandas as pd\n",
" \n",
"# 定义引擎，6.8 test.db为数据库名，./表示当前路径\n",
"engine = create_engine('images/ch6/6.8 test.db') # , echo=True)\n",
"score_df = pd.read_sql('score', engine) # 从score表读数据dataframe\n",
"\n",
"title = score_df.columns.tolist() # dataframe数据转为列表类型\n",
"score_ls = score_df.values.tolist() # dataframe数据转为列表类型\n",
"print('输出列表类型的数据:\\n', [title] + score_ls) # 输出转为列表的数据"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"```python\n",
"[['姓名', 'C语言', 'Java', 'Python', 'C#', 'C++'], \n",
" ['罗明', 95, 96, 85, 63, 91], \n",
" ['朱佳', 75, 93, 66, 85, 88], \n",
" ['李思', 86, 76, 96, 93, 67], \n",
" ['郑君', 88, 98, 76, 90, 89], \n",
" ['王雪', 99, 96, 91, 88, 86]]\n",
"```"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": []
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"pip install sqlalchemy"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"类似的方法，也可以从json格式的文件中读取数据到列表："
]
},
{
"cell_type": "code",
"execution_count": 21,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"输出列表类型的数据:\n",
" [['姓名', 'C语言', 'Java', 'Python', 'C#', 'C++'], ['罗明', 95, 96, 85, 63, 91], ['朱佳', 75, 93, 66, 85, 88], ['李思', 86, 76, 96, 93, 67], ['郑君', 88, 98, 76, 90, 89], ['王雪', 99, 96, 91, 88, 86]]\n"
]
}
],
"source": [
"import pandas as pd\n",
" \n",
"score_df = pd.read_json('images/ch6/6.8 scoreTest.json', encoding='utf-8') \n",
"title = score_df.columns.tolist() # dataframe数据转为列表类型\n",
"score_ls = score_df.values.tolist() # dataframe数据转为列表类型\n",
"print('输出列表类型的数据:\\n', [title] + score_ls) # 输出转为列表的数据"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### pandas 数据显示"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"若csv文件的某列中有逗号，一般会将该列数据放到一对比引号中。pandas读csv文件时，可以将引号中的数据自动处理为一个元素，用pandas处理此类数据，可以避免用切分方法导致的误切分。"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"<a href=\"images/ch6/winemag-data.csv\" target=\"_blank\">winemag-data.csv</a>"
]
},
{
"cell_type": "code",
"execution_count": 22,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"(12973, 6)\n",
" number country description points \\\n",
"0 0 Italy Aromas include tropical fruit, broom, brimston... 87 \n",
"1 10 US Soft, supple plum envelopes an oaky structure ... 87 \n",
"2 20 US Ripe aromas of dark berries mingle with ample ... 87 \n",
"3 30 France Red cherry fruit comes laced with light tannin... 86 \n",
"4 40 Italy Catarratto is one of Sicily's most widely farm... 86 \n",
"\n",
" price province \n",
"0 13.0 Sicily & Sardinia \n",
"1 19.0 California \n",
"2 23.0 Virginia \n",
"3 15.0 Beaujolais \n",
"4 17.0 Sicily & Sardinia \n"
]
}
],
"source": [
"import pandas as pd\n",
"\n",
"wine_reviews = pd.read_csv(\"images/ch6/winemag-data.csv\")\n",
"print(wine_reviews.shape) # ((12973, 6)\n",
"pd.set_option('display.max_columns', None) # 显示所有列\n",
"pd.set_option('display.max_rows', None) # 显示所有行\n",
"pd.set_option('display.width', None) # 显示宽度是无限\n",
"print(wine_reviews.head()) # 返回数据的前5行"
]
},
{
"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.5"
}
},
"nbformat": 4,
"nbformat_minor": 4
}
