SQLAlchemy is an OR mapper that is often used when accessing DB with python. Using sqlalchemy to access DB with python summarizes things other than SQL. This time, I've summarized the things that may be a problem if you don't understand SQLAlchemy.
You can use SQL statements directly in SQLAlchemy in addition to updating with ORM. Please note that the intended information cannot be obtained if the method of updating from the DB and the method of acquiring it are different.
Even within the same session, if you try to get the changed contents by SQL statement before commit with ORM, the one before update will be taken. The reverse is also true.
After updating with SQL statement or ORM, get it with SQL statement and ORM and display the contents.
If you set up a new session, you will go to get the DB information, so both the update function and the display function have the same session. Also, since the variable at the time of update does not want to affect the variable at the time of display, update and display are created as separate functions. Please see Previous for creating a session.
main.py
def get_mapper_and_query(local_session):
print('============== query get ===============')
records = local_session.execute("select * from pets where id = 3;")
for record in records:
print(record)
print('============== mapper get ===============')
pet2 = local_session.query(Pets).filter(Pets.id==3).first()
print(pet2)
def update_mapper_and_query():
local_session = SESSION()
print('*************************** default ***************************')
get_mapper_and_query(local_session)
print('*************************** default ***************************\n')
print('*************************** mapper update ***************************')
pet = local_session.query(Pets).filter(Pets.id==3).first()
pet.age = 10
get_mapper_and_query(local_session)
print('*************************** mapper update ***************************')
print('*************************** query update ***************************')
local_session.execute("update pets set age=20 where id = 3;")
get_mapper_and_query(local_session)
print('*************************** query update ***************************\n')
Originally age was 5, but it is updated to 10 only when it is acquired by ORM after updating ORM. Furthermore, after the SQL statement is updated, the age is updated to 20 when the SQL statement is acquired, and 10 when the ORM is updated remains as it is when the ORM is acquired. In this way, if each update method and acquisition method are different, what you can get will be different.
*************************** default ***************************
============== query get ===============
(3, 'mink', 5, datetime.datetime(1990, 2, 12, 0, 0))
============== mapper get ===============
id:3, name:mink, age:5, birthday:1990-02-12 00:00:00
*************************** default ***************************
*************************** mapper update ***************************
============== query get ===============
(3, 'mink', 5, datetime.datetime(1990, 2, 12, 0, 0))
============== mapper get ===============
id:3, name:mink, age:10, birthday:1990-02-12 00:00:00
*************************** mapper update ***************************
*************************** query update ***************************
============== query get ===============
(3, 'mink', 20, datetime.datetime(1990, 2, 12, 0, 0))
============== mapper get ===============
id:3, name:mink, age:10, birthday:1990-02-12 00:00:00
*************************** query update ***************************
SQLAlchemy has a connection pool function. If you want to keep the program running permanently, the number of connections will increase steadily unless you understand when the connections are created.
When I created a WEB service to acquire DB information with flask, I reached the upper limit of the number of DB sessions and started to make an error.
Create a service to get information from DB with flask and check the number of connections on the DB side.
It is a source that creates a session to DB and returns information when a request is received. This time, the application name py_app is given to the DB session for easy understanding. For DB connection, Access DB with python using sqlalchemy, for flask, [How to return http status with flask](https: / See /qiita.com/mink0212/items/52e0ebd66bd94e1303c1).
main.py
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from flask import Flask, jsonify
from Pets import Pets
DATABASE = 'postgresql'
USER = 'postgres'
PASSWORD = 'postgres'
HOST = 'localhost'
PORT = '5431'
DB_NAME = 'animal_db'
CONNECT_STR = '{}://{}:{}@{}:{}/{}'.format(
DATABASE,
USER,
PASSWORD,
HOST,
PORT,
DB_NAME
)
app = Flask(__name__)
@app.route('/hello/<name>')
def hello(name):
engine = create_engine(CONNECT_STR, connect_args={"application_name":"py_app"})
session = sessionmaker(engine)()
pets = session.query(Pets).filter(Pets.name==name).all()
return jsonify(pets)
if __name__ == "__main__":
app.run()
postgres=# select count(*) from pg_stat_activity where application_name = 'py_app';
count
-------
0
(1 row)
postgres=# select count(*) from pg_stat_activity where application_name = 'py_app';
count
-------
1
(1 row)
postgres=# select count(*) from pg_stat_activity where application_name = 'py_app';
count
-------
2
(1 row)
postgres=# select count(*) from pg_stat_activity where application_name = 'py_app';
count
-------
30
(1 row)
As you can see, every time you issue a request, a session is set up. To prevent this, you need to move the session around or close the session and dispose the engine before the response.
The difference in the value obtained this time can be found in the test, but if the session is bad, it may not be found and it is very dangerous. Some people use ORMs without fully understanding them because they are convenient to use, but they need to be fully understood because they can fall into these pitfalls.