[PYTHON] Try using SQLAlchemy + MySQL (Part 2)

This is a continuation of Last time.

I'm following the tutorial, but I think it went smoothly except for the first stumbling block.

Still, make a note of what I was a little worried about.

Specify the number of characters in the column

For MySQL, you must always specify the number of characters in the column. It cannot be omitted like "sqlalchemy.Column ()".

`sqlalchemy_test2.py`


import sqlalchemy
import sqlalchemy.ext.declarative

Base = sqlalchemy.ext.declarative.declarative_base()

class Student(Base):
    __tablename__ = 'students'
    id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True)
    name = sqlalchemy.Column(sqlalchemy.String(20))
    kana = sqlalchemy.Column(sqlalchemy.String(40))

url = 'mysql+pymysql://root:@localhost/test_db?charset=utf8'

engine = sqlalchemy.create_engine(url, echo=True)

Base.metadata.create_all(engine)

[Error message] sqlalchemy.exc.CompileError: (in table 'students', column 'name'): Can't generate DDL for NullType(); did you forget to specify a type on this Column?

SQL statement to pass as an argument to the filter function

The SQL statement passed as an argument to the filter function explicitly uses the text function. If you are not using the text function, you will get a warning.

`sqlalchemy_test3.py`


import sqlalchemy
import sqlalchemy.orm
import sqlalchemy.ext.declarative
from sqlalchemy import text

Base = sqlalchemy.ext.declarative.declarative_base()

class Student(Base):
    __tablename__ = 'students'
    id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True)
    name = sqlalchemy.Column(sqlalchemy.String(20))
    kana = sqlalchemy.Column(sqlalchemy.String(40))
    
url = 'mysql+pymysql://root:@localhost/test_db?charset=utf8'

engine = sqlalchemy.create_engine(url, echo=False)

Session = sqlalchemy.orm.sessionmaker(bind=engine)
session = Session()

students = session.query(Student).filter("id=20") # SAWarning: Textual SQL expression
students = session.query(Student).filter(text("id=20"))

[Warning message] SAWarning: Textual SQL expression 'id=20' should be explicitly declared as text('id=20') (this warning may be suppressed after 10 occurrences) {"expr": util.ellipses_string(element)})