你好，我是林驥。
我在使用 Python 之前，做數(shù)據(jù)分析工作的流程，一般是先打開數(shù)據(jù)庫客戶端，然后運行一段寫好的 SQL 語句，把數(shù)據(jù)查詢出來，然后再把數(shù)據(jù)復(fù)制到 Excel 中并制作報表。在使用 Python 之后，這些工作都可以變成自動化，從而讓我有更多的時間，去思考和解決業(yè)務(wù)相關(guān)的問題，而不是陷入重復(fù)使用工具的手動操作。技術(shù)其實分為術(shù)和道兩種，具體的做事方法是術(shù)，做事的原理和原則是道。
很多具體的技術(shù)，很快就會落伍，所以只追求術(shù)的人，一輩子會很辛苦，還容易被淘汰。只有理解了事物的本質(zhì)和精髓，才能做到游刃有余。要想真正做好一件事，其實離不開長期的刻意練習(xí)。我寫的很多文章，在介紹「術(shù)」的同時，也希望能夠傳達(dá)「道」的理念，也就是把工具和思維相結(jié)合。今天介紹的技術(shù)，是用 Python 自動操作數(shù)據(jù)庫的方法。以 Python 中的 SQLAlchemy 模塊為例，配合使用其他第三方模塊，SQLAlchemy 能夠操作各種數(shù)據(jù)庫，包括 Oracle、PostgreSQL、MySQL、SQLite、SQL Server 等等，如果你還沒有安裝，可以通過以下命令進(jìn)行安裝：要測試 SQLAlchemy 模塊是否正確安裝，可以在 Jupyter Lab 中運行以下代碼：# 導(dǎo)入庫import sqlalchemy as sa
# 查看 SQLAlchemy 版本sa.__version__
如果該模塊正確安裝，就會輸出版本號，我目前使用的版本是 1.3.20。不同的數(shù)據(jù)庫，需要安裝不同的第三方模塊，比如說，要操作 Oracle，那么通常需要先安裝 cx_Oracle：在開始操作數(shù)據(jù)庫之前，需要先創(chuàng)建一個數(shù)據(jù)庫引擎，然后再連接數(shù)據(jù)庫：from sqlalchemy import create_engine
# 創(chuàng)建數(shù)據(jù)庫引擎engine = create_engine('oracle://user:password@ip_address:1521/orcl')
# 連接數(shù)據(jù)庫con = engine.connect()
其中用戶名、密碼和 IP 地址等基本信息，要根據(jù)自己的實際情況進(jìn)行修改。為了演示用 Python 自動操作數(shù)據(jù)庫，假設(shè)你的數(shù)據(jù)庫賬號擁有創(chuàng)建表的權(quán)限，那么就可以執(zhí)行下面的語句，實現(xiàn)創(chuàng)建一個新的表：# 執(zhí)行創(chuàng)建表的 SQL 語句sql = 'create table usr(id integer, name varchar2(50))'con.execute(sql)
數(shù)據(jù)庫的常用操作包括增、刪、改、查，下面分別簡單演示一下。# 增con.execute("insert into usr(id, name) values(1, 'Jim')")con.execute("insert into usr(id, name) values(2, 'Joe')")
# 刪con.execute('delete from usr where id = 1')
# 改con.execute("update usr set name = 'Jack' where id = 2")
雖然 SQLAlchemy 非常強(qiáng)大，但是如果能配合 Pandas 一起使用，那么就能雙劍合璧，從而更好地解決數(shù)據(jù)處理和分析的問題。# 查sql = 'select id, name from usr where id = :id'import pandas as pddf = pd.read_sql(sa.text(sql), engine, params={'id': 2})
df
有時候，我們還需要把數(shù)據(jù)備份到數(shù)據(jù)庫中，如果直接使用 Pandas 的 to_sql 函數(shù)，那么字符串類型的列會被自動存儲為 CLOB，這樣后續(xù)處理起來就會比較麻煩。我們可以用一個函數(shù)，實現(xiàn)自動轉(zhuǎn)換為 NVARCHAR 類型：from sqlalchemy.types import NVARCHAR, Float, Integer
# 映射數(shù)據(jù)中的列與數(shù)據(jù)類型，避免存為 CLOBdef mapping_df_types(df): dtypedict = {} for i, j in zip(df.columns, df.dtypes): if "object" in str(j): dtypedict.update({i: NVARCHAR(length=255)}) if "float" in str(j): dtypedict.update({i: Float(precision=2, asdecimal=True)}) if "int" in str(j): dtypedict.update({i: Integer()}) return dtypedict
# 把數(shù)據(jù)備份到數(shù)據(jù)庫，替換現(xiàn)有表，如果把 replace 換成 append，那么就是附加數(shù)據(jù)dtypedict = mapping_df_types(df)df.to_sql('usr_backup', engine, index=False, if_exists='replace', dtype=dtypedict)
這個功能還可以應(yīng)用于不同數(shù)據(jù)庫之間的數(shù)據(jù)遷移。比如說，從一個 MySQL 數(shù)據(jù)庫中查詢指定的數(shù)據(jù)，保存為 df，然后再附加到 Oracle 數(shù)據(jù)庫中。如果設(shè)置好相應(yīng)的定時任務(wù)，就能實現(xiàn)用 Python 自動操作數(shù)據(jù)庫，從而自動完成相關(guān)工作。最后，我們刪除上面演示用的兩個表，并關(guān)閉數(shù)據(jù)庫連接，節(jié)約資源，減少浪費，這是一個很好的習(xí)慣。# 刪除表con.execute('drop table usr')con.execute('drop table usr_backup')
# 關(guān)閉數(shù)據(jù)庫連接con.close()
本文介紹了用 Python 自動操作數(shù)據(jù)庫的一些常用方法，從 SQLAlchemy 和 cx_Oracle 模塊的安裝和導(dǎo)入，到連接數(shù)據(jù)庫，再到創(chuàng)建表和增刪改查，最后對數(shù)據(jù)進(jìn)行備份和刪除表，這些操作都可以在 Jupyter Lab 中一鍵執(zhí)行，自動完成一些數(shù)據(jù)庫的相關(guān)操作。事實上，你可以根據(jù)自己的實際情況，修改數(shù)據(jù)庫的類型和字符串連接等信息，并執(zhí)行各種各樣的 SQL 語句，自動完成更加復(fù)雜的數(shù)據(jù)庫操作。隨著機(jī)器變得越來越智能，許多工作都變得越來越自動化，導(dǎo)致很多人擔(dān)心被機(jī)器搶了飯碗，憂慮遭遇中年危機(jī)，這也是人之常情，但我們要學(xué)會積極應(yīng)對。雖然數(shù)據(jù)分析的工具變化得很快，但數(shù)據(jù)分析的思維基本不變，而且方法總比困難多。如果我們把新的技術(shù)工具，與數(shù)據(jù)分析的思維相結(jié)合，應(yīng)用于實際工作中，洞察事物的本質(zhì)，那么就能更好地完成自己的工作，從而創(chuàng)造更大的價值。
長按下方的二維碼，關(guān)注林驥的公眾號，更多干貨早知道。歡迎加入我的免費知識星球，我每天都會在星球內(nèi)分享讀書筆記和思考感悟，點擊左下角的閱讀原文即可加入。
