The ORM: SQLAlchemy first meeting - Moment For Technology

ORM: Object Relational Mapper.

Python currently has a number of ORM tools that can be used to image databases as Python Objects. Some of the better known are Django’s ORM, SQLAlchemy, PostgreSQL, and so on. SQLAlchemy is maintained by more people and features are more complete. So that’s usually our preference.

For SQLAlchemy users, once you start connecting to a database, whether it’s SQLite, MySQL, or whatever, the process is exactly the same. This convenience also accounts for its popularity.

Instead of weaving your own SQL statements, making models, and connecting to databases, SQLAlchemy puts it all in a black box so you don’t have to do anything about it. It also helps prevent things like SQL-injection. In this way, we can say that in the connection to the database, we saved at least half of the code.

Even for data queries, SQLAlchemy replaces SQL statements with a specialized MongoDb-like object.query.filter_by (name=’Jason’).all() method.

Installation:

# sqlalchemy $PIP install sqlalchemy

Install the software:

# MySQL $PIP install pymysql # Postgresql $PIP install psycopg2 # MySQL $PIP install pymysql # Postgresql $PIP install psycopg2

SQLAlchemy does not come with database driver itself, so we need to install it ourselves and specify it when connecting. These drivers are actually the packages that we used to connect to the database manually. SQLAlchemy just uses these same packages instead of us.

Connecting to a database

Create a SQLite ORM engine:

SqlAlchemy import create_engine from sqlAlchemy import create_engine sqlite://<Hostname>/<path> engine = create_engine('sqlite:///foo.db', echo=True)

MySQL > create an ORM engine for MySQL

SqlAlchemy import create_engine from sqlAlchemy import create_engine dialect+driver://username:password@host:port/database engine = create_engine('mysql+pymysql://root:password123@localhost/db_test_01', echo=True)

The location of the database (relative path with three slashes, absolute path with four slashes) :

# db file using absolute path (////), Such as/TMP/mydatabase. Db engine = create_engine (' sqlite: / / / / TMP/mydatabase. Db ') # use the current "execution" database file (/ / / / / / / /) engine = Create_engine ('sqlite:///mydatabase.db') # Use the parent directory of the current "execution location" (///.. /) engine = create_engine('sqlite:///.. /mydatabase.db') # import OS CWD = os.path.split(os.path.realpath(__file__))[0] engine = create_engine('sqlite:///{}/mydatabase.db'.format(cwd))

CREATE TABLES

Note: Unlike SQL statements, the table names in SQLAlchemy are
Completely case sensitive!

Create a table with no ORM objects in it:

from sqlalchemy import create_engine, MetaData from sqlalchemy import Table, Column from sqlalchemy import Integer, String, ForeignKey engine = create_engine('mysql+pymysql://root:password123@localhost/db_test_01', User_table = Table('tb_user', metadata, Column('id', Integer)); Primary_key =True), Column('name', String(50)), Column('fullname', String(100)))

Create an ORM object (including the table) :

SqlAlchemy import create_engine from sqlAlchemy import Column, Integer, String, ForeignKey # import create ORM model related to the from sqlalchemy. Ext declarative import declarative_base Base = declarative_base () class User(Base): __tablename__ = 'tb_Person' id = Column('id', Integer, primary_key=True) username = Column('username', String, unique=True) engine = create_engine('sqlite:///test.sqlite', echo=True) Base.metadata.create_all(bind=engine)

What is the difference between a Table created with a regular Table and an ORM object? They create the exact same table in the database! The only difference is that the Table does not contain ORM objects, which means it does not provide you with the ability to manipulate Python objects directly. The advantage of this is that there are many tables that are just associative, and there is no need to generate ORM objects.

Delete the table in the database

# engine = ... # Base = ... Drop (engine); drop(engine); delete (base.metadata. drop(engine);

During design or debugging, tables are frequently changed, so it is necessary to clear the tables from the test database before creating a table and then create a new definition.

Insertion data

Add data to database:

#... SqlAlchemy. Orm import sessionmaker from sqlAlchemy. Orm import sessionmaker from sqlAlchemy. Orm import sessionmaker from sqlAlchemy. relationship user = User() user.id = 1 user.username = 'Jason' Session = sessionmaker(bind=engine) session = Session() session.add(user) session.commit() session.close()

Note: The concept of session here is a little different from the concept of session on the website. This is the tool used to commit database changes.

Batch add data (pass the list to add_all()) :

session.add_all( [user1, user2, user3] )

Automatic flush() when adding each data item:

session = sessionmaker(bind=engine, autoflush=True)

Autoflush automatically executes session.flush() on every session.add(), generating dynamic data (primary key ID, etc.) for all objects in memory before inserting them into the database. The default is false, because it affects efficiency. Preferably, only manually execute session.flush() when needed

See the next section, “Data Works.”

The data takes effect

Both create_all() and session.mit () in SQLAlchemy are statements that directly make objects defined in Python files work in the database. Until then, no matter how you define it, the data is in memory, not in the database.

Pay attention to the distinction:

create_allJust allow the creation of the table structure to take effect, not the data entry for the INSERT
session.commit()Just allow the added data to take effect and not be responsible for any table structure.

The order of these two, of course, is to create the table and then insert the data.

However, if we know this principle, we can use freedom more in coding. For example, even the create_engine() creation engine can be defined later, not necessarily in the file header, which is before all ORM definitions. CREATE_ENGINE is defined only after all ORM classes and schemas.

After that, we start inserting data, which makes use of the Session.

In the course of sessions, we will also encounter situations where we refer to each other’s primary key and foreign key IDs. Note, however, that at this point, since we haven’t actually committed the data to the database using the final session.com MIT (), these IDs have no value. The solution is to use the built-in session.flush() method to populate all the objects that have been added to the session, but not yet committed to the database, but we have internal access to the various IDs.

Update/delete data

Update:

# Get a row of data
me = session.query(User).filter_by(username='Jason').first()

# Method 1:
me.age += 1
session.commit()

# Method 2:
session.query().filter(
    User.username == 'Jason'
).update(
    {"age": (User.age +1)}
)
session.commit()

# Method 3:
setattr(user, 'age', user.age+1)
session.commit()

Get Primary Key Value

# sqlAlchemy can’t get primary key, # sqlAlchemy can’t get primary key

It took me a lot of time to explore the query and find the solution before I realized that it was pretty obvious.

SQLAlchemy returns the ID of the newly inserted data?

Although we can browse property values in objects created from the ORM directly before inserting data without Session or Engine. However, the value of the PRIMAR_KEY primary key column cannot be retrieved at this time anyway.

Because the primary key hasn’t been inserted into the database yetThe value of the dynamicIs None until the database is in effect.

Why do we need to get value of primary_key? Consider these scenarios:

In the child tableforeign keyThe foreign key needs to refer to the ID of the primary table
??

So how do I get the primary key ID?

See also StackOverflow: SQLAlchemy Flush () and Get INSERTED ID? SQLAlchemy = insert id; Set the primary key to a pre-existing database table (without SQLite)

If you want to get the value of a dynamic column, such as a primary key, before you insert the data, there are several methods:

Use SQLAlchemy directly to establish the internal association of the class directly, instead of using ID directly
The primary table inserts data, and the session takes effect. Query fetches the corresponding object and retrieves its ID.
(*) The master table is used firstsession.add(..)Again,session.flush(), then you can get the ID, and finallysession.commit()
Do not use the primary key, create your own manual ID, so that you can arbitrarily obtain.

Recommended practices are as follows:

Every time a new object is created, session.add(..) will be created immediately. , then immediately session.flush(), add all good text ends, and session.mit ().

Query Query

Session.com MIT () is a session query, so the query must be run after session.com MIT (), otherwise it will report an error.

Query Query Query Query Query Query Query
After the insert into the database takes effect. This is important to understand, because a lot of primary keys, foreign keys, and so on don’t exist until the object is inserted into the database, so they can’t be queried.

PythonSheets – Object Relational Basic Query

Query data:

session.commit() # ... Users = session.query(User).all() # return User: >>> [<User 1>, <User 2>,....]  for u in users: print(u.id, u.username)

Common query methods:

>>> session.query(User).all() # all rows of data >>> session.query(User).first() # first Order_by (row of data as an object) >>> session.query(User).order_by(user.birth).all() # Filter (True/False expression) >>> session.query(User).filter( User.name ! = 'Jason' ).all() >>> session.query(User).filter( User.name.like('%ed%') ).all() # Fuzzy search >>> session.query(User).filter( User.id in [1, 2, 3] ).all() # IN >>> session.query(User).filter( ~ User.id in [4, 5, 6] ).all() # NOT IN >>> session.query(User).filter( User.school == 'MIT', User.age < 24 ).first() # AND >>> session.query(User).filter( _or(User.school == 'MIT', User.age < 24) ).first() # OR