간결하게 정리된 문법으로 정리
# SQL Alchemy 로 DataBase 연결하기
import sqlalchemy as db
import pandas as pd
engine = db.create_engine(
"mysql+pymysql://root:" + "erdosql" +\
"@localhost:3306/?charset=utf8", encoding='utf-8')
사용자 임의의 Query 조작
engine.execute("use food_data")
cursor = engine.connect()
query = "show tables"
print(cursor.execute(query).fetchall())
cursor.close()
매핑규칙 (mapping rule) : 개념적 데이터베이스 ERD를 테이블로 정의하는 Query (관계 스키마 작성)
from sqlalchemy import create_engine
from sqlalchemy import MetaData, Table, Column, Integer, String
engine = db.create_engine(
"mysql+pymysql://root:" + "erdosql" +\
"@localhost:3306/?charset=utf8", encoding='utf-8')
metadata = MetaData()
car_price = Table('car_user', metadata,
Column('id', Integer, primary_key=True),
Column('name', String(50)),
Column('price', Integer)
)
class CarUser():
def __init__(self, name, price):
self.name = name
self.price = price
from sqlalchemy.orm import mapper
mapper(CarUser, car_price)
metadata.create_all(engine)
connect = engine.connect()
connect.execute("use food_data;")
connect.execute("DROP TABLE IF EXISTS car_data;")
<sqlalchemy.engine.result.ResultProxy at 0x7f1a94afe208>
# car_data 테이블 생성하기
import sqlalchemy as db
metadata = db.MetaData()
emp = db.Table('car_data', metadata,
db.Column('Id', db.Integer()),
db.Column('name', db.String(255), nullable=False),
db.Column('salary', db.Float(), default=100.0),
db.Column('active', db.Boolean(), default=True)
)
metadata.create_all(connect)
connect.execute("show tables;").fetchall()
[('car_data',), ('car_users',), ('cars',), ('estate',), ('estatedb',)]
connect
--------------------------------------------------------------------------- AttributeError Traceback (most recent call last) <ipython-input-8-882b6244583c> in <module> ----> 1 connect.commit() AttributeError: 'Connection' object has no attribute 'commit'
metadata = db.MetaData()
con = engine.connect()
con.execute("use food_data;")
# 'car_data' 테이블 Instance 만들기
# con.execute("DROP TABLE IF EXISTS car_data;")
emp = db.Table('car_data', metadata,
autoload=True, autoload_with=con)
데이터 베이스 연결하기
# DataBase 와 연결하는 Python Engine
from sqlalchemy import create_engine
engine = create_engine(
"mysql+pymysql://" + "root" + ":" + "erdosql" +\
"@localhost:3306/?charset=utf8",
encoding='utf-8', echo=True)
# engine.execute("show databases;").fetchall()
engine.execute("use food_data")
engine.execute("show tables;").fetchall()
2019-12-06 20:38:18,918 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'sql_mode'
2019-12-06 20:38:18,918 INFO sqlalchemy.engine.base.Engine {}
2019-12-06 20:38:18,919 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'lower_case_table_names'
2019-12-06 20:38:18,920 INFO sqlalchemy.engine.base.Engine {}
2019-12-06 20:38:18,922 INFO sqlalchemy.engine.base.Engine SELECT DATABASE()
2019-12-06 20:38:18,922 INFO sqlalchemy.engine.base.Engine {}
2019-12-06 20:38:18,923 INFO sqlalchemy.engine.base.Engine show collation where `Charset` = 'utf8mb4' and `Collation` = 'utf8mb4_bin'
2019-12-06 20:38:18,923 INFO sqlalchemy.engine.base.Engine {}
2019-12-06 20:38:18,924 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS CHAR(60)) AS anon_1
2019-12-06 20:38:18,925 INFO sqlalchemy.engine.base.Engine {}
2019-12-06 20:38:18,926 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS CHAR(60)) AS anon_1
2019-12-06 20:38:18,926 INFO sqlalchemy.engine.base.Engine {}
2019-12-06 20:38:18,927 INFO sqlalchemy.engine.base.Engine SELECT CAST('test collated returns' AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_bin AS anon_1
2019-12-06 20:38:18,927 INFO sqlalchemy.engine.base.Engine {}
2019-12-06 20:38:18,928 INFO sqlalchemy.engine.base.Engine use food_data
2019-12-06 20:38:18,928 INFO sqlalchemy.engine.base.Engine {}
2019-12-06 20:38:18,929 INFO sqlalchemy.engine.base.Engine show tables;
2019-12-06 20:38:18,929 INFO sqlalchemy.engine.base.Engine {}
[('car_data',), ('cars',), ('estate',), ('estatedb',)]
데이터 베이스 연결하기
# Connection 을 활용한 쿼리
conn = engine.connect()
trans = conn.begin()
print(conn.execute('show tables;').fetchall())
trans.commit()
# conn.close()
# Connection 종료 후에도 engine 은 유지 중
engine.execute("show databases").fetchall()
2019-12-06 20:35:38,758 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2019-12-06 20:35:38,759 INFO sqlalchemy.engine.base.Engine show tables;
2019-12-06 20:35:38,761 INFO sqlalchemy.engine.base.Engine {}
[('car_data',), ('cars',), ('estate',), ('estatedb',)]
2019-12-06 20:35:38,762 INFO sqlalchemy.engine.base.Engine COMMIT
2019-12-06 20:35:38,765 INFO sqlalchemy.engine.base.Engine show databases
2019-12-06 20:35:38,765 INFO sqlalchemy.engine.base.Engine {}
[('food_data',), ('information_schema',), ('mysql',), ('performance_schema',)]
# DataBase 와 연결하는 Python Engine
from sqlalchemy import create_engine
engine = create_engine(
"mysql+pymysql://" + "root" + ":" + "erdosql" +\
"@localhost:3306/food_data?charset=utf8",
encoding='utf-8', echo=True)
from sqlalchemy.orm import scoped_session, sessionmaker
session = scoped_session(sessionmaker(autocommit=False, autoflush=False, bind=engine))
# session.execute("use food_data")
session.execute("show tables").fetchall()
2019-12-06 20:59:39,487 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'sql_mode'
2019-12-06 20:59:39,488 INFO sqlalchemy.engine.base.Engine {}
2019-12-06 20:59:39,489 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'lower_case_table_names'
2019-12-06 20:59:39,489 INFO sqlalchemy.engine.base.Engine {}
2019-12-06 20:59:39,490 INFO sqlalchemy.engine.base.Engine SELECT DATABASE()
2019-12-06 20:59:39,491 INFO sqlalchemy.engine.base.Engine {}
2019-12-06 20:59:39,492 INFO sqlalchemy.engine.base.Engine show collation where `Charset` = 'utf8mb4' and `Collation` = 'utf8mb4_bin'
2019-12-06 20:59:39,492 INFO sqlalchemy.engine.base.Engine {}
2019-12-06 20:59:39,493 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS CHAR(60)) AS anon_1
2019-12-06 20:59:39,493 INFO sqlalchemy.engine.base.Engine {}
2019-12-06 20:59:39,494 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS CHAR(60)) AS anon_1
2019-12-06 20:59:39,495 INFO sqlalchemy.engine.base.Engine {}
2019-12-06 20:59:39,496 INFO sqlalchemy.engine.base.Engine SELECT CAST('test collated returns' AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_bin AS anon_1
2019-12-06 20:59:39,496 INFO sqlalchemy.engine.base.Engine {}
2019-12-06 20:59:39,497 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2019-12-06 20:59:39,498 INFO sqlalchemy.engine.base.Engine show tables
2019-12-06 20:59:39,498 INFO sqlalchemy.engine.base.Engine {}
[('car_data',), ('car_users',), ('cars',), ('estate',), ('estatedb',)]
from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class User(Base):
__tablename__ = 'car_users'
id = Column(Integer, primary_key=True)
name = Column(String(length=5))
user = Column(String(length=10))
def __repr__(self):
return f'User {self.name}'
engine.execute("use food_data")
Base.metadata.create_all(engine)
2019-12-06 20:55:38,291 INFO sqlalchemy.engine.base.Engine use food_data
2019-12-06 20:55:38,291 INFO sqlalchemy.engine.base.Engine {}
2019-12-06 20:55:38,292 INFO sqlalchemy.engine.base.Engine DESCRIBE `car_users`
2019-12-06 20:55:38,293 INFO sqlalchemy.engine.base.Engine {}
user = User(name='tesla', user='john')
session.add(user)
print(user.id) # None
None
session.flush()
2019-12-06 20:57:18,575 INFO sqlalchemy.engine.base.Engine INSERT INTO car_users (name, user) VALUES (%(name)s, %(user)s)
2019-12-06 20:57:18,576 INFO sqlalchemy.engine.base.Engine {'name': 'tesla', 'user': 'john'}
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
from sqlalchemy import Column, Integer, String
class User(Base):
__tablename__ = 'car_users'
id = Column(Integer, primary_key=True)
name = Column(String)
price = Column(Integer)
def __repr__(self):
return f'car_users {self.name}'
conn = engine.execute("use food_data")
engine.execute("show tables").fetchall()
2019-12-06 20:38:22,711 INFO sqlalchemy.engine.base.Engine use food_data
2019-12-06 20:38:22,712 INFO sqlalchemy.engine.base.Engine {}
2019-12-06 20:38:22,713 INFO sqlalchemy.engine.base.Engine show tables
2019-12-06 20:38:22,714 INFO sqlalchemy.engine.base.Engine {}
[('car_data',), ('cars',), ('estate',), ('estatedb',)]
conn = engine.execute("use food_data")
2019-12-06 20:39:06,720 INFO sqlalchemy.engine.base.Engine use food_data
2019-12-06 20:39:06,721 INFO sqlalchemy.engine.base.Engine {}
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=conn)
session = Session()
user = User(name='tesla model 3', price=7600)
session.add(user)
session.commit()
--------------------------------------------------------------------------- AttributeError Traceback (most recent call last) <ipython-input-33-1028764d38c2> in <module> 4 user = User(name='tesla model 3', price=7600) 5 session.add(user) ----> 6 session.commit() ~/Python/Python/lib/python3.6/site-packages/sqlalchemy/orm/session.py in commit(self) 1025 raise sa_exc.InvalidRequestError("No transaction is begun.") 1026 -> 1027 self.transaction.commit() 1028 1029 def prepare(self): ~/Python/Python/lib/python3.6/site-packages/sqlalchemy/orm/session.py in commit(self) 492 self._assert_active(prepared_ok=True) 493 if self._state is not PREPARED: --> 494 self._prepare_impl() 495 496 if self._parent is None or self.nested: ~/Python/Python/lib/python3.6/site-packages/sqlalchemy/orm/session.py in _prepare_impl(self) 471 if self.session._is_clean(): 472 break --> 473 self.session.flush() 474 else: 475 raise exc.FlushError( ~/Python/Python/lib/python3.6/site-packages/sqlalchemy/orm/session.py in flush(self, objects) 2468 try: 2469 self._flushing = True -> 2470 self._flush(objects) 2471 finally: 2472 self._flushing = False ~/Python/Python/lib/python3.6/site-packages/sqlalchemy/orm/session.py in _flush(self, objects) 2606 except: 2607 with util.safe_reraise(): -> 2608 transaction.rollback(_capture_exception=True) 2609 2610 def bulk_save_objects( ~/Python/Python/lib/python3.6/site-packages/sqlalchemy/util/langhelpers.py in __exit__(self, type_, value, traceback) 66 self._exc_info = None # remove potential circular references 67 if not self.warn_only: ---> 68 compat.reraise(exc_type, exc_value, exc_tb) 69 else: 70 if not compat.py3k and self._exc_info and self._exc_info[1]: ~/Python/Python/lib/python3.6/site-packages/sqlalchemy/util/compat.py in reraise(tp, value, tb, cause) 151 if value.__traceback__ is not tb: 152 raise value.with_traceback(tb) --> 153 raise value 154 155 def u(s): ~/Python/Python/lib/python3.6/site-packages/sqlalchemy/orm/session.py in _flush(self, objects) 2566 self._warn_on_events = True 2567 try: -> 2568 flush_context.execute() 2569 finally: 2570 self._warn_on_events = False ~/Python/Python/lib/python3.6/site-packages/sqlalchemy/orm/unitofwork.py in execute(self) 420 else: 421 for rec in topological.sort(self.dependencies, postsort_actions): --> 422 rec.execute(self) 423 424 def finalize_flush_changes(self): ~/Python/Python/lib/python3.6/site-packages/sqlalchemy/orm/unitofwork.py in execute(self, uow) 587 self.mapper, 588 uow.states_for_mapper_hierarchy(self.mapper, False, False), --> 589 uow, 590 ) 591 ~/Python/Python/lib/python3.6/site-packages/sqlalchemy/orm/persistence.py in save_obj(base_mapper, states, uowtransaction, single) 211 row_switch, 212 update_version_id, --> 213 ) in _organize_states_for_save(base_mapper, states, uowtransaction): 214 if has_identity or row_switch: 215 states_to_update.append( ~/Python/Python/lib/python3.6/site-packages/sqlalchemy/orm/persistence.py in _organize_states_for_save(base_mapper, states, uowtransaction) 372 373 for state, dict_, mapper, connection in _connections_for_states( --> 374 base_mapper, uowtransaction, states 375 ): 376 ~/Python/Python/lib/python3.6/site-packages/sqlalchemy/orm/persistence.py in _connections_for_states(base_mapper, uowtransaction, states) 1602 connection_callable = uowtransaction.session.connection_callable 1603 else: -> 1604 connection = uowtransaction.transaction.connection(base_mapper) 1605 connection_callable = None 1606 ~/Python/Python/lib/python3.6/site-packages/sqlalchemy/orm/session.py in connection(self, bindkey, execution_options, **kwargs) 311 self._assert_active() 312 bind = self.session.get_bind(bindkey, **kwargs) --> 313 return self._connection_for_bind(bind, execution_options) 314 315 def _begin(self, nested=False): ~/Python/Python/lib/python3.6/site-packages/sqlalchemy/orm/session.py in _connection_for_bind(self, bind, execution_options) 417 418 if self._parent: --> 419 conn = self._parent._connection_for_bind(bind, execution_options) 420 if not self.nested: 421 return conn ~/Python/Python/lib/python3.6/site-packages/sqlalchemy/orm/session.py in _connection_for_bind(self, bind, execution_options) 429 ) 430 else: --> 431 conn = bind._contextual_connect() 432 433 if execution_options: AttributeError: 'ResultProxy' object has no attribute '_contextual_connect'
session.execute("show tables")
--------------------------------------------------------------------------- AttributeError Traceback (most recent call last) <ipython-input-30-3e77f0476f1a> in <module> ----> 1 session.execute("show tables") ~/Python/Python/lib/python3.6/site-packages/sqlalchemy/orm/session.py in execute(self, clause, params, mapper, bind, **kw) 1266 bind = self.get_bind(mapper, clause=clause, **kw) 1267 -> 1268 return self._connection_for_bind(bind, close_with_result=True).execute( 1269 clause, params or {} 1270 ) ~/Python/Python/lib/python3.6/site-packages/sqlalchemy/orm/session.py in _connection_for_bind(self, engine, execution_options, **kw) 1128 if self.transaction is not None: 1129 return self.transaction._connection_for_bind( -> 1130 engine, execution_options 1131 ) 1132 else: ~/Python/Python/lib/python3.6/site-packages/sqlalchemy/orm/session.py in _connection_for_bind(self, bind, execution_options) 429 ) 430 else: --> 431 conn = bind._contextual_connect() 432 433 if execution_options: AttributeError: 'ResultProxy' object has no attribute '_contextual_connect'
session.flush()
session.close()
2019-12-06 20:36:31,688 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2019-12-06 20:36:31,689 INFO sqlalchemy.engine.base.Engine INSERT INTO car_users (name, price) VALUES (%(name)s, %(price)s)
2019-12-06 20:36:31,690 INFO sqlalchemy.engine.base.Engine {'name': 'tesla model 3', 'price': 7600}
2019-12-06 20:36:31,690 INFO sqlalchemy.engine.base.Engine ROLLBACK
--------------------------------------------------------------------------- ProgrammingError Traceback (most recent call last) ~/Python/Python/lib/python3.6/site-packages/sqlalchemy/engine/base.py in _execute_context(self, dialect, constructor, statement, parameters, *args) 1245 self.dialect.do_execute( -> 1246 cursor, statement, parameters, context 1247 ) ~/Python/Python/lib/python3.6/site-packages/sqlalchemy/engine/default.py in do_execute(self, cursor, statement, parameters, context) 580 def do_execute(self, cursor, statement, parameters, context=None): --> 581 cursor.execute(statement, parameters) 582 ~/Python/Python/lib/python3.6/site-packages/pymysql/cursors.py in execute(self, query, args) 169 --> 170 result = self._query(query) 171 self._executed = query ~/Python/Python/lib/python3.6/site-packages/pymysql/cursors.py in _query(self, q) 327 self._clear_result() --> 328 conn.query(q) 329 self._do_get_result() ~/Python/Python/lib/python3.6/site-packages/pymysql/connections.py in query(self, sql, unbuffered) 516 self._execute_command(COMMAND.COM_QUERY, sql) --> 517 self._affected_rows = self._read_query_result(unbuffered=unbuffered) 518 return self._affected_rows ~/Python/Python/lib/python3.6/site-packages/pymysql/connections.py in _read_query_result(self, unbuffered) 731 result = MySQLResult(self) --> 732 result.read() 733 self._result = result ~/Python/Python/lib/python3.6/site-packages/pymysql/connections.py in read(self) 1074 try: -> 1075 first_packet = self.connection._read_packet() 1076 ~/Python/Python/lib/python3.6/site-packages/pymysql/connections.py in _read_packet(self, packet_type) 683 packet = packet_type(buff, self.encoding) --> 684 packet.check_error() 685 return packet ~/Python/Python/lib/python3.6/site-packages/pymysql/protocol.py in check_error(self) 219 if DEBUG: print("errno =", errno) --> 220 err.raise_mysql_exception(self._data) 221 ~/Python/Python/lib/python3.6/site-packages/pymysql/err.py in raise_mysql_exception(data) 108 errorclass = error_map.get(errno, InternalError) --> 109 raise errorclass(errno, errval) ProgrammingError: (1146, "Table 'food_data.car_users' doesn't exist") The above exception was the direct cause of the following exception: ProgrammingError Traceback (most recent call last) <ipython-input-24-83163545f569> in <module> ----> 1 session.flush() 2 session.close() ~/Python/Python/lib/python3.6/site-packages/sqlalchemy/orm/session.py in flush(self, objects) 2468 try: 2469 self._flushing = True -> 2470 self._flush(objects) 2471 finally: 2472 self._flushing = False ~/Python/Python/lib/python3.6/site-packages/sqlalchemy/orm/session.py in _flush(self, objects) 2606 except: 2607 with util.safe_reraise(): -> 2608 transaction.rollback(_capture_exception=True) 2609 2610 def bulk_save_objects( ~/Python/Python/lib/python3.6/site-packages/sqlalchemy/util/langhelpers.py in __exit__(self, type_, value, traceback) 66 self._exc_info = None # remove potential circular references 67 if not self.warn_only: ---> 68 compat.reraise(exc_type, exc_value, exc_tb) 69 else: 70 if not compat.py3k and self._exc_info and self._exc_info[1]: ~/Python/Python/lib/python3.6/site-packages/sqlalchemy/util/compat.py in reraise(tp, value, tb, cause) 151 if value.__traceback__ is not tb: 152 raise value.with_traceback(tb) --> 153 raise value 154 155 def u(s): ~/Python/Python/lib/python3.6/site-packages/sqlalchemy/orm/session.py in _flush(self, objects) 2566 self._warn_on_events = True 2567 try: -> 2568 flush_context.execute() 2569 finally: 2570 self._warn_on_events = False ~/Python/Python/lib/python3.6/site-packages/sqlalchemy/orm/unitofwork.py in execute(self) 420 else: 421 for rec in topological.sort(self.dependencies, postsort_actions): --> 422 rec.execute(self) 423 424 def finalize_flush_changes(self): ~/Python/Python/lib/python3.6/site-packages/sqlalchemy/orm/unitofwork.py in execute(self, uow) 587 self.mapper, 588 uow.states_for_mapper_hierarchy(self.mapper, False, False), --> 589 uow, 590 ) 591 ~/Python/Python/lib/python3.6/site-packages/sqlalchemy/orm/persistence.py in save_obj(base_mapper, states, uowtransaction, single) 243 mapper, 244 table, --> 245 insert, 246 ) 247 ~/Python/Python/lib/python3.6/site-packages/sqlalchemy/orm/persistence.py in _emit_insert_statements(base_mapper, uowtransaction, cached_connections, mapper, table, insert, bookkeeping) 1135 else: 1136 result = cached_connections[connection].execute( -> 1137 statement, params 1138 ) 1139 ~/Python/Python/lib/python3.6/site-packages/sqlalchemy/engine/base.py in execute(self, object_, *multiparams, **params) 980 raise exc.ObjectNotExecutableError(object_) 981 else: --> 982 return meth(self, multiparams, params) 983 984 def _execute_function(self, func, multiparams, params): ~/Python/Python/lib/python3.6/site-packages/sqlalchemy/sql/elements.py in _execute_on_connection(self, connection, multiparams, params) 285 def _execute_on_connection(self, connection, multiparams, params): 286 if self.supports_execution: --> 287 return connection._execute_clauseelement(self, multiparams, params) 288 else: 289 raise exc.ObjectNotExecutableError(self) ~/Python/Python/lib/python3.6/site-packages/sqlalchemy/engine/base.py in _execute_clauseelement(self, elem, multiparams, params) 1099 distilled_params, 1100 compiled_sql, -> 1101 distilled_params, 1102 ) 1103 if self._has_events or self.engine._has_events: ~/Python/Python/lib/python3.6/site-packages/sqlalchemy/engine/base.py in _execute_context(self, dialect, constructor, statement, parameters, *args) 1248 except BaseException as e: 1249 self._handle_dbapi_exception( -> 1250 e, statement, parameters, cursor, context 1251 ) 1252 ~/Python/Python/lib/python3.6/site-packages/sqlalchemy/engine/base.py in _handle_dbapi_exception(self, e, statement, parameters, cursor, context) 1474 util.raise_from_cause(newraise, exc_info) 1475 elif should_wrap: -> 1476 util.raise_from_cause(sqlalchemy_exception, exc_info) 1477 else: 1478 util.reraise(*exc_info) ~/Python/Python/lib/python3.6/site-packages/sqlalchemy/util/compat.py in raise_from_cause(exception, exc_info) 396 exc_type, exc_value, exc_tb = exc_info 397 cause = exc_value if exc_value is not exception else None --> 398 reraise(type(exception), exception, tb=exc_tb, cause=cause) 399 400 ~/Python/Python/lib/python3.6/site-packages/sqlalchemy/util/compat.py in reraise(tp, value, tb, cause) 150 value.__cause__ = cause 151 if value.__traceback__ is not tb: --> 152 raise value.with_traceback(tb) 153 raise value 154 ~/Python/Python/lib/python3.6/site-packages/sqlalchemy/engine/base.py in _execute_context(self, dialect, constructor, statement, parameters, *args) 1244 if not evt_handled: 1245 self.dialect.do_execute( -> 1246 cursor, statement, parameters, context 1247 ) 1248 except BaseException as e: ~/Python/Python/lib/python3.6/site-packages/sqlalchemy/engine/default.py in do_execute(self, cursor, statement, parameters, context) 579 580 def do_execute(self, cursor, statement, parameters, context=None): --> 581 cursor.execute(statement, parameters) 582 583 def do_execute_no_params(self, cursor, statement, context=None): ~/Python/Python/lib/python3.6/site-packages/pymysql/cursors.py in execute(self, query, args) 168 query = self.mogrify(query, args) 169 --> 170 result = self._query(query) 171 self._executed = query 172 return result ~/Python/Python/lib/python3.6/site-packages/pymysql/cursors.py in _query(self, q) 326 self._last_executed = q 327 self._clear_result() --> 328 conn.query(q) 329 self._do_get_result() 330 return self.rowcount ~/Python/Python/lib/python3.6/site-packages/pymysql/connections.py in query(self, sql, unbuffered) 515 sql = sql.encode(self.encoding, 'surrogateescape') 516 self._execute_command(COMMAND.COM_QUERY, sql) --> 517 self._affected_rows = self._read_query_result(unbuffered=unbuffered) 518 return self._affected_rows 519 ~/Python/Python/lib/python3.6/site-packages/pymysql/connections.py in _read_query_result(self, unbuffered) 730 else: 731 result = MySQLResult(self) --> 732 result.read() 733 self._result = result 734 if result.server_status is not None: ~/Python/Python/lib/python3.6/site-packages/pymysql/connections.py in read(self) 1073 def read(self): 1074 try: -> 1075 first_packet = self.connection._read_packet() 1076 1077 if first_packet.is_ok_packet(): ~/Python/Python/lib/python3.6/site-packages/pymysql/connections.py in _read_packet(self, packet_type) 682 683 packet = packet_type(buff, self.encoding) --> 684 packet.check_error() 685 return packet 686 ~/Python/Python/lib/python3.6/site-packages/pymysql/protocol.py in check_error(self) 218 errno = self.read_uint16() 219 if DEBUG: print("errno =", errno) --> 220 err.raise_mysql_exception(self._data) 221 222 def dump(self): ~/Python/Python/lib/python3.6/site-packages/pymysql/err.py in raise_mysql_exception(data) 107 errval = data[3:].decode('utf-8', 'replace') 108 errorclass = error_map.get(errno, InternalError) --> 109 raise errorclass(errno, errval) ProgrammingError: (pymysql.err.ProgrammingError) (1146, "Table 'food_data.car_users' doesn't exist") [SQL: INSERT INTO car_users (name, price) VALUES (%(name)s, %(price)s)] [parameters: {'name': 'tesla model 3', 'price': 7600}] (Background on this error at: http://sqlalche.me/e/f405)
conn.commit()
--------------------------------------------------------------------------- AttributeError Traceback (most recent call last) <ipython-input-21-e2f59a37941c> in <module> ----> 1 conn.commit() AttributeError: 'Connection' object has no attribute 'commit'
print(user.id)
None
user
car_users tesla model 3
session.commit()
2019-12-06 20:33:41,957 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
--------------------------------------------------------------------------- ResourceClosedError Traceback (most recent call last) <ipython-input-11-83a1dbdbd92a> in <module> ----> 1 session.commit() ~/Python/Python/lib/python3.6/site-packages/sqlalchemy/orm/session.py in commit(self) 1025 raise sa_exc.InvalidRequestError("No transaction is begun.") 1026 -> 1027 self.transaction.commit() 1028 1029 def prepare(self): ~/Python/Python/lib/python3.6/site-packages/sqlalchemy/orm/session.py in commit(self) 492 self._assert_active(prepared_ok=True) 493 if self._state is not PREPARED: --> 494 self._prepare_impl() 495 496 if self._parent is None or self.nested: ~/Python/Python/lib/python3.6/site-packages/sqlalchemy/orm/session.py in _prepare_impl(self) 471 if self.session._is_clean(): 472 break --> 473 self.session.flush() 474 else: 475 raise exc.FlushError( ~/Python/Python/lib/python3.6/site-packages/sqlalchemy/orm/session.py in flush(self, objects) 2468 try: 2469 self._flushing = True -> 2470 self._flush(objects) 2471 finally: 2472 self._flushing = False ~/Python/Python/lib/python3.6/site-packages/sqlalchemy/orm/session.py in _flush(self, objects) 2606 except: 2607 with util.safe_reraise(): -> 2608 transaction.rollback(_capture_exception=True) 2609 2610 def bulk_save_objects( ~/Python/Python/lib/python3.6/site-packages/sqlalchemy/util/langhelpers.py in __exit__(self, type_, value, traceback) 66 self._exc_info = None # remove potential circular references 67 if not self.warn_only: ---> 68 compat.reraise(exc_type, exc_value, exc_tb) 69 else: 70 if not compat.py3k and self._exc_info and self._exc_info[1]: ~/Python/Python/lib/python3.6/site-packages/sqlalchemy/util/compat.py in reraise(tp, value, tb, cause) 151 if value.__traceback__ is not tb: 152 raise value.with_traceback(tb) --> 153 raise value 154 155 def u(s): ~/Python/Python/lib/python3.6/site-packages/sqlalchemy/orm/session.py in _flush(self, objects) 2566 self._warn_on_events = True 2567 try: -> 2568 flush_context.execute() 2569 finally: 2570 self._warn_on_events = False ~/Python/Python/lib/python3.6/site-packages/sqlalchemy/orm/unitofwork.py in execute(self) 420 else: 421 for rec in topological.sort(self.dependencies, postsort_actions): --> 422 rec.execute(self) 423 424 def finalize_flush_changes(self): ~/Python/Python/lib/python3.6/site-packages/sqlalchemy/orm/unitofwork.py in execute(self, uow) 587 self.mapper, 588 uow.states_for_mapper_hierarchy(self.mapper, False, False), --> 589 uow, 590 ) 591 ~/Python/Python/lib/python3.6/site-packages/sqlalchemy/orm/persistence.py in save_obj(base_mapper, states, uowtransaction, single) 211 row_switch, 212 update_version_id, --> 213 ) in _organize_states_for_save(base_mapper, states, uowtransaction): 214 if has_identity or row_switch: 215 states_to_update.append( ~/Python/Python/lib/python3.6/site-packages/sqlalchemy/orm/persistence.py in _organize_states_for_save(base_mapper, states, uowtransaction) 372 373 for state, dict_, mapper, connection in _connections_for_states( --> 374 base_mapper, uowtransaction, states 375 ): 376 ~/Python/Python/lib/python3.6/site-packages/sqlalchemy/orm/persistence.py in _connections_for_states(base_mapper, uowtransaction, states) 1602 connection_callable = uowtransaction.session.connection_callable 1603 else: -> 1604 connection = uowtransaction.transaction.connection(base_mapper) 1605 connection_callable = None 1606 ~/Python/Python/lib/python3.6/site-packages/sqlalchemy/orm/session.py in connection(self, bindkey, execution_options, **kwargs) 311 self._assert_active() 312 bind = self.session.get_bind(bindkey, **kwargs) --> 313 return self._connection_for_bind(bind, execution_options) 314 315 def _begin(self, nested=False): ~/Python/Python/lib/python3.6/site-packages/sqlalchemy/orm/session.py in _connection_for_bind(self, bind, execution_options) 417 418 if self._parent: --> 419 conn = self._parent._connection_for_bind(bind, execution_options) 420 if not self.nested: 421 return conn ~/Python/Python/lib/python3.6/site-packages/sqlalchemy/orm/session.py in _connection_for_bind(self, bind, execution_options) 439 transaction = conn.begin_nested() 440 else: --> 441 transaction = conn.begin() 442 443 self._connections[conn] = self._connections[conn.engine] = ( ~/Python/Python/lib/python3.6/site-packages/sqlalchemy/engine/base.py in begin(self) 627 628 if self.__transaction is None: --> 629 self.__transaction = RootTransaction(self) 630 return self.__transaction 631 else: ~/Python/Python/lib/python3.6/site-packages/sqlalchemy/engine/base.py in __init__(self, connection) 1748 def __init__(self, connection): 1749 super(RootTransaction, self).__init__(connection, None) -> 1750 self.connection._begin_impl(self) 1751 1752 def _do_rollback(self): ~/Python/Python/lib/python3.6/site-packages/sqlalchemy/engine/base.py in _begin_impl(self, transaction) 719 self.connection._reset_agent = transaction 720 except BaseException as e: --> 721 self._handle_dbapi_exception(e, None, None, None, None) 722 723 def _rollback_impl(self): ~/Python/Python/lib/python3.6/site-packages/sqlalchemy/engine/base.py in _handle_dbapi_exception(self, e, statement, parameters, cursor, context) 1476 util.raise_from_cause(sqlalchemy_exception, exc_info) 1477 else: -> 1478 util.reraise(*exc_info) 1479 1480 finally: ~/Python/Python/lib/python3.6/site-packages/sqlalchemy/util/compat.py in reraise(tp, value, tb, cause) 151 if value.__traceback__ is not tb: 152 raise value.with_traceback(tb) --> 153 raise value 154 155 def u(s): ~/Python/Python/lib/python3.6/site-packages/sqlalchemy/engine/base.py in _begin_impl(self, transaction) 715 716 try: --> 717 self.engine.dialect.do_begin(self.connection) 718 if self.connection._reset_agent is None: 719 self.connection._reset_agent = transaction ~/Python/Python/lib/python3.6/site-packages/sqlalchemy/engine/base.py in connection(self) 382 return self._revalidate_connection() 383 except BaseException as e: --> 384 self._handle_dbapi_exception(e, None, None, None, None) 385 386 def get_isolation_level(self): ~/Python/Python/lib/python3.6/site-packages/sqlalchemy/engine/base.py in _handle_dbapi_exception(self, e, statement, parameters, cursor, context) 1476 util.raise_from_cause(sqlalchemy_exception, exc_info) 1477 else: -> 1478 util.reraise(*exc_info) 1479 1480 finally: ~/Python/Python/lib/python3.6/site-packages/sqlalchemy/util/compat.py in reraise(tp, value, tb, cause) 151 if value.__traceback__ is not tb: 152 raise value.with_traceback(tb) --> 153 raise value 154 155 def u(s): ~/Python/Python/lib/python3.6/site-packages/sqlalchemy/engine/base.py in connection(self) 380 pass 381 try: --> 382 return self._revalidate_connection() 383 except BaseException as e: 384 self._handle_dbapi_exception(e, None, None, None, None) ~/Python/Python/lib/python3.6/site-packages/sqlalchemy/engine/base.py in _revalidate_connection(self) 461 self.__invalid = False 462 return self.__connection --> 463 raise exc.ResourceClosedError("This Connection is closed") 464 465 @property ResourceClosedError: This Connection is closed
conn_db = engine.execute("use food_data")
Base.metadata.create_all(conn_db)
engine.dispose()
engine.execute("use food_data")
engine.execute("show tables").fetchall()
engine
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
from sqlalchemy import Column, Integer, String
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
name = Column(String)
password = Column(String)
def __repr__(self):
return f'User {self.name}'
Base.metadata.create_all(engine)
import pandas as pd
con = db_engine.raw_connection()
data = pd.read_sql('show databases', con)
data['Database'] #data #data.values
# con.execute("use food_data;")
# print(con.execute("show tables;").fetchall())
pd.read_sql('use food_data', con)
con = db_engine.connect()
con.execute("use food_data;")
print(con.execute("show tables;").fetchall())
import pandas as pd
from sqlalchemy import create_engine
engine = create_engine('mysql+mysqlconnector://[user]:[pass]@[host]:[port]/[schema]', echo=False)
connect = engine.raw_connection()
data = pd.read_sql('SELECT * FROM sample_table', cnx)
data.to_sql(name='sample_table2', con=cnx, if_exists = 'append', index=False)