Example of using Core mode of SQLAlchemy

SQLAlchemy: What is the experience of operating data in the Core mode?

Answer: Cool!

This article is based on: Win + python 3.4 + sqlalchemy 1.0.13

The basic steps are as follows:

1. Binding the Database

 from Import  = create_engine ('sqlite:///:memory:', echo=true)

2. Connect to the database

conn = Engine.connect ()

3. Meta-data

Import MetaData

metadata = metadata (engine)

4. Defining tables

 fromSQLAlchemyImportTable, Column, Integer, String, ForeignKey, Sequence
Users= Table ('Users', metadata, Column ('ID', Integer, Sequence ('User_id_seq'), primary_key=True), Column ('name', String), Column ('FullName', String),) addresses= Table ('addresses', metadata, Column ('ID', Integer, primary_key=True), Column ('user_id', None, ForeignKey ('users.id')), Column ('email_address', String, nullable=False))

5. Create a table

# Metadata.drop_all ()
Metadata.create_all ()

6. Insert

#Way OneINS = Users.insert (). VALUES (name='Jack', fullname='Jack Jones .')conn.execute (INS)#Mode twoConn.execute (Users.insert (), id=2, name='Wendy', fullname='Wendy Williams')#Way ThreeConn.execute (Addresses.insert (), [{'user_id': 1,'email_address':'[email protected]'},    {'user_id': 1,'email_address':'[email protected]'},    {'user_id': 2,'email_address':'[email protected]'},    {'user_id': 2,'email_address':'[email protected]'}, ])

7. Enquiry

 fromSqlalchemy.sqlImportSelect
 forRowinchconn.execute (select ([Users])):Print("Name:", Row[users.c.name],"; FullName:", Row[users.c.fullname]) forRowinchconn.execute (select ([Users, Addresses]):Print(Row) forRowinchConn.execute (select ([Users, Addresses]). WHERE (users.c.id = =addresses.c.user_id)):Print(Row) fromSqlalchemy.sqlImportAnd_, Or_, not_s= Select ([(Users.c.fullname +", "+addresses.c.email_address). Label ('title')]). Where (And_ (users.c.id==addresses.c.user_id, Users.c.name.between ('m','Z'), Or_ (Addresses.c.email_address.like ('% @aol. com'), Addresses.c.email_address.like ('% @msn. com'))) Conn.execute (s). Fetchall ()

8. Complete code

#Binding Database fromSQLAlchemyImportCreate_engineengine= Create_engine ('sqlite:///:memory:', echo=True)#connecting to a databaseconn =Engine.connect ()#Meta Data fromSQLAlchemyImportMetadatametadata=MetaData (engine)#Defining Tables fromSQLAlchemyImportTable, Column, Integer, String, ForeignKey, Sequenceusers= Table ('Users', metadata, Column ('ID', Integer, Sequence ('User_id_seq'), primary_key=True), Column ('name', String), Column ('FullName', String),) addresses= Table ('addresses', metadata, Column ('ID', Integer, primary_key=True), Column ('user_id', None, ForeignKey ('users.id')), Column ('email_address', String, nullable=False))#Create a table
# Metadata.drop_all ()
Metadata.create_all ()#Insert#Way OneINS = Users.insert (). VALUES (name='Jack', fullname='Jack Jones .') Result=conn.execute (INS)#Mode twoConn.execute (Users.insert (), id=2, name='Wendy', fullname='Wendy Williams')#Way ThreeConn.execute (Addresses.insert (), [{'user_id': 1,'email_address':'[email protected]'},    {'user_id': 1,'email_address':'[email protected]'},    {'user_id': 2,'email_address':'[email protected]'},    {'user_id': 2,'email_address':'[email protected]'}, ])#Enquiry fromSqlalchemy.sqlImportSelect
 forRowinchconn.execute (select ([Users])):Print("Name:", Row[users.c.name],"; FullName:", Row[users.c.fullname]) forRowinchconn.execute (select ([Users, Addresses]):Print(Row) forRowinchConn.execute (select ([Users, Addresses]). WHERE (users.c.id = =addresses.c.user_id)):Print(Row) fromSqlalchemy.sqlImportAnd_, Or_, Not_
S= Select ([(Users.c.fullname +", "+addresses.c.email_address). Label ('title')]). Where (And_ (users.c.id==addresses.c.user_id, Users.c.name.between ('m','Z'), Or_ (Addresses.c.email_address.like ('% @aol. com'), Addresses.c.email_address.like ('% @msn. com'))) Conn.execute (s). Fetchall ()

Example of using Core mode of SQLAlchemy