How to Execute Raw SQL in SQLAlchemy | AtlassianTry now Products Featured Developers Product Managers IT professionals Business Teams Leadership Teams Featured Developers Product Managers IT professionals Business Teams Leadership Teams
[Python] SQLAlchemyã‚’é ‘å¼µã£ã¦é«˜é€ŸåŒ– - Qiita
ã¯ã˜ã‚ã« pythonã‹ã‚‰MySQLã‚’å©ã際，何を使ã£ã¦ã„ã¾ã™ã‹ï¼ŸSQLAlchemy,Django,peeweeã‚ãŸã‚Šã‚’使ã£ã¦ã„ã‚‹æ–¹ãŒå¤šã„ã®ã§ã¯ãªã„ã§ã—ょã†ã‹ï¼Žç§ã¯è‰²ã€…使ã£ã¦ã¿ã¦çµå±€SQLAlchemyã«è½ã¡ç€ã„ã¦ã„ã¾ã™ãŒï¼Œä½•åƒä¸‡ï¼Œä½•å„„オーダã®ãƒ‡ãƒ¼ã‚¿ã«ãªã‚‹ã¨å‡¦ç†ãŒé…ãéžå¸¸ã«ã‚„ã£ã‹ã„ã§ã™ï¼Ž ãªã®ã§ä»Šå›žã¯ï¼ˆå¤§ããªãƒ‡ãƒ¼ã‚¿ã«sql使ã†ãªã¨ã‹é«˜é€Ÿå‡¦ç†ã«python使ã†ãªã¨ã„ã†è©±ã¯ä¸€æ—¦ç½®ã„ã¦ãŠã„ã¦ï¼‰ï¼ŒSQLAlchemyを使ã£ãŸãƒ‡ãƒ¼ã‚¿å‡¦ç†ã‚’ã„ã‹ã«é«˜é€Ÿã«ã™ã‚‹ã‹ï¼Œä½•é …ç›®ã‹ã«åˆ†ã‘ã¦å‚™å¿˜éŒ²ã‚’残ã—ã¦ãŠãã¾ã™ï¼Ž SQLAlchemyã‚’~ã¨æ›¸ã„ã¦ã‚ã‚Šã¾ã™ãŒï¼ŒSQLAlchemyを使ã£ã¦DBã«ãƒ‡ãƒ¼ã‚¿ã‚’INSERT,SELECTã™ã‚‹éš›ã®python高速化TIPSã”ã£ãŸç…®ã§ã™ï¼Žæœ¬å½“ã¯è¨˜äº‹ã‚’分ã‘ãŸã»ã†ãŒè‰¯ã„ã®ã§ã™ãŒï¼Œè‡ªåˆ†ã§è¦‹è¿”ã™éš›ã«ã¾ã¨ã¾ã£ã¦ã„ã‚‹ã»ã†ãŒå¬‰ã—ã„ã®ã§ã“ã†ã—ã¾ã—ãŸï¼Ž ãªãŠï¼Œä»Šå›žä½¿ç”¨ã™ã‚‹ã‚³ãƒ¼ãƒ‰ã¯å…¨ã¦githubã«
![[Python] SQLAlchemyã‚’é ‘å¼µã£ã¦é«˜é€ŸåŒ– - Qiita](https://cdn-ak-scissors.b.st-hatena.com/image/square/d92ddd43388ef3a6ada9cbc8aa35df50be43c394/height=288;version=1;width=512/https%3A%2F%2Fqiita-user-contents.imgix.net%2Fhttps%253A%252F%252Fqiita-user-contents.imgix.net%252Fhttps%25253A%25252F%25252Fcdn.qiita.com%25252Fassets%25252Fpublic%25252Farticle-ogp-background-afbab5eb44e0b055cce1258705637a91.png%253Fixlib%253Drb-4.0.0%2526w%253D1200%2526blend64%253DaHR0cHM6Ly9xaWl0YS11c2VyLXByb2ZpbGUtaW1hZ2VzLmltZ2l4Lm5ldC9odHRwcyUzQSUyRiUyRnFpaXRhLWltYWdlLXN0b3JlLnMzLmFtYXpvbmF3cy5jb20lMkYwJTJGNTk4NjQlMkZwcm9maWxlLWltYWdlcyUyRjE0NzM2OTUwNTg_aXhsaWI9cmItNC4wLjAmYXI9MSUzQTEmZml0PWNyb3AmbWFzaz1lbGxpcHNlJmZtPXBuZzMyJnM9YTE3N2MyNGEwNmJhYmVhMWE0ZDUwMmNmNWM5MGVjZTI%2526blend-x%253D120%2526blend-y%253D467%2526blend-w%253D82%2526blend-h%253D82%2526blend-mode%253Dnormal%2526s%253Dc8dd32b31fae03c70963421bf3671e4f%3Fixlib%3Drb-4.0.0%26w%3D1200%26fm%3Djpg%26mark64%3DaHR0cHM6Ly9xaWl0YS11c2VyLWNvbnRlbnRzLmltZ2l4Lm5ldC9-dGV4dD9peGxpYj1yYi00LjAuMCZ3PTk2MCZoPTMyNCZ0eHQ9JTVCUHl0aG9uJTVEJTIwU1FMQWxjaGVteSVFMyU4MiU5MiVFOSVBMCU5MSVFNSVCQyVCNSVFMyU4MSVBMyVFMyU4MSVBNiVFOSVBQiU5OCVFOSU4MCU5RiVFNSU4QyU5NiZ0eHQtYWxpZ249bGVmdCUyQ3RvcCZ0eHQtY29sb3I9JTIzMUUyMTIxJnR4dC1mb250PUhpcmFnaW5vJTIwU2FucyUyMFc2JnR4dC1zaXplPTU2JnR4dC1wYWQ9MCZzPTgwNWE0NjFkOTVjOWIxNjFiYjUzYTgwOTg1ODBkZTg0%26mark-x%3D120%26mark-y%3D112%26blend64%3DaHR0cHM6Ly9xaWl0YS11c2VyLWNvbnRlbnRzLmltZ2l4Lm5ldC9-dGV4dD9peGxpYj1yYi00LjAuMCZ3PTgzOCZoPTU4JnR4dD0lNDB5dWtpQiZ0eHQtY29sb3I9JTIzMUUyMTIxJnR4dC1mb250PUhpcmFnaW5vJTIwU2FucyUyMFc2JnR4dC1zaXplPTM2JnR4dC1wYWQ9MCZzPTQwNDMzNzY3OWM3ZGFlZDg1NWQ2ZmFkZmQ5NTQ2NTYy%26blend-x%3D242%26blend-y%3D454%26blend-w%3D838%26blend-h%3D46%26blend-fit%3Dcrop%26blend-crop%3Dleft%252Cbottom%26blend-mode%3Dnormal%26txt64%3DaW4g5qCq5byP5Lya56S-44OG44Oz44Kv44O8%26txt-x%3D242%26txt-y%3D539%26txt-width%3D838%26txt-clip%3Dend%252Cellipsis%26txt-color%3D%25231E2121%26txt-font%3DHiragino%2520Sans%2520W6%26txt-size%3D28%26s%3D0203fa8dc2abc78fa7041c515182a2e7)
SQLAlchemyåŒæœŸ/éžåŒæœŸã®æ¯”較 - Qiita
Pythonã®ãƒ•ãƒ¬ãƒ¼ãƒ ワークã§ã‚ã‚‹FastAPIを触る機会ãŒã‚ã£ã¦ã€ãã®ä¸ã§éžåŒæœŸãƒ—ãƒã‚°ãƒ©ãƒŸãƒ³ã‚°ã®å˜åœ¨ã‚’知りã¾ã—ãŸã€‚ éžåŒæœŸãƒ—ãƒã‚°ãƒ©ãƒŸãƒ³ã‚°ã«ã¤ã„ã¦è‰²ã€…調ã¹ã¦ã„ãã¨ã€DB接続もéžåŒæœŸå‡¦ç†ã«å¯¾å¿œã—ã¦ãã¦ã„ã‚‹ã“ã¨ãŒåˆ†ã‹ã‚Šã€Pythonã®SQLライブラリã§æœ‰åãªSQLAlchemyã‚‚ãƒãƒ¼ã‚¸ãƒ§ãƒ³1.4ã‹ã‚‰éžåŒæœŸå‡¦ç†ã«å¯¾å¿œã—ã¦ã„るよã†ã§ã™ã€‚ 時代ã¯éžåŒæœŸãƒ—ãƒã‚°ãƒ©ãƒŸãƒ³ã‚°ã¿ãŸã„ã§ã™ã。 ãŸã SQLAlchemyã®éžåŒæœŸå‡¦ç†ã®è¨˜äº‹ãŒã¾ã å°‘ãªãã€åŒæœŸå‡¦ç†ã‹ã‚‰éžåŒæœŸå‡¦ç†ã«å¤‰ãˆã‚‹ã¨ãã«ã€è‹¦åŠ´ã—ãã†ãªã®ã§ã€ä»Šå›žã¯SQLAlchemyåŒæœŸå‡¦ç†ã¨éžåŒæœŸå‡¦ç†ã§è¨å®šã‚„クエリãŒã©ã†é•ã†ã®æ¯”較ã—よã†ã¨æ€ã„ã¾ã™ã€‚ SQLAlchemyã«ã¯coreã¨ormãŒã‚ã‚Šã¾ã™ãŒã€ã“ã“ã§ã¯ormã§æ›¸ã„ã¦ã„ãã¾ã™ã€‚ APIã«ã¯FastAPIã€DBã¯PostgreSQLを使ã£ã¦ã„ãã¾ã™ã€‚ 動作確èªç”¨ã‚³ãƒ¼ãƒ‰ã¯ä¸‹è¨˜ãƒªãƒã‚¸ãƒˆãƒªã«ç½®ã„ã¦ã„ã¾ã™ã€‚ åŒæœŸå‡¦ç†
Pythonã®ORMã€SQLAlchemyã‚’ã¾ã¨ã‚る（SELECT編）
from sqlalchemy import create_engine, MetaData from sqlalchemy.orm import sessionmaker, declarative_base from databases import Database DATABASE_URL = 'postgresql://postgres:password@db:5432/db_name' engine = create_engine(DATABASE_URL) Session = sessionmaker(autocommit=False, autoflush=False, bind=engine) session = Session() Base = declarative_base() from sqlalchemy import ( Column, DateTime, Int
FastAPI+SQLAlchemyã§éžåŒæœŸWebAPI
å°‘ã—å‰ã«SQLAlchemy 1.4ãŒãƒªãƒªãƒ¼ã‚¹ã•ã‚Œã¾ã—ãŸã€‚ ã“ã®SQLAlchemy 1.4ã«ã¯å¤§ããªç‰¹å¾´ã¨ã—ã¦æ¬¡ã®2点ãŒã‚ã‚Šã¾ã™ã€‚ SQLAlchemy ORMã§ã“ã‚Œã¾ã§ã®è¨˜æ³•(1.x Style)ã«åŠ ãˆã¦ã€2.0 Styleã¨å‘¼ã°ã‚Œã‚‹æ–°ã—ã„記法ã«å¯¾å¿œ Coreã¨ORMã®ä¸¡æ–¹ã§ã‚¤ãƒ™ãƒ³ãƒˆãƒ«ãƒ¼ãƒ—を使ã£ãŸéžåŒæœŸå‡¦ç†ã«å¯¾å¿œ 今後ã®ãƒãƒ¼ãƒ‰ãƒžãƒƒãƒ—ã§ã¯ã€SQLAlchemy 2.0ã§ã¯2.0 Styleã®ã¿ãŒã‚µãƒãƒ¼ãƒˆã•ã‚Œã¾ã™ã€‚ ã¾ãŸã€ã‚¤ãƒ™ãƒ³ãƒˆãƒ«ãƒ¼ãƒ—を使ã£ãŸéžåŒæœŸå‡¦ç†ã‚‚ã“ã®æ•°å¹´ã§ä¸€æ°—ã«åºƒã¾ã£ã¦ãã¾ã—ãŸã€‚ SQLAlchemyãŒå¯¾å¿œã—ãŸã“ã¨ã‚‚ã“ã®æµã‚Œã‚’ã•ã‚‰ã«åŠ 速ã•ã›ã‚‹ã¨æ€ã„ã¾ã™ã€‚ ãã“ã§æœ€è¿‘よã利用ã—ã¦ã„ã‚‹FastAPIã¨çµ„ã¿åˆã‚ã›ã¦ã€SQLAlchemy 2.0 Styleを使ã£ãŸéžåŒæœŸWeb APIã®ã‚µãƒ³ãƒ—ルプãƒã‚¸ã‚§ã‚¯ãƒˆã‚’用æ„ã—ã¾ã—ãŸã€‚ コードã¯rhoboro/async-fastapi-sqlalc
SQLAlchemy filter()ã¨filter_by()ã®é•ã„ - Qiita
Pythonã®O/R Mapperã®ï¼‘ã¤ã§ã‚ã‚‹SQLAlchemyã§DB検索ã™ã‚‹éš›ã«filter()ã¨filter_by()ã®ï¼’ã¤ã®é–¢æ•°ãŒã‚ã‚‹ã®ã§é•ã„を説明ã—ã¾ã™ã€‚ å…¬å¼ãƒ‰ã‚ュメントを見れã°æ˜Žç™½ã§ã—ãŸã€‚ filter_by(), filter() çµè«–ã‹ã‚‰è¨€ã†ã¨ã€fileter_by()ã®æ–¹ãŒã‚³ãƒ¼ãƒ‰ãŒçŸããªã‚‹åˆ©ç‚¹ãŒã‚るよã†ã§ã™ã€‚ filter():{モデル}.{変数å}ã§ãƒ†ãƒ¼ãƒ–ルã®ã‚«ãƒ©ãƒ ã®å€¤ã‚’検索æ¡ä»¶ã«è¿½åŠ å¯èƒ½ filter_by(): モデルãªã—ã§ã€{変数å}ã ã‘ã§ã‚«ãƒ©ãƒ ã®å€¤ã‚’検索æ¡ä»¶ã«è¿½åŠ å¯èƒ½ コードã§è¦‹ã‚‹ã¨ã‚ã‹ã‚Šã‚„ã™ã„ã®ã§æ˜Žè¨˜ã™ã‚‹ã¨ # filter()を使ã†å ´åˆã®è¨˜è¿°ä¾‹ session.query(MyClass).\ filter(MyClass.name == 'some name', MyClass.id > 5) # filter_by()を使ã†å ´åˆã®è¨˜è¿°ä¾‹ session
SQLAlchemyã§ã®ãƒ†ãƒ¼ãƒ–ル定義 - Qiita
ã¯ã˜ã‚ã« ã“ã“ã§ã¯ï¼ŒSQLAlchemyを用ã„ã¦ãƒ†ãƒ¼ãƒ–ル定義を行ã†ã¨ãã®æ–¹æ³•ã«ã¤ã„ã¦ã¾ã¨ã‚る． 以下，dialect(DBã®ç¨®é¡ž)ã¯mysqlã¨ã™ã‚‹ï¼Ž Charset指定 __table_args__ã®mysql_charsetã§æŒ‡å®šã™ã‚‹ã“ã¨ãŒã§ãる． 例ãˆã°ï¼Œcharsetã‚’utf8mb4ã«æŒ‡å®šã—ãŸã‘ã‚Œã°ï¼Œ
ã€Python】ãŠã€æ‰‹è»½ï¼Flaskフレームワークã®ORMæ‹¡å¼µã§DBマイグレーションを実装ã—ã¦ã¿ãŸ - Qiita
ã¯ã˜ã‚㫠先日ã€ç¤¾å†…ã®ã‚ªãƒ³ãƒ©ã‚¤ãƒ³é–‹ç™ºåˆå®¿ã¨ã„ã†ã‚¤ãƒ™ãƒ³ãƒˆå‘ã‘ã«ã€ã‚¢ãƒ—リケーションを作る一環ã¨ã—ã¦ã€ （コãƒãƒŠç¦ã‚‚ã‚ã£ã¦ã‚ªãƒ³ãƒ©ã‚¤ãƒ³...） REST APIã®ä½œæˆã¯å®Œäº†ã—ãŸã®ã§ã€ 今度ã¯Flaskã«ãŠã‘ã‚‹DBマイグレーションã®å®Ÿè£…ã¨Seeding（テストデータã®INSERT処ç†ï¼‰ã‚’ 実装・検証ã—ãŸã®ã§ã€å…±æœ‰ã—ãŸã„ã¨æ€ã„ã¾ã™ã€‚ DBマイグレーションã¨ã¯ ソフトウェア工å¦ã«ãŠã„ã¦ã€ã‚¹ã‚ーママイグレーション（データベースマイグレーションã€ãƒ‡ãƒ¼ã‚¿ãƒ™ãƒ¼ã‚¹ãƒã‚§ãƒ³ã‚¸ãƒžãƒã‚¸ãƒ¡ãƒ³ãƒˆï¼‰ã¨ã¯ã€ãƒªãƒ¬ãƒ¼ã‚·ãƒ§ãƒŠãƒ«ãƒ‡ãƒ¼ã‚¿ãƒ™ãƒ¼ã‚¹ã®ã‚¹ã‚ーマã«å¯¾ã™ã‚‹å¢—分的ã€å¯é€†çš„ãªå¤‰æ›´ã¨ãƒãƒ¼ã‚¸ãƒ§ãƒ³ç®¡ç†ã®ç®¡ç†ã‚’指ã™ã€‚スã‚ーママイグレーションã¯ã€ãƒ‡ãƒ¼ã‚¿ãƒ™ãƒ¼ã‚¹ã®ã‚¹ã‚ーマを更新ã—ãŸã‚Šã€æ–°ã—ã„ãƒãƒ¼ã‚¸ãƒ§ãƒ³ã‚„å¤ã„ãƒãƒ¼ã‚¸ãƒ§ãƒ³ã«æˆ»ã—ãŸã‚Šã™ã‚‹å¿…è¦ãŒã‚ã‚‹å ´åˆã«ã€ãƒ‡ãƒ¼ã‚¿ãƒ™ãƒ¼ã‚¹ã«å¯¾ã—ã¦å®Ÿè¡Œã•ã‚Œã¾ã™ã€‚ å‚照元：wikipedia ※DeepL翻訳 ãµã‚€ãµã‚€ã€ãƒ‡ãƒ¼ã‚¿ãƒ™ãƒ¼ã‚¹ã®
Pythonã®migrationツールã®alembicã‚’å‹•ã‹ã—ã¦ã¿ãŸ - Qiita
root@9a7582105665:/usr/src# pip install alembic psycopg2-binary Collecting alembic Downloading alembic-1.4.2.tar.gz (1.1 MB) |████████████████████████████████| 1.1 MB 7.8 MB/s Installing build dependencies ... done Getting requirements to build wheel ... done Preparing wheel metadata ... done Collecting psycopg2-binary Downloading psycopg2_binary-2.8.5-cp38-cp38-manylinux1_x86_64.whl (3.0 MB) |███
1
リリースã€éšœå®³æƒ…å ±ãªã©ã®ã‚µãƒ¼ãƒ“スã®ãŠçŸ¥ã‚‰ã›
最新ã®äººæ°—エントリーã®é…ä¿¡
j次ã®ãƒ–ックマーク
kå‰ã®ãƒ–ックマーク
lã‚ã¨ã§èªã‚€
eコメント一覧を開ã
oページを開ã
PlaySQLAlchemy: SQLAlchemy入門
{{#tags}}- {{label}}
{{/tags}}