如何在Colab中使用SQL
如今，编码测试在数据科学面试过程中几乎是标准的。
作为一名数据科学招聘经理，我发现一个20-30分钟的现场编码测试和一些准备好的任务能够有效地识别那些能够胜任职位的候选人。
google colab［https://colab.research.google.com/notebooks/intro.ipynb？utm_source=scs-index］是各种离线和实时数据科学编码的优秀工具，因为它熟悉的笔记本环境和并且方便共享。但是colab几乎只限于python（还有一些黑客攻击）。
根据我个人的经验，sql是成为一名成功的数据科学家的关键技能。深度学习是很好的，但是如果你不能编写sql查询，你可能永远都会被你的猫对狗分类器所困扰。
因此，我开始寻找进行实时sql编码测试的方法，瞧！我偶然发现了这种在colab（或任何python环境）中运行sql的简单方法，实际上只有2个helper函数。
在本文中，我将介绍两个python函数，我们可以使用它们在google colab中设置和运行sql，具体如下：
在google colab中创建一个数据库并将数据帧上传到该数据库中的一个表中
在googlecolab中对该数据库和表编写和执行sql查询。
所有代码都可以在我的github帐户上找到。你也可以直接在谷歌colab上打开代码， 单击此链接：
https://colab.research.google.com/github/stephenleo/medium-python-hacks/blob/main/02_sql_on_colab/main.ipynb
sqlite
失败是成功之母
在这次搜索之前，我只隐约听说过sqlite，但从未需要深入研究它。
sqlite…实现了一个小型、快速、自包含、高可靠性、功能齐全的sql数据库引擎。
sqlite是世界上使用最多的数据库引擎。sqlite内置于所有手机和大多数计算机中，并捆绑在人们每天使用的应用程序中。
听起来很有希望！sqlite的工作原理是创建一个本地的.db文件，我们可以像普通的sql数据库一样连接到该文件。然后我们可以在这个.db文件上创建表、上传数据和查询数据。
另外，用于创建sqlite并与之交互的python模块（sqlite3）也是python标准库的一部分。所以它在colab上是开箱即用的。
首先，让我们导入sqlite3和pandas模块，它们是我们任务的唯一需求
import sqlite3
import pandas as pd
函数1：将dataframe转换为sql db表 我们的第一个任务是创建一个数据库，并将一个数据帧上传到该数据库中的一个表中。
下面的代码将其作为一个函数实现，我将逐步介绍。该函数将输入数据帧、表名称和数据库名称（.db）作为输入，并运行以下步骤。
设置一些日志以跟踪函数的执行
查找数据框中的所有列。这是必要的，因为我们需要在创建表和将数据上载到表时提供此信息。
连接到.db文件（如果存在）。如果文件不存在，请在本地计算机上创建新文件。
在.db文件中创建一个表，我们在上一步中连接到该表（或刚刚创建）
将 input_df 中的数据行上传到我们在上一步中刚刚创建的表中
提交更改并关闭与数据库的连接
def pd_to_sqldb(input_df: pd.dataframe,
table_name: str,
db_name: str = 'default.db') -> none:
'''
取一个数据帧' input_df '并将其上传到' table_name ' sqlite表
args:
input_df (pd.dataframe): 包含要上传到sqlite的数据的数据帧
table_name (str): 要上传的sqlite表的名称
db_name (str, optional): 创建表的sqlite数据库的名称。
默认为“default.db”
'''
# 步骤1:设置本地日志
import logging
logging.basicconfig(level=logging.info,
format='%(asctime)s %(levelname)s: %(message)s',
datefmt='%y-%m-%d %h:%m:%s')
# 步骤2:在数据帧中查找列
cols = input_df.columns
cols_string = ','.join(cols)
val_wildcard_string = ','.join(['?'] * len(cols))
# 步骤3:如果db文件存在，连接到它，否则创建一个新文件
con = sqlite3.connect(db_name)
cur = con.cursor()
logging.info(f'sql db {db_name} created')
# 步骤4:创建表
sql_string = fcreate table {table_name} ({cols_string});
cur.execute(sql_string)
logging.info(f'sql table {table_name} created with {len(cols)} columns')
# 步骤5:上传数据帧
rows_to_upload = input_df.to_dict(orient='split')['data']
sql_string = finsert into {table_name} ({cols_string}) values ({val_wildcard_string});
cur.executemany(sql_string, rows_to_upload)
logging.info(f'{len(rows_to_upload)} rows uploaded to {table_name}')
# 步骤6:提交更改并关闭连接
con.commit()
con.close()
函数2：对数据帧的sql查询 我们的第二个任务是在第一个任务中创建的数据库和表上编写和执行sql查询。
下面的代码将其作为另一个函数实现，我也将逐步介绍。该函数将sql_query_string和db_name（.db file）作为输入，运行以下步骤，并返回一个dataframe作为输出。
连接到.db文件中的sql db
在sql查询字符串中执行sql查询
运行sql查询后获取结果数据和输出的列名
关闭与数据库的连接
将结果作为数据帧返回
def sql_query_to_pd(sql_query_string: str, db_name: str ='default.db') -> pd.dataframe:
'''执行一个sql查询，并以数据帧的形式返回结果
args:
sql_query_string (str): 要执行的sql查询字符串
db_name (str, optional): :要执行查询的sqlite数据库的名称
默认为“default.db”
returns:
pd.dataframe: 数据帧中sql查询的结果
'''
# 步骤1:连接sql数据库
con = sqlite3.connect(db_name)
# 步骤2:执行sql查询
cursor = con.execute(sql_query_string)
# 步骤3:获取数据和列名
result_data = cursor.fetchall()
cols = [description[0] for description in cursor.description]
# 步骤4:关闭连接
con.close()
# 步骤5:返回数据帧
return pd.dataframe(result_data, columns=cols)
差不多就是这样！我们现在有两个函数，可以粘贴到任何colab笔记本中，以解决我们的两个任务。让我们通过一个例子来看看如何使用这两个函数
使用这两个函数在colab中运行sql 由于新冠病毒基本上是我们这些天谈论的唯一话题，我从kaggle下载了一个新冠病毒疫苗样本数据集，以测试我们的两个函数函数的使用非常简单，如下所示
将下载的csv文件加载到数据帧中
使用我们的第一个函数pd_to_sqldb函数将我们在上一步中加载的数据帧上载到名为default.db的数据库中名为 country_vaccination 的表中
在名为 sql_query_string 的字符串变量中编写sql查询。正如你在下面的代码片段中所看到的，我们可以编写任何可以想到的复杂sql查询。
使用第二个函数sql_query_to_pd在default.db上执行上一步的sql查询，并将结果保存在result_df数据框中
# 步骤1:读取csv文件到一个数据帧
# 数据集来自https://www.kaggle.com/gpreda/covid-world-vaccination-progress
input_df = pd.read_csv('country_vaccinations.csv')
# 步骤2: 上传数据帧到sql表
pd_to_sqldb(input_df,
table_name='country_vaccinations',
db_name='default.db')
# 步骤3: 在字符串变量中写入sql查询
sql_query_string =
select country, sum(daily_vaccinations) as total_vaccinated
from country_vaccinations
where daily_vaccinations is not null
group by country
order by total_vaccinated desc
# 步骤4:执行sql查询
result_df = sql_query_to_pd(sql_query_string, db_name='default.db')
result_df
从结果中，我们可以看到（在撰写本文时），中国的接种人数居世界首位，其次是印度和美国。我们通过在googlecolab中对db执行sql查询得到了这些结果！
给你。现在，你可以在下次面试中展示你卓越的python和sql知识了！
另外，节省一些时间，直接使用我在本文开头链接的googlecolab笔记本，它包含上述函数和示例查询。
新唐开发平台：进阶使用者的七大诀窍(3)使用System Viewer检查寄存器状态
威图手机售后维修部-VERTU手机官方售后维修中心
云计算厂商在2020的竞争有多大
印制电路板的设计基础
ONNX格式模型部署兼容性框架介绍
如何在Colab中使用SQL
灵动微电子M0内核32位单片机MM32F0144C4Q
如何避免代价昂贵的数据恢复？
作为新时代的智慧医院，利用云计算实现数据进行精准分析
2017华为OV米的国产手机崛起，苹果三星你们怕了吗？2018年将会是什么情况呢？期待吗？
购买二手空调可能会出现的问题盘点 看过后你再决定买不买吧
HKHC45/14电流互感器
共射放大电路的交流分析
iPhone 15若限充电速度或违法
我国自主三代核电华龙一号热态性能试验完成
智能门禁系统是现代化小区智慧管理便捷、安全的新选择
麻省理工研发出新型LCD电视控制传感器
Vimeo对GIF的运用方法与GIF的优势劣势
怎么快速找到EMI问题源头 怎么完善系统化设计流程
宁德时代MBT技术落地 重卡及工程机械电动化大势所趋
作为一名数据科学招聘经理，我发现一个20-30分钟的现场编码测试和一些准备好的任务能够有效地识别那些能够胜任职位的候选人。
google colab［https://colab.research.google.com/notebooks/intro.ipynb？utm_source=scs-index］是各种离线和实时数据科学编码的优秀工具，因为它熟悉的笔记本环境和并且方便共享。但是colab几乎只限于python（还有一些黑客攻击）。
根据我个人的经验，sql是成为一名成功的数据科学家的关键技能。深度学习是很好的，但是如果你不能编写sql查询，你可能永远都会被你的猫对狗分类器所困扰。
因此，我开始寻找进行实时sql编码测试的方法，瞧！我偶然发现了这种在colab（或任何python环境）中运行sql的简单方法，实际上只有2个helper函数。
在本文中，我将介绍两个python函数，我们可以使用它们在google colab中设置和运行sql，具体如下：
在google colab中创建一个数据库并将数据帧上传到该数据库中的一个表中
在googlecolab中对该数据库和表编写和执行sql查询。
所有代码都可以在我的github帐户上找到。你也可以直接在谷歌colab上打开代码， 单击此链接：
https://colab.research.google.com/github/stephenleo/medium-python-hacks/blob/main/02_sql_on_colab/main.ipynb
sqlite
失败是成功之母
在这次搜索之前，我只隐约听说过sqlite，但从未需要深入研究它。
sqlite…实现了一个小型、快速、自包含、高可靠性、功能齐全的sql数据库引擎。
sqlite是世界上使用最多的数据库引擎。sqlite内置于所有手机和大多数计算机中，并捆绑在人们每天使用的应用程序中。
听起来很有希望！sqlite的工作原理是创建一个本地的.db文件，我们可以像普通的sql数据库一样连接到该文件。然后我们可以在这个.db文件上创建表、上传数据和查询数据。
另外，用于创建sqlite并与之交互的python模块（sqlite3）也是python标准库的一部分。所以它在colab上是开箱即用的。
首先，让我们导入sqlite3和pandas模块，它们是我们任务的唯一需求
import sqlite3
import pandas as pd
函数1：将dataframe转换为sql db表 我们的第一个任务是创建一个数据库，并将一个数据帧上传到该数据库中的一个表中。
下面的代码将其作为一个函数实现，我将逐步介绍。该函数将输入数据帧、表名称和数据库名称（.db）作为输入，并运行以下步骤。
设置一些日志以跟踪函数的执行
查找数据框中的所有列。这是必要的，因为我们需要在创建表和将数据上载到表时提供此信息。
连接到.db文件（如果存在）。如果文件不存在，请在本地计算机上创建新文件。
在.db文件中创建一个表，我们在上一步中连接到该表（或刚刚创建）
将 input_df 中的数据行上传到我们在上一步中刚刚创建的表中
提交更改并关闭与数据库的连接
def pd_to_sqldb(input_df: pd.dataframe,
table_name: str,
db_name: str = 'default.db') -> none:
'''
取一个数据帧' input_df '并将其上传到' table_name ' sqlite表
args:
input_df (pd.dataframe): 包含要上传到sqlite的数据的数据帧
table_name (str): 要上传的sqlite表的名称
db_name (str, optional): 创建表的sqlite数据库的名称。
默认为“default.db”
'''
# 步骤1:设置本地日志
import logging
logging.basicconfig(level=logging.info,
format='%(asctime)s %(levelname)s: %(message)s',
datefmt='%y-%m-%d %h:%m:%s')
# 步骤2:在数据帧中查找列
cols = input_df.columns
cols_string = ','.join(cols)
val_wildcard_string = ','.join(['?'] * len(cols))
# 步骤3:如果db文件存在，连接到它，否则创建一个新文件
con = sqlite3.connect(db_name)
cur = con.cursor()
logging.info(f'sql db {db_name} created')
# 步骤4:创建表
sql_string = fcreate table {table_name} ({cols_string});
cur.execute(sql_string)
logging.info(f'sql table {table_name} created with {len(cols)} columns')
# 步骤5:上传数据帧
rows_to_upload = input_df.to_dict(orient='split')['data']
sql_string = finsert into {table_name} ({cols_string}) values ({val_wildcard_string});
cur.executemany(sql_string, rows_to_upload)
logging.info(f'{len(rows_to_upload)} rows uploaded to {table_name}')
# 步骤6:提交更改并关闭连接
con.commit()
con.close()
函数2：对数据帧的sql查询 我们的第二个任务是在第一个任务中创建的数据库和表上编写和执行sql查询。
下面的代码将其作为另一个函数实现，我也将逐步介绍。该函数将sql_query_string和db_name（.db file）作为输入，运行以下步骤，并返回一个dataframe作为输出。
连接到.db文件中的sql db
在sql查询字符串中执行sql查询
运行sql查询后获取结果数据和输出的列名
关闭与数据库的连接
将结果作为数据帧返回
def sql_query_to_pd(sql_query_string: str, db_name: str ='default.db') -> pd.dataframe:
'''执行一个sql查询，并以数据帧的形式返回结果
args:
sql_query_string (str): 要执行的sql查询字符串
db_name (str, optional): :要执行查询的sqlite数据库的名称
默认为“default.db”
returns:
pd.dataframe: 数据帧中sql查询的结果
'''
# 步骤1:连接sql数据库
con = sqlite3.connect(db_name)
# 步骤2:执行sql查询
cursor = con.execute(sql_query_string)
# 步骤3:获取数据和列名
result_data = cursor.fetchall()
cols = [description[0] for description in cursor.description]
# 步骤4:关闭连接
con.close()
# 步骤5:返回数据帧
return pd.dataframe(result_data, columns=cols)
差不多就是这样！我们现在有两个函数，可以粘贴到任何colab笔记本中，以解决我们的两个任务。让我们通过一个例子来看看如何使用这两个函数
使用这两个函数在colab中运行sql 由于新冠病毒基本上是我们这些天谈论的唯一话题，我从kaggle下载了一个新冠病毒疫苗样本数据集，以测试我们的两个函数函数的使用非常简单，如下所示
将下载的csv文件加载到数据帧中
使用我们的第一个函数pd_to_sqldb函数将我们在上一步中加载的数据帧上载到名为default.db的数据库中名为 country_vaccination 的表中
在名为 sql_query_string 的字符串变量中编写sql查询。正如你在下面的代码片段中所看到的，我们可以编写任何可以想到的复杂sql查询。
使用第二个函数sql_query_to_pd在default.db上执行上一步的sql查询，并将结果保存在result_df数据框中
# 步骤1:读取csv文件到一个数据帧
# 数据集来自https://www.kaggle.com/gpreda/covid-world-vaccination-progress
input_df = pd.read_csv('country_vaccinations.csv')
# 步骤2: 上传数据帧到sql表
pd_to_sqldb(input_df,
table_name='country_vaccinations',
db_name='default.db')
# 步骤3: 在字符串变量中写入sql查询
sql_query_string =
select country, sum(daily_vaccinations) as total_vaccinated
from country_vaccinations
where daily_vaccinations is not null
group by country
order by total_vaccinated desc
# 步骤4:执行sql查询
result_df = sql_query_to_pd(sql_query_string, db_name='default.db')
result_df
从结果中，我们可以看到（在撰写本文时），中国的接种人数居世界首位，其次是印度和美国。我们通过在googlecolab中对db执行sql查询得到了这些结果！
给你。现在，你可以在下次面试中展示你卓越的python和sql知识了！
另外，节省一些时间，直接使用我在本文开头链接的googlecolab笔记本，它包含上述函数和示例查询。
新唐开发平台：进阶使用者的七大诀窍(3)使用System Viewer检查寄存器状态
威图手机售后维修部-VERTU手机官方售后维修中心
云计算厂商在2020的竞争有多大
印制电路板的设计基础
ONNX格式模型部署兼容性框架介绍
如何在Colab中使用SQL
灵动微电子M0内核32位单片机MM32F0144C4Q
如何避免代价昂贵的数据恢复？
作为新时代的智慧医院，利用云计算实现数据进行精准分析
2017华为OV米的国产手机崛起，苹果三星你们怕了吗？2018年将会是什么情况呢？期待吗？
购买二手空调可能会出现的问题盘点 看过后你再决定买不买吧
HKHC45/14电流互感器
共射放大电路的交流分析
iPhone 15若限充电速度或违法
我国自主三代核电华龙一号热态性能试验完成
智能门禁系统是现代化小区智慧管理便捷、安全的新选择
麻省理工研发出新型LCD电视控制传感器
Vimeo对GIF的运用方法与GIF的优势劣势
怎么快速找到EMI问题源头 怎么完善系统化设计流程
宁德时代MBT技术落地 重卡及工程机械电动化大势所趋