如何将dataframe加载到PostgreSQL 14

Question

我试图将数据框加载到PostgreSQL，尤其是对服务器PostgreSQL 14（我有2个服务器PostgreSQL 9.3在端口5434上运行，另一个是postgreSQL在端口5433上运行的postgresql 14）使用此命令

from sqlalchemy import create_engine
engine = create_engine('postgresql://postgres:password@localhost:5433/MYDATABASE')
df.to_sql('My_Table', engine)

： ，我也尝试了更多的方式，但它总是相同的错误，我想它与我正在使用的两个服务器有关：

---------------------------------------------------------------------------
OperationalError                          Traceback (most recent call last)
File ~\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\engine\base.py:3280, in Engine._wrap_pool_connect(self, fn, connection)
   3279 try:
-> 3280     return fn()
   3281 except dialect.dbapi.Error as e:

File ~\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\pool\base.py:310, in Pool.connect(self)
    303 """Return a DBAPI connection from the pool.
    304 
    305 The connection is instrumented such that when its
   (...)
    308 
    309 """
--> 310 return _ConnectionFairy._checkout(self)

File ~\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\pool\base.py:868, in _ConnectionFairy._checkout(cls, pool, threadconns, fairy)
    867 if not fairy:
--> 868     fairy = _ConnectionRecord.checkout(pool)
    870     fairy._pool = pool

File ~\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\pool\base.py:476, in _ConnectionRecord.checkout(cls, pool)
    474 @classmethod
    475 def checkout(cls, pool):
--> 476     rec = pool._do_get()
    477     try:

File ~\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\pool\impl.py:145, in QueuePool._do_get(self)
    144 except:
--> 145     with util.safe_reraise():
    146         self._dec_overflow()

File ~\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\util\langhelpers.py:70, in safe_reraise.__exit__(self, type_, value, traceback)
     69     if not self.warn_only:
---> 70         compat.raise_(
     71             exc_value,
     72             with_traceback=exc_tb,
     73         )
     74 else:

File ~\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\util\compat.py:207, in raise_(***failed resolving arguments***)
    206 try:
--> 207     raise exception
    208 finally:
    209     # credit to
    210     # https://cosmicpercolator.com/2016/01/13/exception-leaks-in-python-2-and-3/
    211     # as the __traceback__ object creates a cycle

File ~\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\pool\impl.py:143, in QueuePool._do_get(self)
    142 try:
--> 143     return self._create_connection()
    144 except:

File ~\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\pool\base.py:256, in Pool._create_connection(self)
    254 """Called by subclasses to create a new ConnectionRecord."""
--> 256 return _ConnectionRecord(self)

File ~\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\pool\base.py:371, in _ConnectionRecord.__init__(self, pool, connect)
    370 if connect:
--> 371     self.__connect()
    372 self.finalize_callback = deque()

File ~\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\pool\base.py:665, in _ConnectionRecord.__connect(self)
    664 except Exception as e:
--> 665     with util.safe_reraise():
    666         pool.logger.debug("Error on connect(): %s", e)

File ~\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\util\langhelpers.py:70, in safe_reraise.__exit__(self, type_, value, traceback)
     69     if not self.warn_only:
---> 70         compat.raise_(
     71             exc_value,
     72             with_traceback=exc_tb,
     73         )
     74 else:

File ~\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\util\compat.py:207, in raise_(***failed resolving arguments***)
    206 try:
--> 207     raise exception
    208 finally:
    209     # credit to
    210     # https://cosmicpercolator.com/2016/01/13/exception-leaks-in-python-2-and-3/
    211     # as the __traceback__ object creates a cycle

File ~\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\pool\base.py:661, in _ConnectionRecord.__connect(self)
    660 self.starttime = time.time()
--> 661 self.dbapi_connection = connection = pool._invoke_creator(self)
    662 pool.logger.debug("Created new connection %r", connection)

File ~\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\engine\create.py:590, in create_engine.<locals>.connect(connection_record)
    589             return connection
--> 590 return dialect.connect(*cargs, **cparams)

File ~\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\engine\default.py:597, in DefaultDialect.connect(self, *cargs, **cparams)
    595 def connect(self, *cargs, **cparams):
    596     # inherits the docstring from interfaces.Dialect.connect
--> 597     return self.dbapi.connect(*cargs, **cparams)

File ~\AppData\Local\Programs\Python\Python310\lib\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: 

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

OperationalError                          Traceback (most recent call last)
Input In [105], in <cell line: 3>()
      1 from sqlalchemy import create_engine
      2 engine = create_engine('postgresql://postgres:password@localhost:5433/MYDATABASE')
----> 3 df.to_sql('My_Table', engine)

File ~\AppData\Local\Programs\Python\Python310\lib\site-packages\pandas\core\generic.py:2951, in NDFrame.to_sql(self, name, con, schema, if_exists, index, index_label, chunksize, dtype, method)
   2794 """
   2795 Write records stored in a DataFrame to a SQL database.
   2796 
   (...)
   2947 [(1,), (None,), (2,)]
   2948 """  # noqa:E501
   2949 from pandas.io import sql
-> 2951 return sql.to_sql(
   2952     self,
   2953     name,
   2954     con,
   2955     schema=schema,
   2956     if_exists=if_exists,
   2957     index=index,
   2958     index_label=index_label,
   2959     chunksize=chunksize,
   2960     dtype=dtype,
   2961     method=method,
   2962 )

File ~\AppData\Local\Programs\Python\Python310\lib\site-packages\pandas\io\sql.py:697, in to_sql(frame, name, con, schema, if_exists, index, index_label, chunksize, dtype, method, engine, **engine_kwargs)
    692 elif not isinstance(frame, DataFrame):
    693     raise NotImplementedError(
    694         "'frame' argument should be either a Series or a DataFrame"
    695     )
--> 697 return pandas_sql.to_sql(
    698     frame,
    699     name,
    700     if_exists=if_exists,
    701     index=index,
    702     index_label=index_label,
    703     schema=schema,
    704     chunksize=chunksize,
    705     dtype=dtype,
    706     method=method,
    707     engine=engine,
    708     **engine_kwargs,
    709 )

File ~\AppData\Local\Programs\Python\Python310\lib\site-packages\pandas\io\sql.py:1729, in SQLDatabase.to_sql(self, frame, name, if_exists, index, index_label, schema, chunksize, dtype, method, engine, **engine_kwargs)
   1679 """
   1680 Write records stored in a DataFrame to a SQL database.
   1681 
   (...)
   1725     Any additional kwargs are passed to the engine.
   1726 """
   1727 sql_engine = get_engine(engine)
-> 1729 table = self.prep_table(
   1730     frame=frame,
   1731     name=name,
   1732     if_exists=if_exists,
   1733     index=index,
   1734     index_label=index_label,
   1735     schema=schema,
   1736     dtype=dtype,
   1737 )
   1739 total_inserted = sql_engine.insert_records(
   1740     table=table,
   1741     con=self.connectable,
   (...)
   1748     **engine_kwargs,
   1749 )
   1751 self.check_case_sensitive(name=name, schema=schema)

File ~\AppData\Local\Programs\Python\Python310\lib\site-packages\pandas\io\sql.py:1628, in SQLDatabase.prep_table(self, frame, name, if_exists, index, index_label, schema, dtype)
   1616             raise ValueError(f"The type of {col} is not a SQLAlchemy type")
   1618 table = SQLTable(
   1619     name,
   1620     self,
   (...)
   1626     dtype=dtype,
   1627 )
-> 1628 table.create()
   1629 return table

File ~\AppData\Local\Programs\Python\Python310\lib\site-packages\pandas\io\sql.py:831, in SQLTable.create(self)
    830 def create(self):
--> 831     if self.exists():
    832         if self.if_exists == "fail":
    833             raise ValueError(f"Table '{self.name}' already exists.")

File ~\AppData\Local\Programs\Python\Python310\lib\site-packages\pandas\io\sql.py:815, in SQLTable.exists(self)
    814 def exists(self):
--> 815     return self.pd_sql.has_table(self.name, self.schema)

File ~\AppData\Local\Programs\Python\Python310\lib\site-packages\pandas\io\sql.py:1762, in SQLDatabase.has_table(self, name, schema)
   1759 if _gt14():
   1760     from sqlalchemy import inspect
-> 1762     insp = inspect(self.connectable)
   1763     return insp.has_table(name, schema or self.meta.schema)
   1764 else:

File ~\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\inspection.py:64, in inspect(subject, raiseerr)
     62 if reg is True:
     63     return subject
---> 64 ret = reg(subject)
     65 if ret is not None:
     66     break

File ~\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\engine\reflection.py:182, in Inspector._engine_insp(bind)
    180 @inspection._inspects(Engine)
    181 def _engine_insp(bind):
--> 182     return Inspector._construct(Inspector._init_engine, bind)

File ~\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\engine\reflection.py:117, in Inspector._construct(cls, init, bind)
    114     cls = bind.dialect.inspector
    116 self = cls.__new__(cls)
--> 117 init(self, bind)
    118 return self

File ~\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\engine\reflection.py:128, in Inspector._init_engine(self, engine)
    126 def _init_engine(self, engine):
    127     self.bind = self.engine = engine
--> 128     engine.connect().close()
    129     self._op_context_requires_connect = True
    130     self.dialect = self.engine.dialect

File ~\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\engine\base.py:3234, in Engine.connect(self, close_with_result)
   3219 def connect(self, close_with_result=False):
   3220     """Return a new :class:`_engine.Connection` object.
   3221 
   3222     The :class:`_engine.Connection` object is a facade that uses a DBAPI
   (...)
   3231 
   3232     """
-> 3234     return self._connection_cls(self, close_with_result=close_with_result)

File ~\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\engine\base.py:96, in Connection.__init__(self, engine, connection, close_with_result, _branch_from, _execution_options, _dispatch, _has_events, _allow_revalidate)
     91     self._has_events = _branch_from._has_events
     92 else:
     93     self._dbapi_connection = (
     94         connection
     95         if connection is not None
---> 96         else engine.raw_connection()
     97     )
     99     self._transaction = self._nested_transaction = None
    100     self.__savepoint_seq = 0

File ~\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\engine\base.py:3313, in Engine.raw_connection(self, _connection)
   3291 def raw_connection(self, _connection=None):
   3292     """Return a "raw" DBAPI connection from the connection pool.
   3293 
   3294     The returned object is a proxied version of the DBAPI
   (...)
   3311 
   3312     """
-> 3313     return self._wrap_pool_connect(self.pool.connect, _connection)

File ~\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\engine\base.py:3283, in Engine._wrap_pool_connect(self, fn, connection)
   3281 except dialect.dbapi.Error as e:
   3282     if connection is None:
-> 3283         Connection._handle_dbapi_exception_noconnection(
   3284             e, dialect, self
   3285         )
   3286     else:
   3287         util.raise_(
   3288             sys.exc_info()[1], with_traceback=sys.exc_info()[2]
   3289         )

File ~\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\engine\base.py:2117, in Connection._handle_dbapi_exception_noconnection(cls, e, dialect, engine)
   2115     util.raise_(newraise, with_traceback=exc_info[2], from_=e)
   2116 elif should_wrap:
-> 2117     util.raise_(
   2118         sqlalchemy_exception, with_traceback=exc_info[2], from_=e
   2119     )
   2120 else:
   2121     util.raise_(exc_info[1], with_traceback=exc_info[2])

File ~\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\util\compat.py:207, in raise_(***failed resolving arguments***)
    204     exception.__cause__ = replace_context
    206 try:
--> 207     raise exception
    208 finally:
    209     # credit to
    210     # https://cosmicpercolator.com/2016/01/13/exception-leaks-in-python-2-and-3/
    211     # as the __traceback__ object creates a cycle
    212     del exception, replace_context, from_, with_traceback

File ~\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\engine\base.py:3280, in Engine._wrap_pool_connect(self, fn, connection)
   3278 dialect = self.dialect
   3279 try:
-> 3280     return fn()
   3281 except dialect.dbapi.Error as e:
   3282     if connection is None:

File ~\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\pool\base.py:310, in Pool.connect(self)
    302 def connect(self):
    303     """Return a DBAPI connection from the pool.
    304 
    305     The connection is instrumented such that when its
   (...)
    308 
    309     """
--> 310     return _ConnectionFairy._checkout(self)

File ~\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\pool\base.py:868, in _ConnectionFairy._checkout(cls, pool, threadconns, fairy)
    865 @classmethod
    866 def _checkout(cls, pool, threadconns=None, fairy=None):
    867     if not fairy:
--> 868         fairy = _ConnectionRecord.checkout(pool)
    870         fairy._pool = pool
    871         fairy._counter = 0

File ~\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\pool\base.py:476, in _ConnectionRecord.checkout(cls, pool)
    474 @classmethod
    475 def checkout(cls, pool):
--> 476     rec = pool._do_get()
    477     try:
    478         dbapi_connection = rec.get_connection()

File ~\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\pool\impl.py:145, in QueuePool._do_get(self)
    143         return self._create_connection()
    144     except:
--> 145         with util.safe_reraise():
    146             self._dec_overflow()
    147 else:

File ~\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\util\langhelpers.py:70, in safe_reraise.__exit__(self, type_, value, traceback)
     68     self._exc_info = None  # remove potential circular references
     69     if not self.warn_only:
---> 70         compat.raise_(
     71             exc_value,
     72             with_traceback=exc_tb,
     73         )
     74 else:
     75     if not compat.py3k and self._exc_info and self._exc_info[1]:
     76         # emulate Py3K's behavior of telling us when an exception
     77         # occurs in an exception handler.

File ~\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\util\compat.py:207, in raise_(***failed resolving arguments***)
    204     exception.__cause__ = replace_context
    206 try:
--> 207     raise exception
    208 finally:
    209     # credit to
    210     # https://cosmicpercolator.com/2016/01/13/exception-leaks-in-python-2-and-3/
    211     # as the __traceback__ object creates a cycle
    212     del exception, replace_context, from_, with_traceback

File ~\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\pool\impl.py:143, in QueuePool._do_get(self)
    141 if self._inc_overflow():
    142     try:
--> 143         return self._create_connection()
    144     except:
    145         with util.safe_reraise():

File ~\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\pool\base.py:256, in Pool._create_connection(self)
    253 def _create_connection(self):
    254     """Called by subclasses to create a new ConnectionRecord."""
--> 256     return _ConnectionRecord(self)

File ~\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\pool\base.py:371, in _ConnectionRecord.__init__(self, pool, connect)
    369 self.__pool = pool
    370 if connect:
--> 371     self.__connect()
    372 self.finalize_callback = deque()

File ~\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\pool\base.py:665, in _ConnectionRecord.__connect(self)
    663     self.fresh = True
    664 except Exception as e:
--> 665     with util.safe_reraise():
    666         pool.logger.debug("Error on connect(): %s", e)
    667 else:
    668     # in SQLAlchemy 1.4 the first_connect event is not used by
    669     # the engine, so this will usually not be set

File ~\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\util\langhelpers.py:70, in safe_reraise.__exit__(self, type_, value, traceback)
     68     self._exc_info = None  # remove potential circular references
     69     if not self.warn_only:
---> 70         compat.raise_(
     71             exc_value,
     72             with_traceback=exc_tb,
     73         )
     74 else:
     75     if not compat.py3k and self._exc_info and self._exc_info[1]:
     76         # emulate Py3K's behavior of telling us when an exception
     77         # occurs in an exception handler.

File ~\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\util\compat.py:207, in raise_(***failed resolving arguments***)
    204     exception.__cause__ = replace_context
    206 try:
--> 207     raise exception
    208 finally:
    209     # credit to
    210     # https://cosmicpercolator.com/2016/01/13/exception-leaks-in-python-2-and-3/
    211     # as the __traceback__ object creates a cycle
    212     del exception, replace_context, from_, with_traceback

File ~\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\pool\base.py:661, in _ConnectionRecord.__connect(self)
    659 try:
    660     self.starttime = time.time()
--> 661     self.dbapi_connection = connection = pool._invoke_creator(self)
    662     pool.logger.debug("Created new connection %r", connection)
    663     self.fresh = True

File ~\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\engine\create.py:590, in create_engine.<locals>.connect(connection_record)
    588         if connection is not None:
    589             return connection
--> 590 return dialect.connect(*cargs, **cparams)

File ~\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\engine\default.py:597, in DefaultDialect.connect(self, *cargs, **cparams)
    595 def connect(self, *cargs, **cparams):
    596     # inherits the docstring from interfaces.Dialect.connect
--> 597     return self.dbapi.connect(*cargs, **cparams)

File ~\AppData\Local\Programs\Python\Python310\lib\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) 
(Background on this error at: https://sqlalche.me/e/14/e3q8)