I. Introduction to SQLAlchemy
1.1 What is SQLAlchemy?
sqlalchemy is a python language implementation of the orm library for relational databases . Can be used to connect to most common databases , such as Postges, MySQL, SQLite, Oracle and so on.
1.2. Why use SQLAlchemy?
It abstracts your code from the underlying database and its associated SQL features.
1.3. SQLAlchemy provides two main usage patterns
- SQL Expression Language (SQLAlchemy Core)
- ORM
1.4 Which model should be chosen?
Although you're using a framework that has an ORM built in, but want to add more powerful reporting capabilities, go with Core.
If you want to view data in a one schema-centric view (user similar to SQL), use Core.
If your data doesn't require business objects, use Core.
If you want to treat data as business objects, use an ORM.
If you want to create prototypes quickly, use ORM.
If you need coworkers to use business objects and other data unrelated to the problem domain, use Core and ORM in combination.
1.5 Connecting to databases
To connect to a database, you need to create a SQLAlchemy engine first.The SQLAlchemy engine creates a public interface for the database to execute SQL statements. This is accomplished by wrapping the database connection pool and dialects (different database clients).
SQLAlchemy provides a function to create the engine. In this function, you can specify the connection string, as well as some other optional keyword arguments.
from sqlalchemy import create_engine
engine = create_engine('sqlite:///')
engine1 = create_engine('sqlite:///:memory:')
engine2 = create_engine('sqlite://///home/cookiemonster/')
engine3 = create_engine('sqlite:///c:\\Users\\cookiemonster\\')
engine_mysql = create_engine('mysql+pymysql://cookiemonster:chocolatechip', '@/cookies', pool_recycle=3600)
1.6, Modalities and types
In order to access the underlying database, SQLAlchemy needs something to represent the tables in the database. To do this, one of the following three total methods can be used:
Using user-defined Table objects
Using declarative classes that represent data tables
Extrapolation from the database
SQLAlchemy core
SQLAlchemy core defines table structures using the 1st way stated in 1.5. table objects contain a series of columns and attributes with types that are associated with a common metadata container.
Metadata can be thought of as a sort of Table object catalog. These tables can be accessed through.
2.1 Define the table structure
In SQLAlchemy Core, we initialize the Table object through the Table constructor. We have to provide MetaData object (metadata) and table name in the constructor, any other parameters are considered as column objects. Columns are created through Column() function.
from sqlalchemy import create_engine
from sqlalchemy import Column
from sqlalchemy import Integer
from sqlalchemy import String
from sqlalchemy import MedaData
metadata = MetaData()
user = table('user', metadata,
Column(id, Integer(), primary_key=True),
Column(name, String(255)),
)
engine = create_engine('sqlite:///:memory:')
metadata.create_all(engine) # Table Persistence
2.2 Insertion of data
First create an INSERT statement that will be used to put Ming into the USER table. To do this, call the insert() method of the users table and then use the values() statement with the keyword arguments being the individual columns and their corresponding values:
ins = ().values( id=1, name='Little Ming' ) print(str(ins))
To this point just create an inset statement, it has not really executed it, the next insertion operation:
connection = () result = (ins) print(result.inserted_primary_key)
2.3. Querying data
To construct a query, the select function is used, which is similar to a standard SQL SELECT statement.
from import select s = select([user]) # You can use str(s) to see the statements that the database sees print(str(s)) rp = (s) results = ()
2.3.1、ResultProxy
The return value of the execute() function is a hot ResultProxy object that allows access using an index, name, or Column object.
Using ResultProxy to process rows
first_row = results[0] first_row[1] first_row.name first_row[]
Iterate over ResultProxy
rp = (s) for record in rp: print(record.user_name)
Access results using the methodology
() # If there is a record, return the first record and close the connection () # Return to a line and keep the cursor open so you can do more to get the calls () # The result of the incoming query is a single record containing a column，then a single value is returned
2.3.2 Controlling the number of columns in a query
s = select([]) rp = (s) print(()) result = ()
2.3.3 Sorting
s = select([]) s = s.order_by() rp = (s) for user in rp: print()
2.3.4 Limiting the number of entries in the returned result set
s = select([]) s = s.order_by() s = (2) rp = (s) for user in rp: print()
2.3.5 Built-in SQL Functions and Tags
from import func s = select([()]) rp = (s) print(())
2.3.6 Filtering
Filtering on queries is done by adding where() statements.
s = select([user]).where( == 'Little Ming') rp = (s) record = () print(())
Here is just an introduction to common queries, more complex queries please refer to the official documentation.
2.4 Updated data
The update() method is very similar to the preceding insert() method in that their syntax is almost identical, but update() can specify a where() clause that indicates which rows are to be updated.
from sqlalchemy import update u = update(user).where( == 'Little Ming') u = (name='Xiaohua') result = (u) print()
2.5 Deletion of data
When creating a delete statement, you can use either the delete() function or the table's delete() method. Unlike insert() and update(), delete() takes no value arguments, only an optional where clause that specifies the deletion paradigm.
from sqlalchemy import delete u = delete(user).where( == 'Xiaohua') result = (u) print()
Attention:
For more advanced operations: joins, aliasing, grouping, chaining calls, raw queries, etc., please consult the official documentation.
2.5 Transactions
Through () to open a transaction, return a transaction object, the next according to the implementation of the call () to commit the changes or call () to roll back the operation.
III. SQLAlchemy orm
SQLAlchemy orm defines table structures using the 2nd way stated in 1.5. This is done by defining a class that inherits from a special base class called declarative_base. declarative_base combines a metadata container with a mapper (which is used to map classes to data tables).
The classes used by orm should fulfill the following four requirements:
- Inherited from declarative_base object.
- Contains __tablename__, which is the name of the table used in the database.
- Contains one or more properties that are column objects.
- Ensure that one or more attributes make up the primary key.
3.1. Define the table structure:
from sqlalchemy import create_engine
from sqlalchemy import Column
from sqlalchemy import Integer
from sqlalchemy import String
from import declarative_base
Base = declarative_base()
class User(Base):
__tablename__ = 'user'
id = Column(Integer, primary_key=True)
name = Column(String(255))
engine = create_engine('sqlite:///')
.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()
3.2. Sessions
A session is the way SQLAlchemy ORM interacts with the database. It wraps the database connection through the engine and provides an identity map for objects loaded through or associated with the session. An identity map is a cache-like data structure that contains a unique list of objects identified by object tables and primary keys. The session also wraps a transaction that will remain open until the session is committed or rolled back.
To create sessions, SQLAlchemy provides a sessionmaker class that ensures that sessions can be created using the same parameters throughout the application. sessionmaker class accomplishes this by creating a Session class that is configured according to the parameters passed to the sessionmaker factory The sessionmaker class accomplishes this by creating a Session class that is configured based on parameters passed to the sessionmaker factory.
from sqlalchemy import create_engine
from import sessionmaker
engine = create_engine('sqlite:///:memory:')
Session = sessionmaker(bind=engine)
session = Session()
3.3. Insertion
user = User(1, 'Little Ming') (user) ()
3.4. Queries
for row in (User): print(, )
Note: The return value of () is a Query object and you cannot use its return value as a query result. For more information on the usage of Query objects, see:/en/13/orm/#
Methods of common Query objects:
q = (User) () # of query results obtained () # Returns a list of query results, which triggers the execution of a SQL query (id) # Query a single object by primary_key q.as_scalar() # Returns this query'sSQLstatement
3.4.1 Controlling the number of columns in a query
print(().first())
3.4.2 Sorting
for record in (user).order_by(): print()
3.4.3 Limiting the number of entries in the returned result set
query = (user).order_by().[:2] print([result.user_name for result in query])
3.4.4 Built-in SQL Functions and Tags
from sqlalchemy import func inv_count = (()).scalar() print(inv_count)
3.4.5 Filtering
record = (user).filter( == 'Xiaohua') print(record)
3.5 Updated data
query = (user) xm_user = (user.user_name == 'Xiaohua').first() xm_user.name = 'robin' () print(xm_user.quantity)
3.6 Deletion of data
query = (user) xm_user = (user.user_name == 'Xiaohua').first() (xm_user) () print(xm_user)
Attention:
Here is a brief introduction to the common usage of SQLAlchemy orm. For more advanced usage, please refer to the official documentation.
IV. Reflections
Using reflection techniques you can take advantage of populating the SQLAlchemy object with the database first.
4.1. Reflecting individual tables
Creates the initial object:
from sqlalchemy import Metadata, create_engines
metadata = MetaData()
engine = reate_engine('sqlite:///')
Reflective Artist Table
from sqlalchmy impport Table
artist = Table('Artist', metadata, autoload=True, autoload_with=engine)
4.2. Reflecting the entire database
(bind=engine)
bibliography
/en/13/core/type_basics.html#generic-types
This is how to use Python SQLAlchemy library in detail, more information about Python SQLAlchemy library please pay attention to my other related articles!