我正在将一个表保存到SQL Server中,并需要它来替换同名的现有表。
df1.to_sql('customer',schema = r'Marketing\xyz',con = engine,index = False,if_exists = 'replace')
有效,但在架构中添加方括号会破坏它:
df1.to_sql('customer',schema = r'[Marketing\xyz]',if_exists = 'replace')
出现此错误:
Traceback (most recent call last):
File "<ipython-input-11-69ec99f37fd6>",line 1,in <module>
df1.to_sql('customer',schema = r'[abc\xyz]',if_exists = 'replace')
File "C:\Users\xyz\AppData\Local\Continuum\anaconda3\lib\site-packages\pandas\core\generic.py",line 2712,in to_sql
method=method,File "C:\Users\xyz\AppData\Local\Continuum\anaconda3\lib\site-packages\pandas\io\sql.py",line 518,line 1319,in to_sql
table.create()
File "C:\Users\xyz\AppData\Local\Continuum\anaconda3\lib\site-packages\pandas\io\sql.py",line 656,in create
self._execute_create()
File "C:\Users\xyz\AppData\Local\Continuum\anaconda3\lib\site-packages\pandas\io\sql.py",line 638,in _execute_create
self.table.create()
File "C:\Users\xyz\AppData\Local\Continuum\anaconda3\lib\site-packages\sqlalchemy\sql\schema.py",line 860,in create
bind._run_visitor(ddl.SchemaGenerator,self,checkfirst=checkfirst)
File "C:\Users\xyz\AppData\Local\Continuum\anaconda3\lib\site-packages\sqlalchemy\engine\base.py",line 2036,in _run_visitor
conn._run_visitor(visitorcallable,element,**kwargs)
File "C:\Users\xyz\AppData\Local\Continuum\anaconda3\lib\site-packages\sqlalchemy\engine\base.py",line 1607,in _run_visitor
visitorcallable(self.dialect,**kwargs).traverse_single(element)
File "C:\Users\xyz\AppData\Local\Continuum\anaconda3\lib\site-packages\sqlalchemy\sql\visitors.py",line 132,in traverse_single
return meth(obj,**kw)
File "C:\Users\xyz\AppData\Local\Continuum\anaconda3\lib\site-packages\sqlalchemy\sql\ddl.py",line 826,in visit_table
include_foreign_key_constraints,File "C:\Users\xyz\AppData\Local\Continuum\anaconda3\lib\site-packages\sqlalchemy\engine\base.py",line 988,in execute
return meth(self,multiparams,params)
File "C:\Users\xyz\AppData\Local\Continuum\anaconda3\lib\site-packages\sqlalchemy\sql\ddl.py",line 72,in _execute_on_connection
return connection._execute_ddl(self,params)
File "C:\Users\xyz\AppData\Local\Continuum\anaconda3\lib\site-packages\sqlalchemy\engine\base.py",line 1050,in _execute_ddl
compiled,line 1248,in _execute_context
e,statement,parameters,cursor,context
File "C:\Users\xyz\AppData\Local\Continuum\anaconda3\lib\site-packages\sqlalchemy\engine\base.py",line 1466,in _handle_dbapi_exception
util.raise_from_cause(sqlalchemy_exception,exc_info)
File "C:\Users\xyz\AppData\Local\Continuum\anaconda3\lib\site-packages\sqlalchemy\util\compat.py",line 398,in raise_from_cause
reraise(type(exception),exception,tb=exc_tb,cause=cause)
File "C:\Users\xyz\AppData\Local\Continuum\anaconda3\lib\site-packages\sqlalchemy\util\compat.py",line 152,in reraise
raise value.with_traceback(tb)
File "C:\Users\xyz\AppData\Local\Continuum\anaconda3\lib\site-packages\sqlalchemy\engine\base.py",line 1244,in _execute_context
cursor,context
File "C:\Users\xyz\AppData\Local\Continuum\anaconda3\lib\site-packages\sqlalchemy\engine\default.py",line 552,in do_execute
cursor.execute(statement,parameters)
ProgrammingError: (pyodbc.ProgrammingError) ('42S01',"[42S01] [microsoft][ODBC Driver 13 for SQL Server][SQL Server]There is already an object named 'customer' in the database. (2714) (SQLExecDirectW)")
[SQL:
CREATE TABLE [Marketing\xyz].customer (
[Customer] not NULL
)
]
(Background on this error at: http://sqlalche.me/e/f405)
谁知道为什么在架构中添加方括号会导致熊猫无法自动为我删除表格?谢谢-
我正在使用
sys.version
Out[19]: '3.7.4 (default,Aug 9 2019,18:34:13) [MSC v.1915 64 bit (AMD64)]'
pd.__version__
Out[16]: '0.25.3'