作者：俊欣

來(lái)源：關(guān)于數(shù)據(jù)分析與可視化

今天小編和大家來(lái)聊一下SQLALchemy這個(gè)模塊，該模塊是Python當(dāng)中最有名的ORM框架，該框架是建立在數(shù)據(jù)庫(kù)API之上，使用關(guān)系對(duì)象映射進(jìn)行數(shù)據(jù)庫(kù)的操作，簡(jiǎn)而言之便是：將對(duì)象轉(zhuǎn)換成SQL，然后使用數(shù)據(jù)API執(zhí)行SQL并獲取執(zhí)行結(jié)果。

看到這里，相信不少的讀者可能會(huì)感覺(jué)到云里霧里，我們就通過(guò)一個(gè)簡(jiǎn)單的案例在說(shuō)明一下吧。例如我們想要在mysql當(dāng)中新建一個(gè)表格，我們首先需要連接上數(shù)據(jù)庫(kù)，代碼如下

# 連接數(shù)據(jù)庫(kù) sql_connect = 'mysql+pymysql://root:123456@localhost:3306/sql_prac?charset=utf8' engine = create_engine(sql_connect) DBSession = sessionmaker(bind=engine) # 創(chuàng)建對(duì)象的基類: BaseModel = declarative_base()

定義表結(jié)構(gòu)

對(duì)于新創(chuàng)建的表格，我們命名為是“User”，同時(shí)我們還需要定義表結(jié)構(gòu)，代碼如下

#定義對(duì)象 class User(BaseModel): # 表名 __tablename__ = 'user' # 表結(jié)構(gòu)，其中ID設(shè)為是主鍵，并且是自動(dòng)增加的 id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(20))
    age = Column(Integer)

創(chuàng)建以及刪除表

對(duì)于創(chuàng)建表以及刪除表的操作，代碼如下

#創(chuàng)建映射的數(shù)據(jù)庫(kù)表 def init_db(): BaseModel.metadata.create_all(engine) #刪除映射的數(shù)據(jù)庫(kù)表 def drop_db(): BaseModel.metadata.drop_all(engine)

插入數(shù)據(jù)

我們可以嘗試往新建的表格當(dāng)中插入幾個(gè)值，代碼如下

def insert_data(name_1, age_1): # 創(chuàng)建session對(duì)象,相當(dāng)于MySQLdb里面的游標(biāo) session = DBSession() # 創(chuàng)建新User對(duì)象: new_user = User(name=name_1, age=age_1) # 添加到session: session.add(new_user) # 提交添加數(shù)據(jù)的操作 session.commit() # 關(guān)閉session session.close() if __name__ == "__main__":
    insert_data(name_1="Mike", age_1=20)
    insert_data(name_1="John", age_1=35)
    .......

運(yùn)行后的結(jié)果如下圖所示

查詢

要是我們想要查詢表格中的數(shù)據(jù)，可以這么來(lái)做

# 創(chuàng)建Session: session = DBSession() # 創(chuàng)建Query查詢，filter是where條件，最后調(diào)用one()返回唯一行，如果調(diào)用all()則返回所有行: user = session.query(User).filter(User.name == 'Tom').one() # 打印類型和對(duì)象的name屬性和age屬性: print(user.name, user.age) # 關(guān)閉Session: session.close()

要是調(diào)用的是all()則返回所有行，因此我們需要通過(guò)for循環(huán)遍歷出來(lái)的結(jié)果然后打印，代碼如下

users = session.query(User).filter(User.name == 'John').all() for u in users: print(u.name, u.age)

更新和刪除數(shù)據(jù)

我們嘗試來(lái)更新表格中的一些數(shù)據(jù)，代碼如下

# 創(chuàng)建Session: session = DBSession() # 可以進(jìn)行多條數(shù)據(jù)更新 user = session.query(User).filter(User.id == 3) user.update({User.age: 30}) # 提交數(shù)據(jù) session.commit() # 關(guān)閉Session session.close()

通過(guò)“ID”來(lái)鎖定要更新的數(shù)據(jù)的位置，然后我們通過(guò)調(diào)用update()方法將其年齡改成指定的值。與此同時(shí)我們還可以來(lái)刪除表格當(dāng)中的一些值，代碼如下

# 創(chuàng)建Session session = DBSession() # 刪除哪些數(shù)據(jù) user = session.query(User).filter(User.id == 5).one()
session.delete(user) # 提交數(shù)據(jù) session.commit() # 關(guān)閉session session.close()

同樣我們也是通過(guò)“ID”來(lái)鎖定要?jiǎng)h除數(shù)據(jù)的位置，然后調(diào)用delete()方法。

直接運(yùn)行SQL語(yǔ)句

當(dāng)然我們?cè)趧?chuàng)建session之后，我們也可以在里面直接運(yùn)行SQL語(yǔ)句，例如我們想要查看一下總共有哪些數(shù)據(jù)庫(kù)，代碼如下

session = DBSession() print(session.execute('show databases').fetchall())
session.close()

或者我們是想返回表格中的所有數(shù)據(jù)，代碼如下

session = DBSession() print(session.execute('select * from user').fetchall())
session.close()

DataFrame到MySQL數(shù)據(jù)庫(kù)

我們同時(shí)也可以批量的將excel或者csv文件當(dāng)中的數(shù)據(jù)批量的導(dǎo)入到MySQL數(shù)據(jù)庫(kù)當(dāng)中，我們先通過(guò)Pandas讀取文件中的數(shù)據(jù)，代碼如下

sql_connect = 'mysql+pymysql://用戶名:密碼@ip地址:端口號(hào)/數(shù)據(jù)庫(kù)名稱?charset=utf8' engine = create_engine(sql_connect)
df = pd.read_excel("sqlalchemy_test1.xlsx")
df.to_sql("user", engine, index=False, if_exists='append')

當(dāng)然我們也可以從數(shù)據(jù)庫(kù)的某個(gè)表格當(dāng)中來(lái)讀取數(shù)據(jù)，代碼如下

df = pd.read_sql("表格名", engine) print(df.head())