<div id="readability-page-1" class="page"><div>
												
<h2>테이블 생성</h2>
<pre><code>CREATE TABLE `user` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `username` VARCHAR(256) NOT NULL DEFAULT '0' COLLATE 'utf8mb4_general_ci',
    `email` VARCHAR(256) NOT NULL DEFAULT '0' COLLATE 'utf8mb4_general_ci',
    PRIMARY KEY (`id`) USING BTREE
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;</code></pre>
<p>VSCode 에서 session 에 대해 자동완성을 하지 않는다.<br>
해결책을 찾아보아야겠다.</p>
<pre><code>from __future__ import annotations

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, declarative_base
from sqlalchemy import Column, Integer, String, DateTime, Boolean

engine = create_engine('mysql+mysqldb://root:12345678@localhost:3306/testdb')
Session = sessionmaker(engine)
session = Session()

Base = declarative_base()

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    username = Column(String)
    email = Column(String)
    # time = Column(DateTime, default=datetime.datetime.utcnow, primary_key=True)
    def __init__(self, user: User):
        # self.id = user.id
        self.username = user.username
        self.email = user.email
    def __init__(self, username: str, email: str):
        # self.id = user.id
        self.username = username
        self.email = email
    def update(self, user: User):
        self.username = user.username
        self.email = user.email</code></pre>
<h2>테스트 환경 구축</h2>
<pre><code>pip install sqlalchemy
pip install mysqlclient</code></pre>
<h2>insert</h2>
<pre><code># insert
user = User(username="Lee", email="aaa@aaa.com")
session.add(user)
session.commit()</code></pre>
<h2>select</h2>
<pre><code># select one
user: User = session.query(User).filter(User.username == "Lee").first()
# user: User = session.query(User).filter(User.username == "Lee 2", User.id == 2).first()
print(user.id)</code></pre>
<pre><code># select all
users: List[User] = session.query(User).filter(User.username == "Lee").all()
# users: List[User] = session.query(User).filter(User.username == "Lee 2").order_by(User.id.desc()).all()
for user in users:
    print(user.id)</code></pre>
<h2>update</h2>
<pre><code># update one
user2 = User(username="Lee 2", email="aaa2@aaa.com")
user: User = session.query(User).filter(User.username == "Lee").first()
user.update(user2)

# update list
user2 = User(username="Lee 2", email="aaa2@aaa.com")
users: List[User] = session.query(User).filter(User.username == "Lee").all()
for user in users:
    user.update(user2)

# 커밋을 할 때 자동으로 update 문이 실행된다.
session.commit()</code></pre>
<h2>delete</h2>
<pre><code># delete
user: User = session.query(User).filter(User.id == 1).first()
if user is not None:
    session.delete(user)
session.commit()</code></pre>
<p><span></span> <span>Post Views:</span> <span>5</span>
			</p>											</div></div>

상구리의 기술 블로그

Python – SQLAlchemy
테이블 생성
CREATE TABLE `user` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `username` VARCHAR(256) NOT NULL DEFAULT '0' COLLATE 'utf8mb4_general_ci',
    `email` VARCHAR(256) NOT NULL DEFAULT '0' COLLATE 'utf8mb4_general_ci',
    PRIMARY KEY (`id`) USING BTREE
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;
VSCode 에서 session 에 대해 자동완성을 하지 않는다.
from __future__ import annotations
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, declarative_base
from sqlalchemy import Column, Integer, String, DateTime, Boolean
engine = create_engine('mysql+mysqldb://root:12345678@localhost:3306/testdb')
Session = sessionmaker(engine)
session = Session()
Base = declarative_base()
class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    username = Column(String)
    email = Column(String)
    # time = Column(DateTime, default=datetime.datetime.utcnow, primary_key=True)
    def __init__(self, user: User):
        # self.id = user.id
        self.username = user.username
        self.email = user.email
    def __init__(self, username: str, email: str):
        # self.id = user.id
        self.username = username
        self.email = email
    def update(self, user: User):
        self.username = user.username
        self.email = user.email
테스트 환경 구축
pip install sqlalchemy
pip install mysqlclient
insert
# insert
user = User(username="Lee", email="aaa@aaa.com")
session.add(user)
session.commit()
select
# select one
user: User = session.query(User).filter(User.username == "Lee").first()
# user: User = session.query(User).filter(User.username == "Lee 2", User.id == 2).first()
print(user.id)
# select all
users: List[User] = session.query(User).filter(User.username == "Lee").all()
# users: List[User] = session.query(User).filter(User.username == "Lee 2").order_by(User.id.desc()).all()
for user in users:
    print(user.id)
update
# update one
user2 = User(username="Lee 2", email="aaa2@aaa.com")
user: User = session.query(User).filter(User.username == "Lee").first()
user.update(user2)
# update list
user2 = User(username="Lee 2", email="aaa2@aaa.com")
users: List[User] = session.query(User).filter(User.username == "Lee").all()
for user in users:
    user.update(user2)
# 커밋을 할 때 자동으로 update 문이 실행된다.
session.commit()
delete
# delete
user: User = session.query(User).filter(User.id == 1).first()
if user is not None:
    session.delete(user)
session.commit()

Python – SQLAlchemy

댓글