sqlalchemy如何使用

這篇文章主要介紹了sqlalchemy如何使用的相關知識，內容詳細易懂，操作簡單快捷，具有一定借鑒價值，相信大家閱讀完這篇sqlalchemy如何使用文章都會有所收獲，下面我們一起來看看吧。

SQLAlchemy：

是一個ORM框架；

大量使用元編程；

編程時，先對象&關系映射，才能操作DB，已成為工業標準；

pip install sqlalchemy pymysql

pip show sqlalchemy

> import sqlalchemy

> sqlalchemy.__version__ #version check

開發中，一般都采用ORM框架，這樣就可使用對象操作表了；

定義表映射的類，使用Column的描述器定義類屬性，使用ForeignKey定義外鍵約束；

如果在一個對象中，想查看其它表對象的內容，就要使用relationship來定義關系；

是否使用FK？

支持，力挺派，能使數據保證完整性、一致性；

不支持，嫌棄派，開發難度增加，大量數據時影響插入、修改、刪除的效率；

通常要在業務層保證數據一致性（事務）；

注：

賬號密碼授權，若為前端用戶，僅用來查數據，用grant select即可，不要grant all；

UML，統一建模語言；

navicat mysql，右鍵庫或表，轉儲SQL文件，結構和數據；若僅導出結構，導出前要刪除相關表中數據；

oralce中沒有自增，用到sequence，from sqlalchemy import Sequence

1、declare a mapping：

創建映射：

創建基類Base，便于實體類繼承；

創建實體類，Student表；

from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base() #基類，創建基類，一次性的

from sqlalchemy import Column, Integer, String

class Student(Base): #實體類，declare a mapping

__tablename__ = 'student' #指定表名，必須寫，防止忘記對應的表

id = Column('id', Integer, primary_key=True, autoincrement=True)) #定義屬性對應字段，第1參數是字段名，如果和屬性名一致可省，如果和屬性名不一致要指定；Column類指定對應的字段，必須指定，Column即上例的Field；此處'id'可省，Integer為type不能省

name = Column(String(64), nullable=False)

age = Column(Integer)

def __repr__(self):

return '<{} id:{} name:{} age:{}>'.format(self.__class__.__name__, self.id, self.name, self.age)

__str__ = __repr__

2、connecting：

數據庫連接的事情，交給引擎；

echo=True，引擎是否打印執行的語句，調試時打開很方便；

mysqldb的連接：

mysql+mysqldb://<user>:<password>@<host>[:port]/<dbname>

engine = sqlalchemy.create_engine('mysql+mysqldb://root:rootqazwsx@10.113.129.2:3306/test1')

pymysql的連接：

mysql+pymysql://<username>:<password>@<host>:<port>/<dbname>[?<options>]，options為與DB連接相關的選項

engine = sqlalchemy.create_engine('mysql+pymysql://root:rootqazwsx@10.113.129.2:3306/test1')

engine-configuration：

注：

內部使用了連接池；

dialect，方言，sql差異；

from sqlalchemy import create_engine

host = '10.113.129.2'

port = 3306

user = 'root'

password = 'rootqazwsx'

database = 'test1'

conn_str = 'mysql+pymysql://{}:{}@{}:{}/{}'.format(user, password, host, port, database)

# engine = create_engine('mysql+pymysql://root:rootqazwsx@10.113.129.2:3306/test1', echo=True) #

engine = create_engine(conn_str, echo=True) #引擎，管理連接池，connecting；echo=True，執行的語句是否打印，可在配置文件中全局設置，調試時打開

3、create a schema：

Base.metadata.drop_all(engine) #刪除繼承自Base的所有表

Base.metadata.create_all(engine) #create a schema，創建繼承自Base的所有表；Base.metadata中有一張表記錄著所有用Base創建的實體類（實體類繼承自Base)，遍歷所有實體類，將查到的定義信息填到創建表的語句中；engine的echo=True，打開，執行后會有建表語句；創建表，共用的功能，而子類上是個性化的功能

注：

生產很少這樣創建表，都是系統上線時由腳本生成，如用navicat mysql在測試里右鍵庫或表，轉儲SQL文件，再導入到生產里；

生產很少刪除表，廢棄都不能刪除；

4、creating a session：

在一個會話中操作數據庫，會話建立在連接上，連接被引擎管理；

from sqlalchemy.orm import sessionmaker

Session = sessionmaker(bind=engine) #方式一；返回類；另，autoflush=False，autocommit=False

session = Session()實例化，session.add()，session.add_all()，session.commit()，session.rollback()，session.query()，session.cursor，session.execute()執行原生sql

# Session = sessionmaker() #方式二

# session = Session(bind=engine)

注：

class sessionmaker(_SessionClassMethods):

def __init__(self, bind=None, class_=Session, autoflush=True,

autocommit=False,

expire_on_commit=True,

info=None, **kw):

5、create an instance of the mapped class：

例，增：

try:

stu1 = Student()

stu1.name = 'tom' #屬性賦值

stu1.age = 20

# student.id = 100 #有自增字段和有默認值的可不加

# session.add(stu1)狀態為pending

stu2 = Student(name='jerry', age=18) #構造的時候傳入

session.add_all([stu1, stu2])狀態為pending

# lst = []

# for i in range(10):

# stu = Student()

# stu.name = 'jessica' + str(i)

# stu.age = 20 + i

# lst.append(stu)

# session.add_all(lst)

session.commit()

except Exception as e:

print(e)

session.rollback()

finally:

pass

輸出：

2018-10-10 17:04:18,319 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'sql_mode'

2018-10-10 17:04:18,320 INFO sqlalchemy.engine.base.Engine {}

2018-10-10 17:04:18,333 INFO sqlalchemy.engine.base.Engine SELECT DATABASE()

2018-10-10 17:04:18,333 INFO sqlalchemy.engine.base.Engine {}

2018-10-10 17:04:18,355 INFO sqlalchemy.engine.base.Engine show collation where `Charset` = 'utf8' and `Collation` = 'utf8_bin'

2018-10-10 17:04:18,355 INFO sqlalchemy.engine.base.Engine {}

2018-10-10 17:04:18,371 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS CHAR(60)) AS anon_1

2018-10-10 17:04:18,371 INFO sqlalchemy.engine.base.Engine {}

2018-10-10 17:04:18,382 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS CHAR(60)) AS anon_1

2018-10-10 17:04:18,382 INFO sqlalchemy.engine.base.Engine {}

2018-10-10 17:04:18,393 INFO sqlalchemy.engine.base.Engine SELECT CAST('test collated returns' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin AS anon_1

2018-10-10 17:04:18,393 INFO sqlalchemy.engine.base.Engine {}

2018-10-10 17:04:18,414 INFO sqlalchemy.engine.base.Engine DESCRIBE `student`

2018-10-10 17:04:18,414 INFO sqlalchemy.engine.base.Engine {}

2018-10-10 17:04:18,430 INFO sqlalchemy.engine.base.Engine

DROP TABLE student

2018-10-10 17:04:18,430 INFO sqlalchemy.engine.base.Engine {}

2018-10-10 17:04:18,447 INFO sqlalchemy.engine.base.Engine COMMIT

2018-10-10 17:04:18,468 INFO sqlalchemy.engine.base.Engine DESCRIBE `student`

2018-10-10 17:04:18,468 INFO sqlalchemy.engine.base.Engine {}

2018-10-10 17:04:18,482 INFO sqlalchemy.engine.base.Engine ROLLBACK

2018-10-10 17:04:18,494 INFO sqlalchemy.engine.base.Engine

CREATE TABLE student (

id INTEGER NOT NULL AUTO_INCREMENT,

name VARCHAR(64) NOT NULL,

age INTEGER,

PRIMARY KEY (id)

)

2018-10-10 17:04:18,494 INFO sqlalchemy.engine.base.Engine {}

2018-10-10 17:04:18,537 INFO sqlalchemy.engine.base.Engine COMMIT

2018-10-10 17:04:18,562 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)

2018-10-10 17:04:18,563 INFO sqlalchemy.engine.base.Engine INSERT INTO student (name, age) VALUES (%(name)s, %(age)s)

2018-10-10 17:04:18,563 INFO sqlalchemy.engine.base.Engine {'age': 20, 'name': 'tom'}

2018-10-10 17:04:18,574 INFO sqlalchemy.engine.base.Engine INSERT INTO student (name, age) VALUES (%(name)s, %(age)s) #用了參數化查詢

2018-10-10 17:04:18,574 INFO sqlalchemy.engine.base.Engine {'age': 18, 'name': 'jerry'}

2018-10-10 17:04:18,586 INFO sqlalchemy.engine.base.Engine COMMIT

6、adding and updating：

CRUD操作；

每一個實體都有一個狀態屬性_sa_instance_state，其類型是sqlalchemy.orm.state.InstanceState，可使用sqlalchemy.inspect(entity)函數查看狀態；

常見的狀態有：

transient（短暫的，路過的），實體類尚未加入到session中，同時并沒有保存到數據庫中；

pending（未決定的，行將發生的），transient的實體被add()到session中，狀態切換為pending，但還未flush到DB中；

persistent（持久穩固的，堅持的，固執的），session中的實體對象對應著DB中的真實記錄，pending狀態在提交成功后變為persistent狀態，或查詢成功返回的實體也是persistent狀態；

deleted（已刪除的），實體被刪除且已flush但未commit完成，事務提交成功了，實體變成detached，事務失敗返回persistent狀態；

detached（單獨的，冷漠的，超然而客觀的），刪除成功的實體進入這個狀態；

新建一個實體，狀態是transient臨時的；

一旦add()后，由transient-->pending；

成功commit()后，由pending-->persistent；

成功查詢返回的實體對象，也是persistent；

persistent狀態的實體，依然是persistent狀態；

persistent狀態的實體，刪除后，已flush但沒commit()，轉為deleted，事務成功提交，轉為detached，事務提交失敗，轉為persistent；

只有在persistent狀態的實體，才能delete和update，即刪除、修改操作，；

例，commit()后的增：

try:

stu1 = Student()

stu1.name = 'tom'

stu1.age = 20

# student.id = 100

print(stu1.id)

session.add(stu1)

# stu2 = Student(name='jerry', age=18)

# session.add_all([stu1, stu2])

session.commit()

print('~~~~~~~~~~~~~~~~~~~~~~~~~~~')

print('@@@@@@@', stu1.id)

stu1.age = 22 #session.commit()后再改，會先查詢

session.add(stu1) #再次session.add()和session.commit()，由于id為PK且自增，無論有無stu1.age=22都會新增一條記錄；若id不是自增，有stu1.age=22則是update一條記錄

session.commit() #始終與狀態有關，感知到stu1有變化才會提交，能否提交成功看stu1有無變化，有變化了才提交（id為autoincrement，此例只要執行就會新增一條記錄）；stu1主鍵沒有值，就是新增，主鍵有值，就是找到對應的記錄修改

except Exception as e:

print(e)

session.rollback()

finally:

pass

輸出：

……

2018-10-10 21:20:13,812 INFO sqlalchemy.engine.base.Engine {}

None

2018-10-10 21:20:13,839 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)

2018-10-10 21:20:13,840 INFO sqlalchemy.engine.base.Engine INSERT INTO student (name, age) VALUES (%(name)s, %(age)s)

2018-10-10 21:20:13,841 INFO sqlalchemy.engine.base.Engine {'age': 20, 'name': 'tom'}

2018-10-10 21:20:13,852 INFO sqlalchemy.engine.base.Engine COMMIT

~~~~~~~~~~~~~~~~~~~~~~~~~~~

2018-10-10 21:20:13,881 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)

2018-10-10 21:20:13,881 INFO sqlalchemy.engine.base.Engine SELECT student.id AS student_id, student.name AS student_name, student.age AS student_age

FROM student

WHERE student.id = %(param_1)s

2018-10-10 21:20:13,881 INFO sqlalchemy.engine.base.Engine {'param_1': 16}

@@@@@@@ 16

2018-10-10 21:20:13,894 INFO sqlalchemy.engine.base.Engine UPDATE student SET age=%(age)s WHERE student.id = %(student_id)s

2018-10-10 21:20:13,894 INFO sqlalchemy.engine.base.Engine {'age': 22, 'student_id': 16}

2018-10-10 21:20:13,909 INFO sqlalchemy.engine.base.Engine COMMIT

例，簡單查詢：

try:

queryobj = session.query(Student).filter(Student.id==8) #query()方法將實體類傳入，返回類對象（是可迭代對象，查看源碼有__iter__()），這時候并不查詢，迭代它就執行sql來查詢數據庫，封裝數據到指定類的實例；get()方法使用主鍵查詢，返回一條傳入類的一個實例

# queryobj = session.query(Student) #無條件

for i in queryobj:

print('########', i)

except Exception as e:

print(e)

輸出：

……

2018-10-10 22:42:32,931 INFO sqlalchemy.engine.base.Engine SELECT student.id AS student_id, student.name AS student_name, student.age AS student_age

FROM student

WHERE student.id = %(id_1)s

2018-10-10 22:42:32,931 INFO sqlalchemy.engine.base.Engine {'id_1': 8}

######## <Student id:8 name:tom age:26>

例，改，錯誤示例：

try:

stu1 = Student()

stu1.id = 2 #這種不是改，而是是一個全新的stu1，如果該id已有，會PK沖突；正確改的做法，先查再改，得到PK才能改

stu1.name = 'jerry'

stu1.age = 28

session.add(stu1)

session.commit()

except Exception as e:

print(e)

session.rollback()

finally:

pass

輸出：

2018-10-11 08:07:21,772 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)

2018-10-11 08:07:21,773 INFO sqlalchemy.engine.base.Engine INSERT INTO student (id, name, age) VALUES (%(id)s, %(name)s, %(age)s)

2018-10-11 08:07:21,773 INFO sqlalchemy.engine.base.Engine {'age': 28, 'id': 2, 'name': 'jerry'}

2018-10-11 08:07:21,785 INFO sqlalchemy.engine.base.Engine ROLLBACK

(pymysql.err.IntegrityError) (1062, "Duplicate entry '2' for key 'PRIMARY'") [SQL: 'INSERT INTO student (id, name, age) VALUES (%(id)s, %(name)s, %(age)s)'] [parameters: {'age': 28, 'id': 2, 'name': 'jerry'}]

例，改：

先查回來，修改后，再提交；

改不能改PK字段；

先SELECT再UPDATE；

try:

stu1 = session.query(Student).get(2)

print('$$$$$$$', stu1)

stu1.name = 'jowin'

stu1.age = 28

print('#######', stu1)

session.add(stu1)

session.commit()

except Exception as e:

print(e)

session.rollback()

finally:

pass

輸出：

2018-10-11 08:39:56,595 INFO sqlalchemy.engine.base.Engine SELECT student.id AS student_id, student.name AS student_name, student.age AS student_age

FROM student

WHERE student.id = %(param_1)s

2018-10-11 08:39:56,595 INFO sqlalchemy.engine.base.Engine {'param_1': 2}

$$$$$$$ <Student id:2 name:tom age:24>

####### <Student id:2 name:jowin age:28>

2018-10-11 08:39:56,607 INFO sqlalchemy.engine.base.Engine UPDATE student SET name=%(name)s, age=%(age)s WHERE student.id = %(student_id)s

2018-10-11 08:39:56,608 INFO sqlalchemy.engine.base.Engine {'age': 28, 'student_id': 2, 'name': 'jowin'}

2018-10-11 08:39:56,619 INFO sqlalchemy.engine.base.Engine COMMIT

例，刪，錯誤示例：

try:

stu1 = Student(id=2, name='sam', age=26)

session.delete(stu1)

session.commit()

except Exception as e:

print(e)

session.rollback()

finally:

pass

輸出：

Instance '<Student at 0xa59438>' is not persisted #未持久的異常

例，刪：

正確做法，先查再刪；

from sqlalchemy import inspect

try:

stu1 = session.query(Student).get(2)

session.delete(stu1)

print('$$$$$$$$$$$$', inspect(stu1))

session.commit()

print('##########', inspect(stu1))

except Exception as e:

print(e)

session.rollabck()

finally:

pass

輸出：

2018-10-11 08:52:12,317 INFO sqlalchemy.engine.base.Engine SELECT student.id AS student_id, student.name AS student_name, student.age AS student_age

FROM student

WHERE student.id = %(param_1)s

2018-10-11 08:52:12,318 INFO sqlalchemy.engine.base.Engine {'param_1': 2}

$$$$$$$$$$$$ <sqlalchemy.orm.state.InstanceState object at 0x000000000401B400>

2018-10-11 08:52:12,330 INFO sqlalchemy.engine.base.Engine DELETE FROM student WHERE student.id = %(id)s

2018-10-11 08:52:12,330 INFO sqlalchemy.engine.base.Engine {'id': 2}

2018-10-11 08:52:12,342 INFO sqlalchemy.engine.base.Engine COMMIT

########## <sqlalchemy.orm.state.InstanceState object at 0x000000000401B400>

例，刪：

from sqlalchemy import inspect

def show(entity):

ins = inspect(entity)

print('~~~~~~~~~~~~~~~', ins.transient, ins.pending, ins.persistent, ins.detached)

try:

# print('~~~~~~~~~~~~~', Student.__dict__)

stu1 = session.query(Student).get(4)

session.delete(stu1)

# ins = inspect(stu1)

# print('$$$$$$$$$$$$', ins)

show(stu1)

session.commit()

# ins = inspect(stu1)

# print('##########', ins)

show(stu1)

except Exception as e:

print(e)

session.rollabck()

finally:

pass

輸出：

018-10-11 14:40:28,111 INFO sqlalchemy.engine.base.Engine SELECT student.id AS student_id, student.name AS student_name, student.age AS student_age

FROM student

WHERE student.id = %(param_1)s

2018-10-11 14:40:28,111 INFO sqlalchemy.engine.base.Engine {'param_1': 4}

~~~~~~~~~~~~~~~ False False True False

2018-10-11 14:40:28,126 INFO sqlalchemy.engine.base.Engine DELETE FROM student WHERE student.id = %(id)s

2018-10-11 14:40:28,126 INFO sqlalchemy.engine.base.Engine {'id': 4}

2018-10-11 14:40:28,152 INFO sqlalchemy.engine.base.Engine COMMIT

~~~~~~~~~~~~~~~ False False False True

總結：

config.py

USERNAME = 'blog'

PASSWD = 'blog'

IP = '10.10.103.8'

PORT = '3306'

DBNAME = 'blog'

PARAMS = 'charset=utf8mb4'

URL = 'mysql+pymysql://{}:{}@{}:{}/{}?{}'.format(USERNAME, PASSWD, IP, PORT, DBNAME, PARAMS)

DB_DEBUG = True

models.py

from . import config

from sqlalchemy import create_engine

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy import Column, Integer, String, BigInteger, DateTime

from sqlalchemy import ForeignKey, UniqueConstraint, PrimaryKeyConstraint

from sqlalchemy.orm import relationship, sessionmaker

from sqlalchemy.dialects.mysql import LONGTEXT, TINYINT

Base = declarative_base()

class User(Base):創建表

__tablename__ = 'user'

id = Column(Integer, primary_key=True, autoincrement=True)

name = Column(String(48), nullable=False)

password = Column(String(128), nullable=False)

email = Column(String(64), nullable=False, unique=True)

def __repr__(self):

return '<User (id={}, name={}, email={})>'.format(self.id, self.name, self.email)

engine = create_engine(config.URL, echo=config.DB_DEBUG)

def create_all():

Base.metadata.create_all(engine)一旦使用該方法將模型映射到數據庫后，即使改變了模型的字段，也不會重新映射了

def drop_all():

Base.metadata.drop_all(engine)

Session = sessionmaker(bind=engine)

session = Session()使用orm對DB操作必須通過session對象實現

注：

conn = engine.connect() #調用引擎的connect()得到一個對象

result = conn.execute('select version()') #通過conn對象就可對DB進行操作

print(result.fetchone())

Column常用屬性：

default: 默認值

nullable: 是否可空

primary_key: 是否為主鍵

unique: 是否唯一

autoincrement: 是否自增長

name: 該屬性再數據庫中的字段映射

onupdate: 當數據更新時會自動使用這個屬性，比如update_time = Colum(DateTime, notallow=datetime.now, default=datetime.now)

常用數據類型：

Integer: 整型

Float: 浮點型，后面只會保留4位小數，會有精度丟失問題，占據32位

Double: 雙精度浮點類型，占據64位，也會存在精度丟失問題

DECIMAL: 定點類型，解決浮點類型精度丟失問題；如果精度要求高，比如金錢，則適合用此類型

Boolean: 傳遞True/False進行

enum: 枚舉類型

Date: 傳遞datetime.date()進去

Datetime: 傳遞datetime.datetme()進去

Time: 傳遞datetime.time()進去

String: 字符類型，使用時需要指定長度，區別于Text類型

Text: 文本類型，一般可以存儲6w多個字符

LONGTEXT: 長文本類型

from sqlalchemy.dialects.mysql import LONGTEXT

因為LONGTEXT只在MySQL數據庫中存在

關于“sqlalchemy如何使用”這篇文章的內容就介紹到這里，感謝各位的閱讀！相信大家對“sqlalchemy如何使用”知識都有一定的了解，大家如果還想學習更多知識，歡迎關注億速云行業資訊頻道。