如今，編碼測試在數(shù)據(jù)科學(xué)面試過程中幾乎是標(biāo)準(zhǔn)的。

作為一名數(shù)據(jù)科學(xué)招聘經(jīng)理，我發(fā)現(xiàn)一個20-30分鐘的現(xiàn)場編碼測試和一些準(zhǔn)備好的任務(wù)能夠有效地識別那些能夠勝任職位的候選人。

Google Colab［https://colab.research.google.com/notebooks/intro.ipynb？utm_source=scs-index］是各種離線和實(shí)時數(shù)據(jù)科學(xué)編碼的優(yōu)秀工具，因?yàn)樗煜さ墓P記本環(huán)境和并且方便共享。但是Colab幾乎只限于Python（還有一些黑客攻擊）。

根據(jù)我個人的經(jīng)驗(yàn)，SQL是成為一名成功的數(shù)據(jù)科學(xué)家的關(guān)鍵技能。深度學(xué)習(xí)是很好的，但是如果你不能編寫SQL查詢，你可能永遠(yuǎn)都會被你的貓對狗分類器所困擾。

因此，我開始尋找進(jìn)行實(shí)時SQL編碼測試的方法，瞧！我偶然發(fā)現(xiàn)了這種在Colab（或任何Python環(huán)境）中運(yùn)行SQL的簡單方法，實(shí)際上只有2個helper函數(shù)。

在本文中，我將介紹兩個Python函數(shù)，我們可以使用它們在Google Colab中設(shè)置和運(yùn)行SQL，具體如下：

在Google Colab中創(chuàng)建一個數(shù)據(jù)庫并將數(shù)據(jù)幀上傳到該數(shù)據(jù)庫中的一個表中

在GoogleColab中對該數(shù)據(jù)庫和表編寫和執(zhí)行SQL查詢。

所有代碼都可以在我的Github帳戶上找到。你也可以直接在谷歌Colab上打開代碼， 單擊此鏈接：

https://colab.research.google.com/github/stephenleo/medium-python-hacks/blob/main/02_sql_on_colab/main.ipynb

SQLite

失敗是成功之母

在這次搜索之前，我只隱約聽說過SQLite，但從未需要深入研究它。

SQLite…實(shí)現(xiàn)了一個小型、快速、自包含、高可靠性、功能齊全的SQL數(shù)據(jù)庫引擎。

SQLite是世界上使用最多的數(shù)據(jù)庫引擎。SQLite內(nèi)置于所有手機(jī)和大多數(shù)計(jì)算機(jī)中，并捆綁在人們每天使用的應(yīng)用程序中。

聽起來很有希望！SQLite的工作原理是創(chuàng)建一個本地的.db文件，我們可以像普通的SQL數(shù)據(jù)庫一樣連接到該文件。然后我們可以在這個.db文件上創(chuàng)建表、上傳數(shù)據(jù)和查詢數(shù)據(jù)。

另外，用于創(chuàng)建SQLite并與之交互的Python模塊（sqlite3）也是Python標(biāo)準(zhǔn)庫的一部分。所以它在Colab上是開箱即用的。

首先，讓我們導(dǎo)入sqlite3和pandas模塊，它們是我們?nèi)蝿?wù)的唯一需求

importsqlite3
importpandasaspd

函數(shù)1：將Dataframe轉(zhuǎn)換為SQL DB表

我們的第一個任務(wù)是創(chuàng)建一個數(shù)據(jù)庫，并將一個數(shù)據(jù)幀上傳到該數(shù)據(jù)庫中的一個表中。

下面的代碼將其作為一個函數(shù)實(shí)現(xiàn)，我將逐步介紹。該函數(shù)將輸入數(shù)據(jù)幀、表名稱和數(shù)據(jù)庫名稱（.db）作為輸入，并運(yùn)行以下步驟。

1. 設(shè)置一些日志以跟蹤函數(shù)的執(zhí)行

2. 查找數(shù)據(jù)框中的所有列。這是必要的，因?yàn)槲覀冃枰趧?chuàng)建表和將數(shù)據(jù)上載到表時提供此信息。

3. 連接到.db文件（如果存在）。如果文件不存在，請?jiān)诒镜赜?jì)算機(jī)上創(chuàng)建新文件。

4. 在.db文件中創(chuàng)建一個表，我們在上一步中連接到該表（或剛剛創(chuàng)建）

5. 將 input_df 中的數(shù)據(jù)行上傳到我們在上一步中剛剛創(chuàng)建的表中

6. 提交更改并關(guān)閉與數(shù)據(jù)庫的連接

defpd_to_sqlDB(input_df:pd.DataFrame,
table_name:str,
db_name:str='default.db')->None:

'''
取一個數(shù)據(jù)幀'input_df'并將其上傳到'table_name'SQLITE表
Args:
input_df(pd.DataFrame):包含要上傳到SQLITE的數(shù)據(jù)的數(shù)據(jù)幀
table_name(str):要上傳的SQLITE表的名稱
db_name (str, optional):創(chuàng)建表的SQLITE數(shù)據(jù)庫的名稱。
默認(rèn)為“default.db”
'''

#步驟1:設(shè)置本地日志
importlogging
logging.basicConfig(level=logging.INFO,
format='%(asctime)s%(levelname)s:%(message)s',
datefmt='%Y-%m-%d%H:%M:%S')

#步驟2:在數(shù)據(jù)幀中查找列
cols=input_df.columns
cols_string=','.join(cols)
val_wildcard_string=','.join(['?']*len(cols))

#步驟3:如果DB文件存在，連接到它，否則創(chuàng)建一個新文件
con=sqlite3.connect(db_name)
cur=con.cursor()
logging.info(f'SQLDB{db_name}created')

#步驟4:創(chuàng)建表
sql_string=f"""CREATETABLE{table_name}({cols_string});"""
cur.execute(sql_string)
logging.info(f'SQLTable{table_name}createdwith{len(cols)}columns')

#步驟5:上傳數(shù)據(jù)幀
rows_to_upload=input_df.to_dict(orient='split')['data']
sql_string=f"""INSERTINTO{table_name}({cols_string})VALUES({val_wildcard_string});"""
cur.executemany(sql_string,rows_to_upload)
logging.info(f'{len(rows_to_upload)}rowsuploadedto{table_name}')

#步驟6:提交更改并關(guān)閉連接
con.commit()
con.close()

函數(shù)2：對數(shù)據(jù)幀的SQL查詢

我們的第二個任務(wù)是在第一個任務(wù)中創(chuàng)建的數(shù)據(jù)庫和表上編寫和執(zhí)行SQL查詢。

下面的代碼將其作為另一個函數(shù)實(shí)現(xiàn)，我也將逐步介紹。該函數(shù)將sql_query_string和db_name（.db file）作為輸入，運(yùn)行以下步驟，并返回一個dataframe作為輸出。

1. 連接到.DB文件中的SQL DB

2. 在SQL查詢字符串中執(zhí)行SQL查詢

3. 運(yùn)行SQL查詢后獲取結(jié)果數(shù)據(jù)和輸出的列名

4. 關(guān)閉與數(shù)據(jù)庫的連接

5. 將結(jié)果作為數(shù)據(jù)幀返回

defsql_query_to_pd(sql_query_string:str,db_name:str='default.db')->pd.DataFrame:
'''執(zhí)行一個SQL查詢，并以數(shù)據(jù)幀的形式返回結(jié)果
Args:
sql_query_string(str):要執(zhí)行的SQL查詢字符串
db_name(str,optional)::要執(zhí)行查詢的SQLITE數(shù)據(jù)庫的名稱
默認(rèn)為“default.db”
Returns:
pd.DataFrame:數(shù)據(jù)幀中SQL查詢的結(jié)果
'''
#步驟1:連接SQL數(shù)據(jù)庫
con=sqlite3.connect(db_name)

#步驟2:執(zhí)行SQL查詢
cursor=con.execute(sql_query_string)

#步驟3:獲取數(shù)據(jù)和列名
result_data=cursor.fetchall()
cols=[description[0]fordescriptionincursor.description]

#步驟4:關(guān)閉連接
con.close()

#步驟5:返回?cái)?shù)據(jù)幀
returnpd.DataFrame(result_data,columns=cols)

差不多就是這樣！我們現(xiàn)在有兩個函數(shù)，可以粘貼到任何Colab筆記本中，以解決我們的兩個任務(wù)。讓我們通過一個例子來看看如何使用這兩個函數(shù)

使用這兩個函數(shù)在Colab中運(yùn)行SQL

由于新冠病毒基本上是我們這些天談?wù)摰奈ㄒ辉掝}，我從Kaggle下載了一個新冠病毒疫苗樣本數(shù)據(jù)集，以測試我們的兩個函數(shù)函數(shù)的使用非常簡單，如下所示

1. 將下載的csv文件加載到數(shù)據(jù)幀中

2. 使用我們的第一個函數(shù)pd_to_sqlDB函數(shù)將我們在上一步中加載的數(shù)據(jù)幀上載到名為default.DB的數(shù)據(jù)庫中名為 country_vaccination 的表中

3. 在名為 sql_query_string 的字符串變量中編寫SQL查詢。正如你在下面的代碼片段中所看到的，我們可以編寫任何可以想到的復(fù)雜SQL查詢。

4. 使用第二個函數(shù)sql_query_to_pd在default.db上執(zhí)行上一步的sql查詢，并將結(jié)果保存在result_df數(shù)據(jù)框中

#步驟1:讀取csv文件到一個數(shù)據(jù)幀
#數(shù)據(jù)集來自https://www.kaggle.com/gpreda/covid-world-vaccination-progress
input_df=pd.read_csv('country_vaccinations.csv')

#步驟2:上傳數(shù)據(jù)幀到SQL表
pd_to_sqlDB(input_df,
table_name='country_vaccinations',
db_name='default.db')

#步驟3:在字符串變量中寫入SQL查詢
sql_query_string="""
SELECTcountry,SUM(daily_vaccinations)astotal_vaccinated
FROMcountry_vaccinations
WHEREdaily_vaccinationsISNOTNULL
GROUPBYcountry
ORDERBYtotal_vaccinatedDESC
"""

#步驟4:執(zhí)行SQL查詢
result_df=sql_query_to_pd(sql_query_string,db_name='default.db')
result_df

從結(jié)果中，我們可以看到（在撰寫本文時），中國的接種人數(shù)居世界首位，其次是印度和美國。我們通過在GoogleColab中對DB執(zhí)行SQL查詢得到了這些結(jié)果！

給你?，F(xiàn)在，你可以在下次面試中展示你卓越的Python和SQL知識了！

另外，節(jié)省一些時間，直接使用我在本文開頭鏈接的GoogleColab筆記本，它包含上述函數(shù)和示例查詢。