python - Python - Pandas - 我想连接到 Oracle 数据库，将数据放在数据帧上，然后将数据帧发送到 SQL Server
问题描述
我正在尝试连接到 Oracle 基地，将数据扔到 Pandas 数据框中，然后将其发送到 SQL Server Express localhost。我可以连接到 Oracle 并发送到数据帧，但我不能发送到 SQL Server。出现以下错误，您能帮帮我吗？
这部分有效。
import pandas as pd`enter code here`
import cx_Oracle
from sqlalchemy import create_engine
engine = create_engine('oracle://user:password@(DESCRIPTION=(CID=GTU_APP)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=server)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME = LUPA)))')
sql = """
select
substr(af.SK_COD_DATA_TRANSACAO,1,6) MesAno,
emp.nt_cod_empresa,
uneg.nt_cod_unidade_negocio Uneg,
uneg.unidade_negocio UnidadeNegocio,
l.nt_cod_estabelecimento codLocal,
l.estabelecimento Local,
cc.nt_cod_centro_custo codCCusto,
cc.centro_custo CCusto,
cc.nt_cod_plano_centro_custo,
ct.nt_cod_conta_contabil codigoConta,
ct.conta_contabil Conta,
ct.nt_cod_plano_conta_contabil,
sum(case when af.nt_cod_natureza_operacao = 'CR' then 1 else -1 end * af.val_apropriacao) apropriacao,
sum(case when af.nt_cod_natureza_operacao = 'CR' then 1 else -1 end * af.val_lancamento) lancamento
, cur.curso
, sum(case when tl.grupo_lancamento like 'Mensalidade%' and mc.fluxo_conta_corrente = 'ENT' and af.val_apropriacao <>0
then af.val_apropriacao/af.val_apropriacao else
case when tl.grupo_lancamento like 'Mensalidade%' and mc.fluxo_conta_corrente = 'SAI' and af.val_apropriacao <>0
then (af.val_apropriacao/af.val_apropriacao) *-1 end end) hc
, si.status_integracao
from dw.ft_apropriacao_financeiro af
join dw.dm_conta_contabil ct on af.sk_cod_conta_contabil = ct.sk_cod_conta_contabil
join dw.dm_centro_custo cc on af.sk_cod_centro_custo = cc.sk_cod_centro_custo
join dw.dm_unidade_negocio uneg on af.sk_cod_unidade_negocio = uneg.sk_cod_unidade_negocio
join dw.dm_estabelecimento l on af.sk_cod_estabelecimento = l.sk_cod_estabelecimento
join dw.dm_empresa emp on af.sk_cod_empresa = emp.sk_cod_empresa
join dw.dm_curso cur on cur.sk_cod_curso = af.sk_cod_curso
join dw.dm_movimento_contabil mc on af.sk_cod_movimento_contabil = mc.sk_cod_movimento_contabil
join dw.dm_status_titulo st on af.sk_cod_status_titulo = st.sk_cod_status_titulo
join dw.dm_tipo_lancamento tl on af.sk_cod_tipo_lancamento = tl.sk_cod_tipo_lancamento
join dw.dm_aluno aluno on aluno.sk_cod_aluno = af.sk_cod_aluno
join dw.dm_status_integracao si on af.sk_cod_status_integracao = si.sk_cod_status_integracao
where
af.SK_COD_DATA_TRANSACAO between '20200101' and '20200131'
and ct.nt_cod_conta_contabil in
(3110101, 3110102, 3110103, 3110104, 3110105,
3110201, 3110202,
3110301, 3110302, 3110303, 3110304, 3110310, 3110311,
3120101, 3120102, 3120103, 3120104, 3120105,
3120201, 3120102,
3120307, 3120308)
and si.status_integracao in ('Pendente','Integrado')
group by
substr(af.SK_COD_DATA_TRANSACAO,1,6) ,
emp.nt_cod_empresa,
uneg.nt_cod_unidade_negocio ,
uneg.unidade_negocio ,
l.nt_cod_estabelecimento ,
l.estabelecimento ,
cc.nt_cod_centro_custo ,
cc.centro_custo ,
cc.nt_cod_plano_centro_custo,
ct.nt_cod_conta_contabil ,
ct.conta_contabil ,
ct.nt_cod_plano_conta_contabil
, cur.curso
, si.status_integracao
"""
df = pd.read_sql(sql, engine)
这行不通。
import pyodbc
import pandas as result
from sqlalchemy import create_engine
engine2 = create_engine("mssql+pyodbc://localhost/Receita")
result = engine2.execute("select * from teste")
df.to_sql(con=engine2, name='RECEITA', schema='SCHEMA', index=False, if_exists='replace', chunksize=500)
发生此错误。
C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\connectors\pyodbc.py:79: SAWarning: No driver name specified; this is expected by PyODBC when using DSN-less connections
"No driver name specified; "
---------------------------------------------------------------------------
InterfaceError Traceback (most recent call last)
C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\engine\base.py in _wrap_pool_connect(self, fn, connection)
2274 try:
-> 2275 return fn()
2276 except dialect.dbapi.Error as e:
C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\pool\base.py in connect(self)
362 if not self._use_threadlocal:
--> 363 return _ConnectionFairy._checkout(self)
364
C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\pool\base.py in _checkout(cls, pool, threadconns, fairy)
759 if not fairy:
--> 760 fairy = _ConnectionRecord.checkout(pool)
761
C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\pool\base.py in checkout(cls, pool)
491 def checkout(cls, pool):
--> 492 rec = pool._do_get()
493 try:
C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\pool\impl.py in _do_get(self)
138 with util.safe_reraise():
--> 139 self._dec_overflow()
140 else:
C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\util\langhelpers.py in __exit__(self, type_, value, traceback)
67 if not self.warn_only:
---> 68 compat.reraise(exc_type, exc_value, exc_tb)
69 else:
C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\util\compat.py in reraise(tp, value, tb, cause)
152 raise value.with_traceback(tb)
--> 153 raise value
154
C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\pool\impl.py in _do_get(self)
135 try:
--> 136 return self._create_connection()
137 except:
C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\pool\base.py in _create_connection(self)
307
--> 308 return _ConnectionRecord(self)
309
C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\pool\base.py in __init__(self, pool, connect)
436 if connect:
--> 437 self.__connect(first_connect_check=True)
438 self.finalize_callback = deque()
C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\pool\base.py in __connect(self, first_connect_check)
638 self.starttime = time.time()
--> 639 connection = pool._invoke_creator(self)
640 pool.logger.debug("Created new connection %r", connection)
C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\engine\strategies.py in connect(connection_record)
113 return connection
--> 114 return dialect.connect(*cargs, **cparams)
115
C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\engine\default.py in connect(self, *cargs, **cparams)
480 def connect(self, *cargs, **cparams):
--> 481 return self.dbapi.connect(*cargs, **cparams)
482
InterfaceError: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Nome da fonte de dados não encontrado e nenhum driver padrão especificado (0) (SQLDriverConnect)')
The above exception was the direct cause of the following exception:
InterfaceError Traceback (most recent call last)
<ipython-input-3-4ae5388ed198> in <module>
5
6 engine2 = create_engine("mssql+pyodbc://localhost/Receita")
----> 7 result = engine2.execute("select * from teste")
8
9 df.to_sql(con=engine2, name='RECEITA', schema='SCHEMA', index=False, if_exists='replace', chunksize=500)
C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\engine\base.py in execute(self, statement, *multiparams, **params)
2176 """
2177
-> 2178 connection = self._contextual_connect(close_with_result=True)
2179 return connection.execute(statement, *multiparams, **params)
2180
C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\engine\base.py in _contextual_connect(self, close_with_result, **kwargs)
2237 return self._connection_cls(
2238 self,
-> 2239 self._wrap_pool_connect(self.pool.connect, None),
2240 close_with_result=close_with_result,
2241 **kwargs
C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\engine\base.py in _wrap_pool_connect(self, fn, connection)
2277 if connection is None:
2278 Connection._handle_dbapi_exception_noconnection(
-> 2279 e, dialect, self
2280 )
2281 else:
C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\engine\base.py in _handle_dbapi_exception_noconnection(cls, e, dialect, engine)
1542 util.raise_from_cause(newraise, exc_info)
1543 elif should_wrap:
-> 1544 util.raise_from_cause(sqlalchemy_exception, exc_info)
1545 else:
1546 util.reraise(*exc_info)
C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\util\compat.py in raise_from_cause(exception, exc_info)
396 exc_type, exc_value, exc_tb = exc_info
397 cause = exc_value if exc_value is not exception else None
--> 398 reraise(type(exception), exception, tb=exc_tb, cause=cause)
399
400
C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\util\compat.py in reraise(tp, value, tb, cause)
150 value.__cause__ = cause
151 if value.__traceback__ is not tb:
--> 152 raise value.with_traceback(tb)
153 raise value
154
C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\engine\base.py in _wrap_pool_connect(self, fn, connection)
2273 dialect = self.dialect
2274 try:
-> 2275 return fn()
2276 except dialect.dbapi.Error as e:
2277 if connection is None:
C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\pool\base.py in connect(self)
361 """
362 if not self._use_threadlocal:
--> 363 return _ConnectionFairy._checkout(self)
364
365 try:
C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\pool\base.py in _checkout(cls, pool, threadconns, fairy)
758 def _checkout(cls, pool, threadconns=None, fairy=None):
759 if not fairy:
--> 760 fairy = _ConnectionRecord.checkout(pool)
761
762 fairy._pool = pool
C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\pool\base.py in checkout(cls, pool)
490 @classmethod
491 def checkout(cls, pool):
--> 492 rec = pool._do_get()
493 try:
494 dbapi_connection = rec.get_connection()
C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\pool\impl.py in _do_get(self)
137 except:
138 with util.safe_reraise():
--> 139 self._dec_overflow()
140 else:
141 return self._do_get()
C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\util\langhelpers.py in __exit__(self, type_, value, traceback)
66 self._exc_info = None # remove potential circular references
67 if not self.warn_only:
---> 68 compat.reraise(exc_type, exc_value, exc_tb)
69 else:
70 if not compat.py3k and self._exc_info and self._exc_info[1]:
C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\util\compat.py in reraise(tp, value, tb, cause)
151 if value.__traceback__ is not tb:
152 raise value.with_traceback(tb)
--> 153 raise value
154
155 def u(s):
C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\pool\impl.py in _do_get(self)
134 if self._inc_overflow():
135 try:
--> 136 return self._create_connection()
137 except:
138 with util.safe_reraise():
C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\pool\base.py in _create_connection(self)
306 """Called by subclasses to create a new ConnectionRecord."""
307
--> 308 return _ConnectionRecord(self)
309
310 def _invalidate(self, connection, exception=None, _checkin=True):
C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\pool\base.py in __init__(self, pool, connect)
435 self.__pool = pool
436 if connect:
--> 437 self.__connect(first_connect_check=True)
438 self.finalize_callback = deque()
439
C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\pool\base.py in __connect(self, first_connect_check)
637 try:
638 self.starttime = time.time()
--> 639 connection = pool._invoke_creator(self)
640 pool.logger.debug("Created new connection %r", connection)
641 self.connection = connection
C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\engine\strategies.py in connect(connection_record)
112 if connection is not None:
113 return connection
--> 114 return dialect.connect(*cargs, **cparams)
115
116 creator = pop_kwarg("creator", connect)
C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\engine\default.py in connect(self, *cargs, **cparams)
479
480 def connect(self, *cargs, **cparams):
--> 481 return self.dbapi.connect(*cargs, **cparams)
482
483 def create_connect_args(self, url):
InterfaceError: (pyodbc.InterfaceError) ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Nome da fonte de dados não encontrado e nenhum driver padrão especificado (0) (SQLDriverConnect)')
(Background on this error at: http://sqlalche.me/e/rvf5)
解决方案
推荐阅读
- databricks - 将 DataBricks API 2.0 与令牌一起使用
- bash - run a bash script located in public folder github actions
- sql - WITH 语句后的 PL/SQL FOR 循环
- python - 我的模型中有一个定义的主键，但 Django 抛出多个主键错误
- javascript - Vuejs 应用程序 api 登录调用响应出错
- python - 在股票市场数据中添加缺失的日期行以保持 pandas 数据框的连续性
- r - 使用 doParallel 和 foreach 的函数出错
- amazon-s3 - 边缘 URL 与子域
- parse-platform - 带有 express 应用程序的 Parse 服务器项目的概念是什么？
- javascript - 如何使用角度 BrowserAnimationsModule 为网格框更改设置动画？