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:3293, in Engine.raw_connection(self)
   3272 """Return a "raw" DBAPI connection from the connection pool.
   3273 
   3274 The returned object is a proxied version of the DBAPI
   (...)
   3291 
   3292 """
-> 3293 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:1269, in _ConnectionFairy._checkout(cls, pool, threadconns, fairy)
   1268 if not fairy:
-> 1269     fairy = _ConnectionRecord.checkout(pool)
   1271     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:645, in create_engine.<locals>.connect(connection_record)
    643             return connection
--> 645 return dialect.connect(*cargs, **cparams)

File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/engine/default.py:616, in DefaultDialect.connect(self, *cargs, **cparams)
    614 def connect(self, *cargs, **cparams):
    615     # inherits the docstring from interfaces.Dialect.connect
--> 616     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:3269, in Engine.connect(self)
   3246 def connect(self) -> Connection:
   3247     """Return a new :class:`_engine.Connection` object.
   3248 
   3249     The :class:`_engine.Connection` acts as a Python context manager, so
   (...)
   3266 
   3267     """
-> 3269     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:2431, in Connection._handle_dbapi_exception_noconnection(cls, e, dialect, engine, is_disconnect, invalidate_pool_on_disconnect, is_pre_ping)
   2429 elif should_wrap:
   2430     assert sqlalchemy_exception is not None
-> 2431     raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
   2432 else:
   2433     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:3293, in Engine.raw_connection(self)
   3271 def raw_connection(self) -> PoolProxiedConnection:
   3272     """Return a "raw" DBAPI connection from the connection pool.
   3273 
   3274     The returned object is a proxied version of the DBAPI
   (...)
   3291 
   3292     """
-> 3293     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:1269, in _ConnectionFairy._checkout(cls, pool, threadconns, fairy)
   1261 @classmethod
   1262 def _checkout(
   1263     cls,
   (...)
   1266     fairy: Optional[_ConnectionFairy] = None,
   1267 ) -> _ConnectionFairy:
   1268     if not fairy:
-> 1269         fairy = _ConnectionRecord.checkout(pool)
   1271         if threadconns is not None:
   1272             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:645, in create_engine.<locals>.connect(connection_record)
    642         if connection is not None:
    643             return connection
--> 645 return dialect.connect(*cargs, **cparams)

File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/engine/default.py:616, in DefaultDialect.connect(self, *cargs, **cparams)
    614 def connect(self, *cargs, **cparams):
    615     # inherits the docstring from interfaces.Dialect.connect
--> 616     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:3293, in Engine.raw_connection(self)
   3272 """Return a "raw" DBAPI connection from the connection pool.
   3273 
   3274 The returned object is a proxied version of the DBAPI
   (...)
   3291 
   3292 """
-> 3293 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:1269, in _ConnectionFairy._checkout(cls, pool, threadconns, fairy)
   1268 if not fairy:
-> 1269     fairy = _ConnectionRecord.checkout(pool)
   1271     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:645, in create_engine.<locals>.connect(connection_record)
    643             return connection
--> 645 return dialect.connect(*cargs, **cparams)

File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/engine/default.py:616, in DefaultDialect.connect(self, *cargs, **cparams)
    614 def connect(self, *cargs, **cparams):
    615     # inherits the docstring from interfaces.Dialect.connect
--> 616     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:3269, in Engine.connect(self)
   3246 def connect(self) -> Connection:
   3247     """Return a new :class:`_engine.Connection` object.
   3248 
   3249     The :class:`_engine.Connection` acts as a Python context manager, so
   (...)
   3266 
   3267     """
-> 3269     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:2431, in Connection._handle_dbapi_exception_noconnection(cls, e, dialect, engine, is_disconnect, invalidate_pool_on_disconnect, is_pre_ping)
   2429 elif should_wrap:
   2430     assert sqlalchemy_exception is not None
-> 2431     raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
   2432 else:
   2433     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:3293, in Engine.raw_connection(self)
   3271 def raw_connection(self) -> PoolProxiedConnection:
   3272     """Return a "raw" DBAPI connection from the connection pool.
   3273 
   3274     The returned object is a proxied version of the DBAPI
   (...)
   3291 
   3292     """
-> 3293     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:1269, in _ConnectionFairy._checkout(cls, pool, threadconns, fairy)
   1261 @classmethod
   1262 def _checkout(
   1263     cls,
   (...)
   1266     fairy: Optional[_ConnectionFairy] = None,
   1267 ) -> _ConnectionFairy:
   1268     if not fairy:
-> 1269         fairy = _ConnectionRecord.checkout(pool)
   1271         if threadconns is not None:
   1272             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:645, in create_engine.<locals>.connect(connection_record)
    642         if connection is not None:
    643             return connection
--> 645 return dialect.connect(*cargs, **cparams)

File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/engine/default.py:616, in DefaultDialect.connect(self, *cargs, **cparams)
    614 def connect(self, *cargs, **cparams):
    615     # inherits the docstring from interfaces.Dialect.connect
--> 616     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:3293, in Engine.raw_connection(self)
   3272 """Return a "raw" DBAPI connection from the connection pool.
   3273 
   3274 The returned object is a proxied version of the DBAPI
   (...)
   3291 
   3292 """
-> 3293 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:1269, in _ConnectionFairy._checkout(cls, pool, threadconns, fairy)
   1268 if not fairy:
-> 1269     fairy = _ConnectionRecord.checkout(pool)
   1271     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:645, in create_engine.<locals>.connect(connection_record)
    643             return connection
--> 645 return dialect.connect(*cargs, **cparams)

File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/engine/default.py:616, in DefaultDialect.connect(self, *cargs, **cparams)
    614 def connect(self, *cargs, **cparams):
    615     # inherits the docstring from interfaces.Dialect.connect
--> 616     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:3269, in Engine.connect(self)
   3246 def connect(self) -> Connection:
   3247     """Return a new :class:`_engine.Connection` object.
   3248 
   3249     The :class:`_engine.Connection` acts as a Python context manager, so
   (...)
   3266 
   3267     """
-> 3269     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:2431, in Connection._handle_dbapi_exception_noconnection(cls, e, dialect, engine, is_disconnect, invalidate_pool_on_disconnect, is_pre_ping)
   2429 elif should_wrap:
   2430     assert sqlalchemy_exception is not None
-> 2431     raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
   2432 else:
   2433     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:3293, in Engine.raw_connection(self)
   3271 def raw_connection(self) -> PoolProxiedConnection:
   3272     """Return a "raw" DBAPI connection from the connection pool.
   3273 
   3274     The returned object is a proxied version of the DBAPI
   (...)
   3291 
   3292     """
-> 3293     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:1269, in _ConnectionFairy._checkout(cls, pool, threadconns, fairy)
   1261 @classmethod
   1262 def _checkout(
   1263     cls,
   (...)
   1266     fairy: Optional[_ConnectionFairy] = None,
   1267 ) -> _ConnectionFairy:
   1268     if not fairy:
-> 1269         fairy = _ConnectionRecord.checkout(pool)
   1271         if threadconns is not None:
   1272             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:645, in create_engine.<locals>.connect(connection_record)
    642         if connection is not None:
    643             return connection
--> 645 return dialect.connect(*cargs, **cparams)

File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/engine/default.py:616, in DefaultDialect.connect(self, *cargs, **cparams)
    614 def connect(self, *cargs, **cparams):
    615     # inherits the docstring from interfaces.Dialect.connect
--> 616     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#