<td id="aqqih"></td>

<dd id="aqqih"></dd>
  1. <span id="aqqih"></span>
  2. <ol id="aqqih"></ol>

    sqlalchemy如何使用

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

    SQLAlchemy:

    是一個ORM框架;

    大量使用元編程;

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

    pip install sqlalchemy pymysql

    pip show sqlalchemy

    sqlalchemy如何使用  sqlalchemy 第1張

    > import sqlalchemy

    > sqlalchemy.__version__ #version check

    sqlalchemy如何使用  sqlalchemy 第2張

    開發中,一般都采用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:

    sqlalchemy如何使用  sqlalchemy 第3張

    注:

    內部使用了連接池;

    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如何使用”知識都有一定的了解,大家如果還想學習更多知識,歡迎關注蝸牛博客行業資訊頻道。

    免責聲明:本站發布的內容(圖片、視頻和文字)以原創、轉載和分享為主,文章觀點不代表本網站立場,如果涉及侵權請聯系站長郵箱:niceseo99@gmail.com進行舉報,并提供相關證據,一經查實,將立刻刪除涉嫌侵權內容。

    評論

    日本韩欧美一级A片在线观看
    <td id="aqqih"></td>

    <dd id="aqqih"></dd>
    1. <span id="aqqih"></span>
    2. <ol id="aqqih"></ol>