SQLAlchemy database connection and initialization database

View version

>>> import sqlalchemy>>> sqlalchemy.__version__‘1.0.9‘

Create a connection

from sqlclachemy import create_engineengine = create_engine("sqlite:///:memory:", echo=True)

' Sqlite:///:memory: ' is the database URL

PostgreSQL

SQLAlchemy Default Use PSYCOPG2

# 默认情况(即使用psycopg2)engine = create_engine(‘postgresql://scott:[email protected]/mydatabase‘)# 使用psycopg2engine = create_engine(‘postgresql+psycopg2://scott:[email protected]/mydatabase‘)# 使用pg8000engine = create_engine(‘postgresql+pg8000://scott:[email protected]/mydatabase‘)

Mysql

Use Mysql-python by default

# 默认情况（即使用mysql-python）engine = create_engine(‘mysql://scott:[email protected]/foo‘)# 使用mysql-pythonengine = create_engine(‘mysql+mysqldb://scott:[email protected]/foo‘)# 使用MySQL-connector-pythonengine = create_engine(‘mysql+mysqlconnector://scott:[email protected]/foo‘)# 使用OurSQLengine = create_engine(‘mysql+oursql://scott:[email protected]/foo‘)

Oracle

Use Cx_oracle by default

＃ 默认情况（即使用cx_oracle）engine = create_engine(‘oracle://scott:[email protected]:1521/sidname‘)# 使用cx_oracleengine = create_engine(‘oracle+cx_oracle://scott:[email protected]‘)

Microsoft SQL Server

Use PYODBC by default

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

Sqlite

Because SQLite is a file-based database, the databases URL is not the same as the previous one.

# database URL 形式是 sqlite://<nohostname>/<path>engine = create_engine(‘sqlite:///foo.db‘)# 在Unix/Macengine = create_engine(‘sqlite:////absolute/path/to/foo.db‘)# 在Windowsengine = create_engine(‘sqlite:///C:\\path\\to\\foo.db‘)# 在Windows 中使用原始字符串engine = create_engine(r‘sqlite:///C:\path\to\foo.db‘)# 使用内存engine = create_engine(‘sqlite://‘)engine = create_engine(‘sqlite:///:memory:‘)

Create a table

With ORM, you need to describe the table first and then map the class to the table. In Sqlaclhemy these two steps are usually together, using a system called declarative, let's create a class that contains a description of the table we need to create

Create declarative base class

You need to create a base class first

from sqlalchemy.ext.declarative import declarative_baseBase = declarative_base()

Create Class

For example, to create a user class, the user class needs to inherit the base created earlier.

from sqlalchemy import Column, Integer, Stringclass User(Base): __tablename__ = ‘users‘ id = Column(Integer, primary_key=True) name = Column(String) fullname = Column(String) password = Column(String) def __repr__(self): return "<User(name=‘%s‘, fullname=‘%s‘, password=‘%s‘)>" % ( self.name, self.fullname, self.password) 

You need to use TableName to name the table. column indicates the field.

When a class is known to be ORM, the declarative system uses the python's meta-class to create a table class that contains information about the tables, which can be passed by user. Table_ ), and then connect the table to the ORM class through Mapper. All the table classes of the ORM class are contained in a metadata class, and the metadata class can be viewed through base.metadata

Declarative systems are not required for SQLAlchemy ORM, such as:

From SQLAlchemyImport Table, MetaData, Column, Integer, String, ForeignKeyfrom sqlalchemy.orm import mappermetadata = MetaData () user = Table ( "user", metadata, Column ( ' id ', Integer, primary_key=< Span class= "Hljs-keyword" >true), Column ( ' name ', string (50)), Column ( ' fullname ', string (50)), column (  ' password ', string (12))) class user def __init__ (self, name, fullname, password): Self.name = Name Self.fullname = FullName Self.password = passwordmapper (user, user)        

Create User table

Base.metadata.create_all(engine) 

Basic curd operation

Need to create a session to manipulate the table

Create a session

from sqlalchemy.orm import sessionmakerSession = sessionmaker(bind=engine)Session = sessionmaker()‘‘‘orSession = sessionmaker()Session.configure(bind=engine)‘‘‘session = Session()

Inserting data

ed_user = User(name=‘ed‘, fullname=‘Ed Jones‘, password=‘edspassword‘)session.add(ed_user)

Add multiple lines at once

session.add_all([    User(name=‘wendy‘, fullname=‘Wendy Williams‘, password=‘foobar‘),    User(name=‘mary‘, fullname=‘Mary Contrary‘, password=‘xxg527‘), User(name=‘fred‘, fullname=‘Fred Flinstone‘, password=‘blah‘)])

At this point the data is not inserted into the database, Ed_user.id is none. Commit is required if you want to actually insert the database.

session.commit()

If you want to operate just in memory, not really commit,ed_user.id not for none, you can use the flush operation, which is just written into memory.

session.flush()

Rollback operation

You can roll back when there is no commit

session.rollback()

Read operation, querying

Flush is performed automatically before the query operation is performed.
Query Example:

for instance  In Session.query (User). order_by (User. ID): Print (Instance. Name, instance.fullname) # can take only some of these fields for name, fullname in session.query (User. Name, User.fullname): Print (name, fullname) for row in session.query (User, User. Name). All (): print (row. User, Row. name) for row in session.query (User. Name.label (' Name_label ')). All (): print (Row.name_label)         

You can alias a table using alias, which can be used when the table join itself.

from sqlalchemy.orm import aliaseduser_alias = aliased(User, name=‘user_alias‘)for row in session.query(user_alias, user_alias.name).all(): print(row.user_alias)

Using filter_by filtering

for name, in sesson.query(User.name).filter_by(fullname=‘Ed Jones‘):    print(name)

You can also use filter, which is more flexible

for name, in sesson.query(User.name).filter(User.fullname==‘Ed Jones‘):    print(name)

#相等query.Filter (User.Name = =' Ed ')#不相等query.Filter (User.Name! =' Ed ')#likequery.Filter (User.name.like ('%ed% '))#inquery.Filter (User.name.in_ ([' Ed ',' Wendy ',' Jack ']) query.Filter (User.name.in_ (Session.query (user.name).Filter (User.name.like ('%ed% '))))#not Inquery.Filter (~user.name.in_ ([' Ed ',' Wendy ',' Jack ')])#IS Nullquery.Filter (User.Name = = None) query.Filter (User.name.is_ (None))#IS not nukkquery.Filter (User.Name! = None) query.Filter (User.name.isnot (None))#Andfrom sqlalchemy import and_query. filter (and_ (user.name =  ' ed ', user.fullname =  Ed Jones") query. filter (user.name =  ' ed ', user.fullname =  Ed Jones ') query. filter (user.name =  ' Ed ').  Filter (User.fullname = =  ' Ed Jones ')  #OR from sqlalchemy import or_query. filter (or_ (user.name =  ' ed ', User.Name =  Wendy '))  #Matchquery.  Filter (User.name.match (       

Return to List

All () operation, take out all at once, and return a list.

query = session.query(User).filter(Usre.name.like(‘%ed%‘)).all()

First () takes out one, if not, returns none

obj = query.first()

One () take out a, if the filter results more than one, report multipleresultsfound wrong, if not reported noresultfound wrong
One_or_none () Take out one, if the filter result is not more than one, report multipleresultsfound error, if not return none
Scalar () just like One_or_none ()

Using the original SQL

query = session.query(User).filter(‘id<200‘).order_by(text(‘id‘)).all()

Using Parameters

query = session.query.(User).filter(text(‘id<:value and name=:name‘)).params(value=224, name=‘fred‘).order_by(User.id).one()

Using a fully string-based

query = session.query(User).from_statement(    text(‘SELECT * FROM users where name=:name‘).params(name=‘ed‘).all()).all()

Count

count = session.query(User).filter(User.name.like(‘%ed%‘)).count()

from sqlalchemy import funcquery = session.query(func.count(User.name), User.name).group_by(User.name).all()

session.query(func.count(‘*‘)).select_from(User).scalar()

session.query(func.count(User.id)).scalar()

Update

‘haha‘session.commit()

Delete

session.delete(user)

SQLAlchemy database connection and initialization database