SQL data into Pandas#
Many companies store data in central databases to ensure efficient data management, security, and accessibility.
Structured Query Language (SQL) is the standard language for interacting with these databases, allowing users to extract and manipulate data. In recent years, Python has become a popular tool for data analysis and manipulation due to its flexibility, ease of use, and extensive libraries. Pandas is a powerful Python library for data manipulation, and it offers seamless integration with SQL databases through SQLAlchemy, a popular SQL toolkit for Python.
By using Python, Pandas, and SQLAlchemy, users can access and analyze data stored in SQL databases and perform complex queries and data transformations. In this article, we will explore how to connect to a SQL database, retrieve data using SQL queries, and analyze the data using Pandas.
How To#
import pandas as pd
import sqlalchemy as sql
conn = sql.create_engine("postgresql://scott:tiger@localhost/test")
df = pd.read_sql_table("sales", conn)
---------------------------------------------------------------------------
OperationalError Traceback (most recent call last)
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/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 /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/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 /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/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 /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/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 /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/pool/base.py:716, in _ConnectionRecord.checkout(cls, pool)
715 else:
--> 716 rec = pool._do_get()
718 try:
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/pool/impl.py:170, in QueuePool._do_get(self)
169 with util.safe_reraise():
--> 170 self._dec_overflow()
171 raise
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py:146, in safe_reraise.__exit__(self, type_, value, traceback)
145 self._exc_info = None # remove potential circular references
--> 146 raise exc_value.with_traceback(exc_tb)
147 else:
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/pool/impl.py:167, in QueuePool._do_get(self)
166 try:
--> 167 return self._create_connection()
168 except:
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/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 /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/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 /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/pool/base.py:903, in _ConnectionRecord.__connect(self)
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 /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py:146, in safe_reraise.__exit__(self, type_, value, traceback)
145 self._exc_info = None # remove potential circular references
--> 146 raise exc_value.with_traceback(exc_tb)
147 else:
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/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 /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/engine/create.py:637, in create_engine.<locals>.connect(connection_record)
635 return connection
--> 637 return dialect.connect(*cargs, **cparams)
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/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 /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/psycopg2/__init__.py:122, in connect(dsn, connection_factory, cursor_factory, **kwargs)
121 dsn = _ext.make_dsn(dsn, **kwargs)
--> 122 conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
123 if cursor_factory is not None:
OperationalError: connection to server at "localhost" (::1), port 5432 failed: Connection refused
Is the server running on that host and accepting TCP/IP connections?
connection to server at "localhost" (127.0.0.1), port 5432 failed: Connection refused
Is the server running on that host and accepting TCP/IP connections?
The above exception was the direct cause of the following exception:
OperationalError Traceback (most recent call last)
Cell In[4], line 1
----> 1 df = pd.read_sql_table("sales", conn)
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/pandas/io/sql.py:336, in read_sql_table(table_name, con, schema, index_col, coerce_float, parse_dates, columns, chunksize, dtype_backend)
333 if dtype_backend is lib.no_default:
334 dtype_backend = "numpy" # type: ignore[assignment]
--> 336 with pandasSQL_builder(con, schema=schema, need_transaction=True) as pandas_sql:
337 if not pandas_sql.has_table(table_name):
338 raise ValueError(f"Table {table_name} not found")
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/pandas/io/sql.py:832, in pandasSQL_builder(con, schema, need_transaction)
829 raise ImportError("Using URI string without sqlalchemy installed.")
831 if sqlalchemy is not None and isinstance(con, (str, sqlalchemy.engine.Connectable)):
--> 832 return SQLDatabase(con, schema, need_transaction)
834 warnings.warn(
835 "pandas only supports SQLAlchemy connectable (engine/connection) or "
836 "database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 "
(...)
839 stacklevel=find_stack_level(),
840 )
841 return SQLiteDatabase(con)
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/pandas/io/sql.py:1539, in SQLDatabase.__init__(self, con, schema, need_transaction)
1537 self.exit_stack.callback(con.dispose)
1538 if isinstance(con, Engine):
-> 1539 con = self.exit_stack.enter_context(con.connect())
1540 if need_transaction and not con.in_transaction():
1541 self.exit_stack.enter_context(con.begin())
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/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 /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/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 /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/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 /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/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 /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/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 /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/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 /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/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 /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/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 /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/pool/impl.py:170, in QueuePool._do_get(self)
168 except:
169 with util.safe_reraise():
--> 170 self._dec_overflow()
171 raise
172 else:
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py:146, in safe_reraise.__exit__(self, type_, value, traceback)
144 assert exc_value is not None
145 self._exc_info = None # remove potential circular references
--> 146 raise exc_value.with_traceback(exc_tb)
147 else:
148 self._exc_info = None # remove potential circular references
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/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 /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/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 /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/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 /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/pool/base.py:903, in _ConnectionRecord.__connect(self)
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
907 if pool.dispatch.first_connect:
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py:146, in safe_reraise.__exit__(self, type_, value, traceback)
144 assert exc_value is not None
145 self._exc_info = None # remove potential circular references
--> 146 raise exc_value.with_traceback(exc_tb)
147 else:
148 self._exc_info = None # remove potential circular references
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/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 /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/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 /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/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 /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/psycopg2/__init__.py:122, in connect(dsn, connection_factory, cursor_factory, **kwargs)
119 kwasync['async_'] = kwargs.pop('async_')
121 dsn = _ext.make_dsn(dsn, **kwargs)
--> 122 conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
123 if cursor_factory is not None:
124 conn.cursor_factory = cursor_factory
OperationalError: (psycopg2.OperationalError) connection to server at "localhost" (::1), port 5432 failed: Connection refused
Is the server running on that host and accepting TCP/IP connections?
connection to server at "localhost" (127.0.0.1), port 5432 failed: Connection refused
Is the server running on that host and accepting TCP/IP connections?
(Background on this error at: https://sqlalche.me/e/20/e3q8)
query = "SELECT * FROM sales"
df = pd.read_sql_query(query, conn)
---------------------------------------------------------------------------
OperationalError Traceback (most recent call last)
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/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 /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/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 /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/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 /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/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 /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/pool/base.py:716, in _ConnectionRecord.checkout(cls, pool)
715 else:
--> 716 rec = pool._do_get()
718 try:
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/pool/impl.py:170, in QueuePool._do_get(self)
169 with util.safe_reraise():
--> 170 self._dec_overflow()
171 raise
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py:146, in safe_reraise.__exit__(self, type_, value, traceback)
145 self._exc_info = None # remove potential circular references
--> 146 raise exc_value.with_traceback(exc_tb)
147 else:
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/pool/impl.py:167, in QueuePool._do_get(self)
166 try:
--> 167 return self._create_connection()
168 except:
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/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 /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/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 /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/pool/base.py:903, in _ConnectionRecord.__connect(self)
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 /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py:146, in safe_reraise.__exit__(self, type_, value, traceback)
145 self._exc_info = None # remove potential circular references
--> 146 raise exc_value.with_traceback(exc_tb)
147 else:
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/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 /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/engine/create.py:637, in create_engine.<locals>.connect(connection_record)
635 return connection
--> 637 return dialect.connect(*cargs, **cparams)
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/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 /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/psycopg2/__init__.py:122, in connect(dsn, connection_factory, cursor_factory, **kwargs)
121 dsn = _ext.make_dsn(dsn, **kwargs)
--> 122 conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
123 if cursor_factory is not None:
OperationalError: connection to server at "localhost" (::1), port 5432 failed: Connection refused
Is the server running on that host and accepting TCP/IP connections?
connection to server at "localhost" (127.0.0.1), port 5432 failed: Connection refused
Is the server running on that host and accepting TCP/IP connections?
The above exception was the direct cause of the following exception:
OperationalError Traceback (most recent call last)
Cell In[5], line 2
1 query = "SELECT * FROM sales"
----> 2 df = pd.read_sql_query(query, conn)
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/pandas/io/sql.py:468, in read_sql_query(sql, con, index_col, coerce_float, params, parse_dates, chunksize, dtype, dtype_backend)
465 if dtype_backend is lib.no_default:
466 dtype_backend = "numpy" # type: ignore[assignment]
--> 468 with pandasSQL_builder(con) as pandas_sql:
469 return pandas_sql.read_query(
470 sql,
471 index_col=index_col,
(...)
477 dtype_backend=dtype_backend,
478 )
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/pandas/io/sql.py:832, in pandasSQL_builder(con, schema, need_transaction)
829 raise ImportError("Using URI string without sqlalchemy installed.")
831 if sqlalchemy is not None and isinstance(con, (str, sqlalchemy.engine.Connectable)):
--> 832 return SQLDatabase(con, schema, need_transaction)
834 warnings.warn(
835 "pandas only supports SQLAlchemy connectable (engine/connection) or "
836 "database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 "
(...)
839 stacklevel=find_stack_level(),
840 )
841 return SQLiteDatabase(con)
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/pandas/io/sql.py:1539, in SQLDatabase.__init__(self, con, schema, need_transaction)
1537 self.exit_stack.callback(con.dispose)
1538 if isinstance(con, Engine):
-> 1539 con = self.exit_stack.enter_context(con.connect())
1540 if need_transaction and not con.in_transaction():
1541 self.exit_stack.enter_context(con.begin())
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/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 /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/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 /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/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 /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/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 /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/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 /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/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 /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/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 /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/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 /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/pool/impl.py:170, in QueuePool._do_get(self)
168 except:
169 with util.safe_reraise():
--> 170 self._dec_overflow()
171 raise
172 else:
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py:146, in safe_reraise.__exit__(self, type_, value, traceback)
144 assert exc_value is not None
145 self._exc_info = None # remove potential circular references
--> 146 raise exc_value.with_traceback(exc_tb)
147 else:
148 self._exc_info = None # remove potential circular references
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/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 /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/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 /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/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 /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/pool/base.py:903, in _ConnectionRecord.__connect(self)
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
907 if pool.dispatch.first_connect:
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py:146, in safe_reraise.__exit__(self, type_, value, traceback)
144 assert exc_value is not None
145 self._exc_info = None # remove potential circular references
--> 146 raise exc_value.with_traceback(exc_tb)
147 else:
148 self._exc_info = None # remove potential circular references
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/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 /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/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 /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/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 /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/psycopg2/__init__.py:122, in connect(dsn, connection_factory, cursor_factory, **kwargs)
119 kwasync['async_'] = kwargs.pop('async_')
121 dsn = _ext.make_dsn(dsn, **kwargs)
--> 122 conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
123 if cursor_factory is not None:
124 conn.cursor_factory = cursor_factory
OperationalError: (psycopg2.OperationalError) connection to server at "localhost" (::1), port 5432 failed: Connection refused
Is the server running on that host and accepting TCP/IP connections?
connection to server at "localhost" (127.0.0.1), port 5432 failed: Connection refused
Is the server running on that host and accepting TCP/IP connections?
(Background on this error at: https://sqlalche.me/e/20/e3q8)
query = """SELECT customers, total_spend from sales
WHERE YEAR = 2019
LIMIT 1000
"""
df = pd.read_sql_query(query, conn)
---------------------------------------------------------------------------
OperationalError Traceback (most recent call last)
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/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 /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/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 /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/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 /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/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 /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/pool/base.py:716, in _ConnectionRecord.checkout(cls, pool)
715 else:
--> 716 rec = pool._do_get()
718 try:
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/pool/impl.py:170, in QueuePool._do_get(self)
169 with util.safe_reraise():
--> 170 self._dec_overflow()
171 raise
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py:146, in safe_reraise.__exit__(self, type_, value, traceback)
145 self._exc_info = None # remove potential circular references
--> 146 raise exc_value.with_traceback(exc_tb)
147 else:
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/pool/impl.py:167, in QueuePool._do_get(self)
166 try:
--> 167 return self._create_connection()
168 except:
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/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 /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/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 /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/pool/base.py:903, in _ConnectionRecord.__connect(self)
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 /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py:146, in safe_reraise.__exit__(self, type_, value, traceback)
145 self._exc_info = None # remove potential circular references
--> 146 raise exc_value.with_traceback(exc_tb)
147 else:
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/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 /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/engine/create.py:637, in create_engine.<locals>.connect(connection_record)
635 return connection
--> 637 return dialect.connect(*cargs, **cparams)
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/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 /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/psycopg2/__init__.py:122, in connect(dsn, connection_factory, cursor_factory, **kwargs)
121 dsn = _ext.make_dsn(dsn, **kwargs)
--> 122 conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
123 if cursor_factory is not None:
OperationalError: connection to server at "localhost" (::1), port 5432 failed: Connection refused
Is the server running on that host and accepting TCP/IP connections?
connection to server at "localhost" (127.0.0.1), port 5432 failed: Connection refused
Is the server running on that host and accepting TCP/IP connections?
The above exception was the direct cause of the following exception:
OperationalError Traceback (most recent call last)
Cell In[6], line 5
1 query = """SELECT customers, total_spend from sales
2 WHERE YEAR = 2019
3 LIMIT 1000
4 """
----> 5 df = pd.read_sql_query(query, conn)
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/pandas/io/sql.py:468, in read_sql_query(sql, con, index_col, coerce_float, params, parse_dates, chunksize, dtype, dtype_backend)
465 if dtype_backend is lib.no_default:
466 dtype_backend = "numpy" # type: ignore[assignment]
--> 468 with pandasSQL_builder(con) as pandas_sql:
469 return pandas_sql.read_query(
470 sql,
471 index_col=index_col,
(...)
477 dtype_backend=dtype_backend,
478 )
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/pandas/io/sql.py:832, in pandasSQL_builder(con, schema, need_transaction)
829 raise ImportError("Using URI string without sqlalchemy installed.")
831 if sqlalchemy is not None and isinstance(con, (str, sqlalchemy.engine.Connectable)):
--> 832 return SQLDatabase(con, schema, need_transaction)
834 warnings.warn(
835 "pandas only supports SQLAlchemy connectable (engine/connection) or "
836 "database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 "
(...)
839 stacklevel=find_stack_level(),
840 )
841 return SQLiteDatabase(con)
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/pandas/io/sql.py:1539, in SQLDatabase.__init__(self, con, schema, need_transaction)
1537 self.exit_stack.callback(con.dispose)
1538 if isinstance(con, Engine):
-> 1539 con = self.exit_stack.enter_context(con.connect())
1540 if need_transaction and not con.in_transaction():
1541 self.exit_stack.enter_context(con.begin())
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/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 /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/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 /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/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 /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/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 /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/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 /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/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 /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/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 /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/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 /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/pool/impl.py:170, in QueuePool._do_get(self)
168 except:
169 with util.safe_reraise():
--> 170 self._dec_overflow()
171 raise
172 else:
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py:146, in safe_reraise.__exit__(self, type_, value, traceback)
144 assert exc_value is not None
145 self._exc_info = None # remove potential circular references
--> 146 raise exc_value.with_traceback(exc_tb)
147 else:
148 self._exc_info = None # remove potential circular references
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/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 /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/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 /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/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 /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/pool/base.py:903, in _ConnectionRecord.__connect(self)
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
907 if pool.dispatch.first_connect:
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py:146, in safe_reraise.__exit__(self, type_, value, traceback)
144 assert exc_value is not None
145 self._exc_info = None # remove potential circular references
--> 146 raise exc_value.with_traceback(exc_tb)
147 else:
148 self._exc_info = None # remove potential circular references
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/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 /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/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 /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/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 /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/psycopg2/__init__.py:122, in connect(dsn, connection_factory, cursor_factory, **kwargs)
119 kwasync['async_'] = kwargs.pop('async_')
121 dsn = _ext.make_dsn(dsn, **kwargs)
--> 122 conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
123 if cursor_factory is not None:
124 conn.cursor_factory = cursor_factory
OperationalError: (psycopg2.OperationalError) connection to server at "localhost" (::1), port 5432 failed: Connection refused
Is the server running on that host and accepting TCP/IP connections?
connection to server at "localhost" (127.0.0.1), port 5432 failed: Connection refused
Is the server running on that host and accepting TCP/IP connections?
(Background on this error at: https://sqlalche.me/e/20/e3q8)
Exercise#
Try out one of the free online SQL playgrounds