SQLALCHEMY Вставьте заявление PRAGMA, имея дело с базой данных PostGRESQL ⇐ Python

[Anonymous]

Я пытаюсь перенести небольшую таблицу в локальной базе данных SQLite на удаленный сервер PostgreSQL, но я получаю сообщение об ошибке ошибки »(Psycopg2.errors.syntaxError) Синтаксис Ошибка на или рядом с« Pragma ». Это мой код: < /p>
from sqlalchemy import create_engine, select, insert
import sqlalchemy.orm as so
import psycopg2
import pandas as pd
from config import Config
import os
from dotenv import load_dotenv
from app.models import Grupo

# Creating engines
load_dotenv()
sqlite_engine = create_engine(Config.SQLALCHEMY_DATABASE_URI)
postgres_url = f"postgresql+psycopg2://{os.getenv('PGUSER')}:{os.getenv('PGPASSWORD')}@{os.getenv('PGHOST')}:{os.getenv('PGPORT')}/{os.getenv('PGDATABASE')}"
postgres_engine = create_engine(postgres_url)

# Reading table from SQLite
stmt = select(Grupo)
df_Grupo = pd.read_sql(sql=stmt, con=sqlite_engine)
# a small, non important change
df_Grupo['DT_COMPETENCIA'] = df_Grupo['DT_COMPETENCIA'].str[:4] + '-' + df_Grupo['DT_COMPETENCIA'].str[4:]

# Writing table on postgresql
with so.Session(postgres_engine) as pg_session:
try:
pg_session.execute(insert(Grupo), df_Grupo.to_dict(orient='records'))
pg_session.commit()
except Exception as e:
print(f"Erro na migração da tabela Grupo: \n{e}")
< /code>
Это выход: < /p>
Erro na migração da tabela Grupo:
(psycopg2.errors.SyntaxError) syntax error at or near "PRAGMA"
LINE 1: PRAGMA foreign_keys=ON
^

(Background on this error at: https://sqlalche.me/e/20/f405)
< /code>
это полная таблица: < /p>

CO_GRUPO NO_GRUPO DT_COMPETENCIA
0 01 Ações de promoção e prevenção em saúde 2024-06
1 02 Procedimentos com finalidade diagnóstica 2024-06
2 03 Procedimentos clínicos 2024-06
3 04 Procedimentos cirúrgicos 2024-06
4 05 Transplantes de orgãos, tecidos e células 2024-06
5 06 Medicamentos 2024-06
6 07 Órteses, próteses e materiais especiais 2024-06
7 08 Ações complementares da atenção à saúde 2024-06
8 09 Procedimentos para Ofertas de Cuidados Integrados 2024-06
< /code>
Это ошибка Traceback < /p>
Traceback (most recent call last):
File "/home/vado/python-projects/SUSano/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 146, in __init__
self._dbapi_connection = engine.raw_connection()
^^^^^^^^^^^^^^^^^^^^^^^
File "/home/vado/python-projects/SUSano/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 3300, in raw_connection
return self.pool.connect()
^^^^^^^^^^^^^^^^^^^
File "/home/vado/python-projects/SUSano/.venv/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 449, in connect
return _ConnectionFairy._checkout(self)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/vado/python-projects/SUSano/.venv/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 1263, in _checkout
fairy = _ConnectionRecord.checkout(pool)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/vado/python-projects/SUSano/.venv/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 712, in checkout
rec = pool._do_get()
^^^^^^^^^^^^^^
File "/home/vado/python-projects/SUSano/.venv/lib/python3.12/site-packages/sqlalchemy/pool/impl.py", line 179, in _do_get
with util.safe_reraise():
File "/home/vado/python-projects/SUSano/.venv/lib/python3.12/site-packages/sqlalchemy/util/langhelpers.py", line 146, in __exit__
raise exc_value.with_traceback(exc_tb)
File "/home/vado/python-projects/SUSano/.venv/lib/python3.12/site-packages/sqlalchemy/pool/impl.py", line 177, in _do_get
return self._create_connection()
^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/vado/python-projects/SUSano/.venv/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 390, in _create_connection
return _ConnectionRecord(self)
^^^^^^^^^^^^^^^^^^^^^^^
File "/home/vado/python-projects/SUSano/.venv/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 674, in __init__
self.__connect()
File "/home/vado/python-projects/SUSano/.venv/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 914, in __connect
)._exec_w_sync_on_first_run(self.dbapi_connection, self)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/vado/python-projects/SUSano/.venv/lib/python3.12/site-packages/sqlalchemy/event/attr.py", line 483, in _exec_w_sync_on_first_run
self(*args, **kw)
File "/home/vado/python-projects/SUSano/.venv/lib/python3.12/site-packages/sqlalchemy/event/attr.py", line 495, in __call__
fn(*args, **kw)
File "/home/vado/python-projects/SUSano/src/app/__init__.py", line 20, in set_sqlite_pragma
cursor.execute("PRAGMA foreign_keys=ON")
psycopg2.errors.SyntaxError: syntax error at or near "PRAGMA"
LINE 1: PRAGMA foreign_keys=ON
^

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

Traceback (most recent call last):
File "/tmp/ipykernel_189447/3532388728.py", line 3, in
pg_session.execute(insert(Grupo), df_Grupo.to_dict(orient='records'))
File "/home/vado/python-projects/SUSano/.venv/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 2351, in execute
return self._execute_internal(
^^^^^^^^^^^^^^^^^^^^^^^
File "/home/vado/python-projects/SUSano/.venv/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 2226, in _execute_internal
conn = self._connection_for_bind(bind)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/vado/python-projects/SUSano/.venv/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 2095, in _connection_for_bind
return trans._connection_for_bind(engine, execution_options)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "", line 2, in _connection_for_bind
File "/home/vado/python-projects/SUSano/.venv/lib/python3.12/site-packages/sqlalchemy/orm/state_changes.py", line 139, in _go
ret_value = fn(self, *arg, **kw)
^^^^^^^^^^^^^^^^^^^^
File "/home/vado/python-projects/SUSano/.venv/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 1189, in _connection_for_bind
conn = bind.connect()
^^^^^^^^^^^^^^
File "/home/vado/python-projects/SUSano/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 3276, in connect
return self._connection_cls(self)
^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/vado/python-projects/SUSano/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 148, in __init__
Connection._handle_dbapi_exception_noconnection(
File "/home/vado/python-projects/SUSano/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2440, in _handle_dbapi_exception_noconnection
raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
File "/home/vado/python-projects/SUSano/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 146, in __init__
self._dbapi_connection = engine.raw_connection()
^^^^^^^^^^^^^^^^^^^^^^^
File "/home/vado/python-projects/SUSano/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 3300, in raw_connection
return self.pool.connect()
^^^^^^^^^^^^^^^^^^^
File "/home/vado/python-projects/SUSano/.venv/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 449, in connect
return _ConnectionFairy._checkout(self)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/vado/python-projects/SUSano/.venv/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 1263, in _checkout
fairy = _ConnectionRecord.checkout(pool)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/vado/python-projects/SUSano/.venv/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 712, in checkout
rec = pool._do_get()
^^^^^^^^^^^^^^
File "/home/vado/python-projects/SUSano/.venv/lib/python3.12/site-packages/sqlalchemy/pool/impl.py", line 179, in _do_get
with util.safe_reraise():
File "/home/vado/python-projects/SUSano/.venv/lib/python3.12/site-packages/sqlalchemy/util/langhelpers.py", line 146, in __exit__
raise exc_value.with_traceback(exc_tb)
File "/home/vado/python-projects/SUSano/.venv/lib/python3.12/site-packages/sqlalchemy/pool/impl.py", line 177, in _do_get
return self._create_connection()
^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/vado/python-projects/SUSano/.venv/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 390, in _create_connection
return _ConnectionRecord(self)
^^^^^^^^^^^^^^^^^^^^^^^
File "/home/vado/python-projects/SUSano/.venv/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 674, in __init__
self.__connect()
File "/home/vado/python-projects/SUSano/.venv/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 914, in __connect
)._exec_w_sync_on_first_run(self.dbapi_connection, self)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/vado/python-projects/SUSano/.venv/lib/python3.12/site-packages/sqlalchemy/event/attr.py", line 483, in _exec_w_sync_on_first_run
self(*args, **kw)
File "/home/vado/python-projects/SUSano/.venv/lib/python3.12/site-packages/sqlalchemy/event/attr.py", line 495, in __call__
fn(*args, **kw)
File "/home/vado/python-projects/SUSano/src/app/__init__.py", line 20, in set_sqlite_pragma
cursor.execute("PRAGMA foreign_keys=ON")
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.SyntaxError) syntax error at or near "PRAGMA"
LINE 1: PRAGMA foreign_keys=ON
^

(Background on this error at: https://sqlalche.me/e/20/f405)


Подробнее здесь: https://stackoverflow.com/questions/796 ... l-database