!pip install sqlalchemy
!pip install sqlalchemy
Requirement already satisfied: sqlalchemy in c:\users\jhkim\anaconda3\lib\site-packages (1.3.20)
# import sqlalchemy as *
from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
import pymysql
pymysql.install_as_MySQLdb()
#http://i5on9i.blogspot.com/2020/05/no-module-named-mysqldb.html
# import sqlalchemy as *
from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
import pymysql
pymysql.install_as_MySQLdb()
#http://i5on9i.blogspot.com/2020/05/no-module-named-mysqldb.html
engine = create_engine("mysql://root:1234@localhost/test?charset=utf8")
engine
engine = create_engine("mysql://root:1234@localhost/test?charset=utf8")
engine
Engine(mysql://root:***@localhost/test?charset=utf8)
Base = declarative_base()
class User(Base):
__tablename__ = "user"
user_id = Column(Integer, primary_key=True)
name = Column(String(20))
email = Column(String(30))
age = Column(Integer)
rdate = Column(DateTime)
def __init__(self, name, email, age, rdate):
self.name = name
self.email = email
self.age = age
self.rdate = rdate
def __repr__(self):
return "<User {}, {}, {}, {}>".format(self.name, self.email, self.age, self.rdate)
Base = declarative_base()
class User(Base):
__tablename__ = "user"
user_id = Column(Integer, primary_key=True)
name = Column(String(20))
email = Column(String(30))
age = Column(Integer)
rdate = Column(DateTime)
def __init__(self, name, email, age, rdate):
self.name = name
self.email = email
self.age = age
self.rdate = rdate
def __repr__(self):
return "<User {}, {}, {}, {}>".format(self.name, self.email, self.age, self.rdate)
# engine에 연결된 데이터베이스에 테이블 생성
Base.metadata.create_all(engine)
# engine에 연결된 데이터베이스에 테이블 생성
Base.metadata.create_all(engine)
# 데이터베이스에 세션 연결
Session = sessionmaker(engine)
session = Session()
session
# 데이터베이스에 세션 연결
Session = sessionmaker(engine)
session = Session()
session
<sqlalchemy.orm.session.Session at 0x18ffdd1da00>
user = User("jin", "jin@gmail.com", 27, "2016-03-21")
user
user = User("jin", "jin@gmail.com", 27, "2016-03-21")
user
<User jin, jin@gmail.com, 27, 2016-03-21>
session.add(user)
session.add(user)
session.commit()
session.commit()
users = [
User("alice", "alice@gmail.com", 25, "2018-02-21"),
User("andy", "andy@gmail.com", 33, "2015-10-21"),
]
users = [
User("alice", "alice@gmail.com", 25, "2018-02-21"),
User("andy", "andy@gmail.com", 33, "2015-10-21"),
]
session.add_all(users)
session.add_all(users)
session.commit()
session.commit()
session.rollback()
session.rollback()
results = session.query(User).all()
list(results)
results = session.query(User).all()
list(results)
[<User jin, jin@gmail.com, 27, 2016-03-21 00:00:00>,
<User alice, alice@gmail.com, 25, 2018-02-21 00:00:00>,
<User andy, andy@gmail.com, 33, 2015-10-21 00:00:00>,
<User jin, jin@gmail.com, 27, 2016-03-21 00:00:00>,
<User alice, alice@gmail.com, 25, 2018-02-21 00:00:00>,
<User andy, andy@gmail.com, 33, 2015-10-21 00:00:00>,
<User alice, alice@gmail.com, 25, 2018-02-21 00:00:00>,
<User andy, andy@gmail.com, 33, 2015-10-21 00:00:00>]
results = session.query(User).filter(User.name == "jin")
list(results)
results = session.query(User).filter(User.name == "jin")
list(results)
[<User jin, jin@gmail.com, 27, 2016-03-21 00:00:00>,
<User jin, jin@gmail.com, 27, 2016-03-21 00:00:00>]
results = session.query(User).filter(User.email.like("%gmail%"))
list(results)
results = session.query(User).filter(User.email.like("%gmail%"))
list(results)
[<User jin, jin@gmail.com, 27, 2016-03-21 00:00:00>,
<User alice, alice@gmail.com, 25, 2018-02-21 00:00:00>,
<User andy, andy@gmail.com, 33, 2015-10-21 00:00:00>,
<User jin, jin@gmail.com, 27, 2016-03-21 00:00:00>,
<User alice, alice@gmail.com, 25, 2018-02-21 00:00:00>,
<User andy, andy@gmail.com, 33, 2015-10-21 00:00:00>,
<User alice, alice@gmail.com, 25, 2018-02-21 00:00:00>,
<User andy, andy@gmail.com, 33, 2015-10-21 00:00:00>]
results = session.query(User).filter(User.name.in_(["alice","andy"]))
list(results)
results = session.query(User).filter(User.name.in_(["alice","andy"]))
list(results)
[<User alice, alice@gmail.com, 25, 2018-02-21 00:00:00>,
<User andy, andy@gmail.com, 33, 2015-10-21 00:00:00>,
<User alice, alice@gmail.com, 25, 2018-02-21 00:00:00>,
<User andy, andy@gmail.com, 33, 2015-10-21 00:00:00>,
<User alice, alice@gmail.com, 25, 2018-02-21 00:00:00>,
<User andy, andy@gmail.com, 33, 2015-10-21 00:00:00>]
results = session.query(User).filter(
or_(User.name == "jin", User.age == 33)
)
list(results)
results = session.query(User).filter(
or_(User.name == "jin", User.age == 33)
)
list(results)
[<User jin, jin@gmail.com, 27, 2016-03-21 00:00:00>,
<User andy, andy@gmail.com, 33, 2015-10-21 00:00:00>,
<User jin, jin@gmail.com, 27, 2016-03-21 00:00:00>,
<User andy, andy@gmail.com, 33, 2015-10-21 00:00:00>,
<User andy, andy@gmail.com, 33, 2015-10-21 00:00:00>]
results = session.query(User).order_by(User.age.asc())
list(results)
results = session.query(User).order_by(User.age.asc())
list(results)
[<User alice, alice@gmail.com, 25, 2018-02-21 00:00:00>,
<User alice, alice@gmail.com, 25, 2018-02-21 00:00:00>,
<User alice, alice@gmail.com, 25, 2018-02-21 00:00:00>,
<User jin, jin@gmail.com, 27, 2016-03-21 00:00:00>,
<User jin, jin@gmail.com, 27, 2016-03-21 00:00:00>,
<User andy, andy@gmail.com, 33, 2015-10-21 00:00:00>,
<User andy, andy@gmail.com, 33, 2015-10-21 00:00:00>,
<User andy, andy@gmail.com, 33, 2015-10-21 00:00:00>]
session.query(User).count()
session.query(User).count()
8
data = session.query(User).filter(User.name == "andy")
data[0]
data = session.query(User).filter(User.name == "andy")
data[0]
<User andy, andy@gmail.com, 33, 2015-10-21 00:00:00>
data[0].age = 30
session.add(data)
---------------------------------------------------------------------------
AttributeError Traceback (most recent call last)
~\anaconda3\lib\site-packages\sqlalchemy\orm\session.py in add(self, instance, _warn)
2011 try:
-> 2012 state = attributes.instance_state(instance)
2013 except exc.NO_STATE as err:
AttributeError: 'Query' object has no attribute '_sa_instance_state'
The above exception was the direct cause of the following exception:
UnmappedInstanceError Traceback (most recent call last)
<ipython-input-118-f105d900bfce> in <module>
----> 1 session.add(data)
~\anaconda3\lib\site-packages\sqlalchemy\orm\session.py in add(self, instance, _warn)
2012 state = attributes.instance_state(instance)
2013 except exc.NO_STATE as err:
-> 2014 util.raise_(
2015 exc.UnmappedInstanceError(instance),
2016 replace_context=err,
~\anaconda3\lib\site-packages\sqlalchemy\util\compat.py in raise_(***failed resolving arguments***)
180
181 try:
--> 182 raise exception
183 finally:
184 # credit to
UnmappedInstanceError: Class 'sqlalchemy.orm.query.Query' is not mapped
session.commit()
session.query(User).filter(User.name == "jin").delete()
2
session.commit()
User.__table__.drop(engine)
import seaborn as sns
import pandas as pd
iris_df = sns.load_dataset("iris")
iris_df.tail(2)
sepal_length sepal_width petal_length petal_width species
148 6.2 3.4 5.4 2.3 virginica
149 5.9 3.0 5.1 1.8 virginica
engine
Engine(mysql://root:***@localhost/test?charset=utf8)
iris_df.to_sql(name="iris", con=engine, if_exists="replace")
iris
QUERY = """
SELECT *
FROM iris
"""
city_df = pd.read_sql(QUERY, engine)
city_df.tail()
index sepal_length sepal_width petal_length petal_width species
145 145 6.7 3.0 5.2 2.3 virginica
146 146 6.3 2.5 5.0 1.9 virginica
147 147 6.5 3.0 5.2 2.0 virginica
148 148 6.2 3.4 5.4 2.3 virginica
149 149 5.9 3.0 5.1 1.8 virginica
