finance

import pandas as pd

#df = pd.read_csv('./data/2016_FS.txt', sep = "\t", engine='python', encoding='cp949')

import pymysql
from sqlalchemy import create_engine, text
# conn = pymysql.connect(host='localhost',
#                              user='admin',
#                              password='admin1234',
#                              db='STOCKDB',
#                              charset='utf8')

engine = create_engine('mysql+pymysql://admin:admin1234@localhost/STOCKDB')
connn = engine.connect()
# curs = conn.cursor()

---------------------------------------------------------------------------
ConnectionRefusedError                    Traceback (most recent call last)
File ~/work/tdc/tdc/.venv/lib/python3.10/site-packages/pymysql/connections.py:616, in Connection.connect(self, sock)
    615 try:
--> 616     sock = socket.create_connection(
    617         (self.host, self.port), self.connect_timeout, **kwargs
    618     )
    619     break

File /opt/hostedtoolcache/Python/3.10.12/x64/lib/python3.10/socket.py:845, in create_connection(address, timeout, source_address)
    844 try:
--> 845     raise err
    846 finally:
    847     # Break explicitly a reference cycle

File /opt/hostedtoolcache/Python/3.10.12/x64/lib/python3.10/socket.py:833, in create_connection(address, timeout, source_address)
    832     sock.bind(source_address)
--> 833 sock.connect(sa)
    834 # Break explicitly a reference cycle

ConnectionRefusedError: [Errno 111] Connection refused

During handling of the above exception, another exception occurred:

OperationalError                          Traceback (most recent call last)
File ~/work/tdc/tdc/.venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py:145, in Connection.__init__(self, engine, connection, _has_events, _allow_revalidate, _allow_autobegin)
    144 try:
--> 145     self._dbapi_connection = engine.raw_connection()
    146 except dialect.loaded_dbapi.Error as err:

File ~/work/tdc/tdc/.venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py:3288, in Engine.raw_connection(self)
   3267 """Return a "raw" DBAPI connection from the connection pool.
   3268 
   3269 The returned object is a proxied version of the DBAPI
   (...)
   3286 
   3287 """
-> 3288 return self.pool.connect()

File ~/work/tdc/tdc/.venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py:452, in Pool.connect(self)
    445 """Return a DBAPI connection from the pool.
    446 
    447 The connection is instrumented such that when its
   (...)
    450 
    451 """
--> 452 return _ConnectionFairy._checkout(self)

File ~/work/tdc/tdc/.venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py:1267, in _ConnectionFairy._checkout(cls, pool, threadconns, fairy)
   1266 if not fairy:
-> 1267     fairy = _ConnectionRecord.checkout(pool)
   1269     if threadconns is not None:

File ~/work/tdc/tdc/.venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py:716, in _ConnectionRecord.checkout(cls, pool)
    715 else:
--> 716     rec = pool._do_get()
    718 try:

File ~/work/tdc/tdc/.venv/lib/python3.10/site-packages/sqlalchemy/pool/impl.py:169, in QueuePool._do_get(self)
    168 except:
--> 169     with util.safe_reraise():
    170         self._dec_overflow()

File ~/work/tdc/tdc/.venv/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py:147, in safe_reraise.__exit__(self, type_, value, traceback)
    146     self._exc_info = None  # remove potential circular references
--> 147     raise exc_value.with_traceback(exc_tb)
    148 else:

File ~/work/tdc/tdc/.venv/lib/python3.10/site-packages/sqlalchemy/pool/impl.py:167, in QueuePool._do_get(self)
    166 try:
--> 167     return self._create_connection()
    168 except:

File ~/work/tdc/tdc/.venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py:393, in Pool._create_connection(self)
    391 """Called by subclasses to create a new ConnectionRecord."""
--> 393 return _ConnectionRecord(self)

File ~/work/tdc/tdc/.venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py:678, in _ConnectionRecord.__init__(self, pool, connect)
    677 if connect:
--> 678     self.__connect()
    679 self.finalize_callback = deque()

File ~/work/tdc/tdc/.venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py:902, in _ConnectionRecord.__connect(self)
    901 except BaseException as e:
--> 902     with util.safe_reraise():
    903         pool.logger.debug("Error on connect(): %s", e)

File ~/work/tdc/tdc/.venv/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py:147, in safe_reraise.__exit__(self, type_, value, traceback)
    146     self._exc_info = None  # remove potential circular references
--> 147     raise exc_value.with_traceback(exc_tb)
    148 else:

File ~/work/tdc/tdc/.venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py:898, in _ConnectionRecord.__connect(self)
    897 self.starttime = time.time()
--> 898 self.dbapi_connection = connection = pool._invoke_creator(self)
    899 pool.logger.debug("Created new connection %r", connection)

File ~/work/tdc/tdc/.venv/lib/python3.10/site-packages/sqlalchemy/engine/create.py:637, in create_engine.<locals>.connect(connection_record)
    635             return connection
--> 637 return dialect.connect(*cargs, **cparams)

File ~/work/tdc/tdc/.venv/lib/python3.10/site-packages/sqlalchemy/engine/default.py:615, in DefaultDialect.connect(self, *cargs, **cparams)
    613 def connect(self, *cargs, **cparams):
    614     # inherits the docstring from interfaces.Dialect.connect
--> 615     return self.loaded_dbapi.connect(*cargs, **cparams)

File ~/work/tdc/tdc/.venv/lib/python3.10/site-packages/pymysql/connections.py:352, in Connection.__init__(self, user, password, host, database, unix_socket, port, charset, sql_mode, read_default_file, conv, use_unicode, client_flag, cursorclass, init_command, connect_timeout, read_default_group, autocommit, local_infile, max_allowed_packet, defer_connect, auth_plugin_map, read_timeout, write_timeout, bind_address, binary_prefix, program_name, server_public_key, ssl, ssl_ca, ssl_cert, ssl_disabled, ssl_key, ssl_verify_cert, ssl_verify_identity, compress, named_pipe, passwd, db)
    351 else:
--> 352     self.connect()

File ~/work/tdc/tdc/.venv/lib/python3.10/site-packages/pymysql/connections.py:668, in Connection.connect(self, sock)
    667         print(exc.traceback)
--> 668     raise exc
    670 # If e is neither DatabaseError or IOError, It's a bug.
    671 # But raising AssertionError hides original error.
    672 # So just reraise it.

OperationalError: (2003, "Can't connect to MySQL server on 'localhost' ([Errno 111] Connection refused)")

The above exception was the direct cause of the following exception:

OperationalError                          Traceback (most recent call last)
Cell In[3], line 10
      3 # conn = pymysql.connect(host='localhost',
      4 #                              user='admin',
      5 #                              password='admin1234',
      6 #                              db='STOCKDB',
      7 #                              charset='utf8')
      9 engine = create_engine('mysql+pymysql://admin:admin1234@localhost/STOCKDB')
---> 10 connn = engine.connect()
     11 # curs = conn.cursor()

File ~/work/tdc/tdc/.venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py:3264, in Engine.connect(self)
   3241 def connect(self) -> Connection:
   3242     """Return a new :class:`_engine.Connection` object.
   3243 
   3244     The :class:`_engine.Connection` acts as a Python context manager, so
   (...)
   3261 
   3262     """
-> 3264     return self._connection_cls(self)

File ~/work/tdc/tdc/.venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py:147, in Connection.__init__(self, engine, connection, _has_events, _allow_revalidate, _allow_autobegin)
    145         self._dbapi_connection = engine.raw_connection()
    146     except dialect.loaded_dbapi.Error as err:
--> 147         Connection._handle_dbapi_exception_noconnection(
    148             err, dialect, engine
    149         )
    150         raise
    151 else:

File ~/work/tdc/tdc/.venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py:2426, in Connection._handle_dbapi_exception_noconnection(cls, e, dialect, engine, is_disconnect, invalidate_pool_on_disconnect, is_pre_ping)
   2424 elif should_wrap:
   2425     assert sqlalchemy_exception is not None
-> 2426     raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
   2427 else:
   2428     assert exc_info[1] is not None

File ~/work/tdc/tdc/.venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py:145, in Connection.__init__(self, engine, connection, _has_events, _allow_revalidate, _allow_autobegin)
    143 if connection is None:
    144     try:
--> 145         self._dbapi_connection = engine.raw_connection()
    146     except dialect.loaded_dbapi.Error as err:
    147         Connection._handle_dbapi_exception_noconnection(
    148             err, dialect, engine
    149         )

File ~/work/tdc/tdc/.venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py:3288, in Engine.raw_connection(self)
   3266 def raw_connection(self) -> PoolProxiedConnection:
   3267     """Return a "raw" DBAPI connection from the connection pool.
   3268 
   3269     The returned object is a proxied version of the DBAPI
   (...)
   3286 
   3287     """
-> 3288     return self.pool.connect()

File ~/work/tdc/tdc/.venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py:452, in Pool.connect(self)
    444 def connect(self) -> PoolProxiedConnection:
    445     """Return a DBAPI connection from the pool.
    446 
    447     The connection is instrumented such that when its
   (...)
    450 
    451     """
--> 452     return _ConnectionFairy._checkout(self)

File ~/work/tdc/tdc/.venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py:1267, in _ConnectionFairy._checkout(cls, pool, threadconns, fairy)
   1259 @classmethod
   1260 def _checkout(
   1261     cls,
   (...)
   1264     fairy: Optional[_ConnectionFairy] = None,
   1265 ) -> _ConnectionFairy:
   1266     if not fairy:
-> 1267         fairy = _ConnectionRecord.checkout(pool)
   1269         if threadconns is not None:
   1270             threadconns.current = weakref.ref(fairy)

File ~/work/tdc/tdc/.venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py:716, in _ConnectionRecord.checkout(cls, pool)
    714     rec = cast(_ConnectionRecord, pool._do_get())
    715 else:
--> 716     rec = pool._do_get()
    718 try:
    719     dbapi_connection = rec.get_connection()

File ~/work/tdc/tdc/.venv/lib/python3.10/site-packages/sqlalchemy/pool/impl.py:169, in QueuePool._do_get(self)
    167     return self._create_connection()
    168 except:
--> 169     with util.safe_reraise():
    170         self._dec_overflow()
    171     raise

File ~/work/tdc/tdc/.venv/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py:147, in safe_reraise.__exit__(self, type_, value, traceback)
    145     assert exc_value is not None
    146     self._exc_info = None  # remove potential circular references
--> 147     raise exc_value.with_traceback(exc_tb)
    148 else:
    149     self._exc_info = None  # remove potential circular references

File ~/work/tdc/tdc/.venv/lib/python3.10/site-packages/sqlalchemy/pool/impl.py:167, in QueuePool._do_get(self)
    165 if self._inc_overflow():
    166     try:
--> 167         return self._create_connection()
    168     except:
    169         with util.safe_reraise():

File ~/work/tdc/tdc/.venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py:393, in Pool._create_connection(self)
    390 def _create_connection(self) -> ConnectionPoolEntry:
    391     """Called by subclasses to create a new ConnectionRecord."""
--> 393     return _ConnectionRecord(self)

File ~/work/tdc/tdc/.venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py:678, in _ConnectionRecord.__init__(self, pool, connect)
    676 self.__pool = pool
    677 if connect:
--> 678     self.__connect()
    679 self.finalize_callback = deque()

File ~/work/tdc/tdc/.venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py:902, in _ConnectionRecord.__connect(self)
    900     self.fresh = True
    901 except BaseException as e:
--> 902     with util.safe_reraise():
    903         pool.logger.debug("Error on connect(): %s", e)
    904 else:
    905     # in SQLAlchemy 1.4 the first_connect event is not used by
    906     # the engine, so this will usually not be set

File ~/work/tdc/tdc/.venv/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py:147, in safe_reraise.__exit__(self, type_, value, traceback)
    145     assert exc_value is not None
    146     self._exc_info = None  # remove potential circular references
--> 147     raise exc_value.with_traceback(exc_tb)
    148 else:
    149     self._exc_info = None  # remove potential circular references

File ~/work/tdc/tdc/.venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py:898, in _ConnectionRecord.__connect(self)
    896 try:
    897     self.starttime = time.time()
--> 898     self.dbapi_connection = connection = pool._invoke_creator(self)
    899     pool.logger.debug("Created new connection %r", connection)
    900     self.fresh = True

File ~/work/tdc/tdc/.venv/lib/python3.10/site-packages/sqlalchemy/engine/create.py:637, in create_engine.<locals>.connect(connection_record)
    634         if connection is not None:
    635             return connection
--> 637 return dialect.connect(*cargs, **cparams)

File ~/work/tdc/tdc/.venv/lib/python3.10/site-packages/sqlalchemy/engine/default.py:615, in DefaultDialect.connect(self, *cargs, **cparams)
    613 def connect(self, *cargs, **cparams):
    614     # inherits the docstring from interfaces.Dialect.connect
--> 615     return self.loaded_dbapi.connect(*cargs, **cparams)

File ~/work/tdc/tdc/.venv/lib/python3.10/site-packages/pymysql/connections.py:352, in Connection.__init__(self, user, password, host, database, unix_socket, port, charset, sql_mode, read_default_file, conv, use_unicode, client_flag, cursorclass, init_command, connect_timeout, read_default_group, autocommit, local_infile, max_allowed_packet, defer_connect, auth_plugin_map, read_timeout, write_timeout, bind_address, binary_prefix, program_name, server_public_key, ssl, ssl_ca, ssl_cert, ssl_disabled, ssl_key, ssl_verify_cert, ssl_verify_identity, compress, named_pipe, passwd, db)
    350     self._sock = None
    351 else:
--> 352     self.connect()

File ~/work/tdc/tdc/.venv/lib/python3.10/site-packages/pymysql/connections.py:668, in Connection.connect(self, sock)
    666     if DEBUG:
    667         print(exc.traceback)
--> 668     raise exc
    670 # If e is neither DatabaseError or IOError, It's a bug.
    671 # But raising AssertionError hides original error.
    672 # So just reraise it.
    673 raise

OperationalError: (pymysql.err.OperationalError) (2003, "Can't connect to MySQL server on 'localhost' ([Errno 111] Connection refused)")
(Background on this error at: https://sqlalche.me/e/20/e3q8)

# df.to_sql(name='fs1', con=engine, if_exists='append', index=False)

sql = "select * from pb_fs"
result = pd.read_sql(sql, engine)
# with engine.connect() as conn:
#     result = conn.execute(text(sql)).fetchall()

result.columns

Index(['basDt', 'crno', 'bizYear', 'fnclDcd', 'fnclDcdNm', 'enpSaleAmt',
       'enpBzopPft', 'iclsPalClcAmt', 'enpCrtmNpf', 'enpTastAmt', 'enpTdbtAmt',
       'enpTcptAmt', 'enpCptlAmt', 'fnclDebtRto', 'curCd'],
      dtype='object')

result.head()

	basDt	crno	bizYear	fnclDcd	fnclDcdNm	enpSaleAmt	enpBzopPft	iclsPalClcAmt	enpCrtmNpf	enpTastAmt	enpTdbtAmt	enpTcptAmt	enpCptlAmt	fnclDebtRto	curCd
0	20221231	1101110043870	2022	ifrs_ConsolidatedMember	연결요약재무제표	340426327300	29915077882	23085548187	21591210925	461136459052	82463208091	378673250961	27931470000	21.7768769993	KRW
1	20221231	1601110007032	2022	ifrs_ConsolidatedMember	연결요약재무제표	117058740510	-6234392973	-19007690242	-13001279643	149185245583	109161230426	40024015157	48069232500	272.7393291198	KRW
2	20221231	1101110013287	2022	ifrs_ConsolidatedMember	연결요약재무제표	389227582838	31895306272	1648315655	4415249761	1248318313535	493309854178	755008459357	13707635000	65.3383214538	KRW
3	20221231	1101110026181	2022	ifrs_ConsolidatedMember	연결요약재무제표	3316778000000	132324000000	126664000000	105849000000	4547781000000	2024270000000	2523511000000	44342000000	80.2164127678	KRW
4	20221231	1101110028591	2022	ifrs_ConsolidatedMember	연결요약재무제표	2497554821448	190571249139	121380678145	86816258840	3333438311387	2172409936540	1161028374847	368842230000	187.1108392873	KRW