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

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

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

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

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

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

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

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

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

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

SQLite

失敗是成功之母

在這次搜索之前，我只隱約聽(tīng)說(shuō)過(guò)SQLite，但從未需要深入研究它。

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

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

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

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

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

importsqlite3
importpandasaspd

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

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

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

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

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

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

4. 在.db文件中創(chuàng)建一個(gè)表，我們?cè)谏弦徊街羞B接到該表（或剛剛創(chuàng)建）

5. 將 input_df 中的數(shù)據(jù)行上傳到我們?cè)谏弦徊街袆倓倓?chuàng)建的表中

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

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

'''
取一個(gè)數(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ù)庫(kù)的名稱。
默認(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)建一個(gè)新文件
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：對(duì)數(shù)據(jù)幀的SQL查詢

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

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

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

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

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

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

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

defsql_query_to_pd(sql_query_string:str,db_name:str='default.db')->pd.DataFrame:
'''執(zhí)行一個(gè)SQL查詢，并以數(shù)據(jù)幀的形式返回結(jié)果
Args:
sql_query_string(str):要執(zhí)行的SQL查詢字符串
db_name(str,optional)::要執(zhí)行查詢的SQLITE數(shù)據(jù)庫(kù)的名稱
默認(rèn)為“default.db”
Returns:
pd.DataFrame:數(shù)據(jù)幀中SQL查詢的結(jié)果
'''
#步驟1:連接SQL數(shù)據(jù)庫(kù)
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)在有兩個(gè)函數(shù)，可以粘貼到任何Colab筆記本中，以解決我們的兩個(gè)任務(wù)。讓我們通過(guò)一個(gè)例子來(lái)看看如何使用這兩個(gè)函數(shù)

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

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

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

2. 使用我們的第一個(gè)函數(shù)pd_to_sqlDB函數(shù)將我們?cè)谏弦徊街屑虞d的數(shù)據(jù)幀上載到名為default.DB的數(shù)據(jù)庫(kù)中名為 country_vaccination 的表中

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

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

#步驟1:讀取csv文件到一個(gè)數(shù)據(jù)幀
#數(shù)據(jù)集來(lái)自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:在字符串變量中寫(xiě)入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é)果中，我們可以看到（在撰寫(xiě)本文時(shí)），中國(guó)的接種人數(shù)居世界首位，其次是印度和美國(guó)。我們通過(guò)在GoogleColab中對(duì)DB執(zhí)行SQL查詢得到了這些結(jié)果！

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

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