Python Opearte SQLAlchemy Do Something

標籤：

近段時間在看SQLAlchemy，總之萬事開頭難，但是麼辦法。

Database Urls

The create_engine() function produces an Engine object based on a URL. These URLs follow RFC-1738, and usually can include username, password, hostname, database name as well as optional keyword arguments for additional configuration. In some cases a file path is accepted, and in others a “data source name” replaces the “host” and “database” portions. The typical form of a database URL is:

dialect+driver://username:[email protected]:port/database 標準串連資料庫規範

MS-SQL串連案例

Microsoft SQL Server

The SQL Server dialect uses pyodbc as the default DBAPI. pymssql is also available:

# pyodbcengine = create_engine(‘mssql+pyodbc://scott:[email protected]‘)# pymssqlengine = create_engine(‘mssql+pymssql://scott:[email protected]:port/dbname‘)

More notes on connecting to SQL Server at Microsoft SQL Server.

這裡面測試用的是pyodbc進行串連的，分兩種

engine=create_engine("mssql+pyodbc://sa:@192.168.6.112:1433/FactoryHome?driver=SQL+Server+Native+Client+10.0")

還有一種就是通過微軟的dsn進行串連，如不知道dsn串連，可以百度一下看看是什麼意思

對資料的插入

from sqlalchemy import *engine=create_engine("mssql+pyodbc://sa:@192.168.6.112:1433/FactoryHome?driver=SQL+Server+Native+Client+10.0")metadata=MetaData()Table_1=Table("Table_1",metadata,Column("Code",String(10)),Column("Name",String(10)))ins=Table_1.insert().values(Code=‘cccccc‘,Name=‘王二‘)conn=engine.connect()result=conn.execute(ins)

參數化的形式，感覺有點感覺比拼接SQL來的快。

result=conn.execute(Table_1.insert(),Code=‘kkkkk‘,Name=‘網易‘)

對於給定的參數也可以這樣傳值。

對於資料的查詢，也必須的先構造一個TABLE，然後對應的欄位進行查詢

from sqlalchemy import *engine=create_engine("mssql+pyodbc://sa:@192.168.6.112:1433/FactoryHome?driver=SQL+Server+Native+Client+10.0")metadata=MetaData()Table_1=Table("Table_1",metadata,Column("Code",String(10)),Column("Name",String(10)))conn=engine.connect()result=conn.execute(select([Table_1]))for row in result:    print(row)

SQLAlchemy最好的方式就是能像SQL語句一樣能實現join串連查詢

>>> s = select([users, addresses]).where(users.c.id == addresses.c.user_id)SQL>>> for row in conn.execute(s):...     print(row)

這樣可以通過相關表的關聯就能查詢資料。

有好多東西，再敘。

Python Opearte SQLAlchemy Do Something