還在寫落落長的SQL語法嗎?
別鬧了,試試ORM框架吧!防堵SQL injection人人有責!
本文以Python運用SQLAlchemy框架連接Microsoft SQL Server
在網路上已經有許多關於ORM的優缺點文章,在此就不多贅述,若非需要很複雜的查詢,ORM還是我的首選,那麼問題來了,在.NET有Entity Framework,Python有什麼呢?
本文所使用的是SQLAlchemy,並實作一個簡單的學生資料系統,藉此於Python中,以ORM技術完成對資料庫的CRUD操作
作業系統:Windows 10
Python版本:Python 3.6
使用套件:SQLAlchemy 1.2.8、pyodbc 4.0.23
SQLAlchemy需要藉由DBAPI和MS SQL溝通,
這裡我用的是pyodbc,也因為這樣所以加裝pyodbc 4.0.23
前置作業
首先呢資料庫長這樣
其中School資料表裡面已經先新增四所學校
1.安裝
在CMD裡用pip指令安裝SQLAlchemy、pyodbc套件
pip install SQLAlchemy
pip install pyodbc
2.設定資料庫連線
開啟你的GUI主程式,import create_engine,並設定資料庫連線
import sys
from PyQt5.QtWidgets import QDialog, QApplication
from UI import Ui_Form
from sqlalchemy import create_engine
engine = create_engine('mssql+pyodbc://DemoUsername:DemoPassword@DESKTOP-T40QFVL/Demo?driver=SQL+Server+Native+Client+11.0')
class AppWindow(QDialog):
def __init__(self):
super().__init__()
self.ui = Ui_Form()
self.ui.setupUi(self)
self.show()
app = QApplication(sys.argv)
w = AppWindow()
w.show()
sys.exit(app.exec_())
而在其中呢......
ODBC SQL Server 驅動程式版本可以參考
3.建立對應資料庫的Class
import declarative_base、SQL Server的資料庫型態、Table、 MetaData、 Column、 Integer、 String、 ForeignKey
撰寫對應資料庫的Class並且完成映射
import sys
from PyQt5.QtWidgets import QDialog, QApplication
from UI import Ui_Form
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.dialects.mssql import \
BIGINT, BINARY, BIT, CHAR, DATE, DATETIME, DATETIME2, \
DATETIMEOFFSET, DECIMAL, FLOAT, IMAGE, INTEGER, MONEY, \
NCHAR, NTEXT, NUMERIC, NVARCHAR, REAL, SMALLDATETIME, \
SMALLINT, SMALLMONEY, SQL_VARIANT, TEXT, TIME, \
TIMESTAMP, TINYINT, UNIQUEIDENTIFIER, VARBINARY, VARCHAR
from sqlalchemy import Table, MetaData, Column, Integer, String, ForeignKey
from sqlalchemy.orm import mapper
engine = create_engine('mssql+pyodbc://DemoUsername:DemoPassword@DESKTOP-T40QFVL/Demo?driver=SQL+Server+Native+Client+11.0')
Base = declarative_base()
#建立school資料表對應
school_metadata = MetaData()
school = Table('School', school_metadata,
Column('ID', UNIQUEIDENTIFIER, primary_key=True),
Column('Name', NVARCHAR(8))
)
class School(object):
def __init__(self, ID, Name):
self.ID = ID
self.Name = Name
mapper(School, school)
#建立student資料表對應
student_metadata = MetaData()
student = Table('Student', student_metadata,
Column('ID', UNIQUEIDENTIFIER, primary_key=True),
Column('Name', NVARCHAR(10)),
Column('School', UNIQUEIDENTIFIER, ForeignKey(School.ID)),
Column('Sex', NVARCHAR(5))
)
class Student(object):
def __init__(self, ID, Name, School, Sex):
self.ID = ID
self.Name = Name
self.School = School
self.Sex = Sex
mapper(Student, student)
Base.metadata.create_all(engine)
class AppWindow(QDialog):
def __init__(self):
super().__init__()
self.ui = Ui_Form()
self.ui.setupUi(self)
self.show()
app = QApplication(sys.argv)
w = AppWindow()
w.show()
sys.exit(app.exec_())
新增以下這些東西