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)})

Recommended Posts

Try using SQLAlchemy + MySQL (Part 1)
Try using SQLAlchemy + MySQL (Part 2)
Try using Pillow on iPython (Part 1)
Try using Pillow on iPython (Part 2)
Try using Pillow on iPython (Part 3)
Connect to MySQL using Flask SQLAlchemy
Try using Tkinter
Try using docker-py
Try using cookiecutter
Try using PDFMiner
Try using geopandas
Try using Selenium
Try using scipy
Try using pandas.DataFrame
Try using django-swiftbrowser
Try using matplotlib
Try using tf.metrics
Try using PyODE
Try using the Python web framework Tornado Part 1
Try using the Python web framework Tornado Part 2
Try using virtualenv (virtualenvwrapper)
[Azure] Try using Azure Functions
Try using virtualenv now
Try using W & B
Try using Django templates.html
[Kaggle] Try using LGBM
Try using Python's feedparser.
Try using Python's Tkinter
Try using Tweepy [Python2.7]
Try using Pytorch's collate_fn
Try using PythonTex with Texpad.
[Python] Try using Tkinter's canvas
Try using scikit-learn (1) --K-means clustering
Try function optimization using Hyperopt
Try using matplotlib with PyCharm
Try using Azure Logic Apps
Try using Kubernetes Client -Python-
[Kaggle] Try using xg boost
Try using the Twitter API
Try using OpenCV on Windows
Try normal Linux programming Part 2
Try using Jupyter Notebook dynamically
Try normal Linux programming Part 3
Try using AWS SageMaker Studio
Try translating English PDF Part 1
Try tweeting automatically using Selenium.
Try using the Twitter API
Try normal Linux programming Part 4
Try using Django's template feature
Try using the PeeringDB 2.0 API
Try using Pelican's draft feature
Try normal Linux programming Part 6
Try using pytest-Overview and Samples-
Try using folium with anaconda
Generate SQLAlchemy table definition from existing MySQL server using sqlacodegen
Try to analyze online family mahjong using Python (PART 1: Take DATA)
Try using Janus gateway's Admin API
[Statistics] [R] Try using quantile regression.
Try using Spyder included in Anaconda
Try using design patterns (exporter edition)
Try deep learning with TensorFlow Part 2