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

Additional Resources#