任何人都知道为什么我在尝试将数据帧加载到 sybase 表时会收到此错误?[sql炼金术] [英] Anyone know why I am getting this error when trying to load dataframe to sybase table? [sql alchemy]
问题描述
我正在尝试将 Pandas 数据帧附加到已创建的表中，但我不断收到错误消息.
我正确连接到服务器.服务器内部有很多数据库，然后这个表在db_STAFF数据库中.最初，我在做 df.to_sql(db_STAFF.dbo.JUNESALES) 但我意识到我应该在 connString 中引用它.我尝试了 dbo.JUNESALES 以及 JUNESALES.下面错误中的表名根据我所说的表(dbo.JUNESALES 与 JUNESALES)而变化，但实际错误保持不变.
请参阅下面的代码和错误，减去我确实包含的导入语句.
df = pd.DataFrame(lists_data)connString = "DRIVER={Adaptive Server Enterprise};SERVER=XXXX,DATABASE = 'db_STAFF'...."conn_url = quote_plus(connString)new_connection = "sybase+pyodbc:///?odbc_connect={}".format(conn_url)引擎 = create_engine(new_connection)df.to_sql('[dbo].[JUNESALES]', con=engine, if_exists = 'append', index = False) #我也试过不带括号的，我读到带括号它对某人有用engine.execute("SELECT * FROM dbo.JUNESALES").fetchall()cursor.execute(语句，参数)我在 df.to_sql 行上收到此错误
sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42000', "[42000] [Sybase][ODBC Driver][Adaptive Server Enterprise] '(' 附近的语法不正确.\n (102)(SQLExecDirectW)") [SQL: '\nCREATE TABLE "[dbo].[JUNESALES]" (\n\t"0" BIGINT NULL，\n\t"1" BIGINT NULL，\n\t"2" FLOATNULL、\n\t"3" TEXT NULL、\n\t"4" BIT NULL、\n\t"5" BIT NULL、\n\t"6" FLOAT NULL、\n\t"7" FLOATNULL，\n\t"8" FLOAT NULL，\n\t"9" FLOAT NULL，\n\t"10" FLOAT NULL，\n\t"11" BIGINT NULL，\n\tCHECK ("4"IN (0, 1)), \n\tCHECK ("5" IN (0, 1))\n)\n\n'](此错误的背景:http://sqlalche.me/e/f405)注意:我是 Sybase ASE DBA；我不使用 python/pandas/sqlalchemy/etc；所以虽然我可以告诉你为什么 ASE 会产生一个错误，甚至向你展示一种正确格式化 create table 命令的方法......我不知道如何告诉你的应用程序如何(重新)编码create table 命令(假设这是您无法直接控制的内容).
错误消息告诉我们 create table 命令如下所示:
创建表 "[dbo].[JUNESALES]" ("0" BIGINT NULL,"1" BIGINT NULL,2"浮动空，3"文本空，4"位空，5"位空，6"浮动空，7"浮动空，8"浮动空，9"浮动空，10"浮动空，"11" BIGINT NULL,检查 ("4" IN (0, 1)),检查 ("5" IN (0, 1)))嗯，从哪里开始......
如果您要将其剪切-粘贴到 ASE 会话中(例如，通过 isql 命令行工具)，您将得到相同的错误:
消息 102，级别 15，状态 181:服务器ASE200"，第 2 行:'(' 附近的语法不正确.该命令似乎使用双引号试图转义非标准标识符.这样做的一个问题是，默认情况下，ASE 不会将双引号识别为非标准标识符的转义字符.要解决这个问题，您需要启用 quoted_identifier，例如:
设置quoted_identifier on创建表...……剪……将quoted_identifier 设置为关闭——或者如果您要继续使用双引号来指定非标准标识符，请保留打开"去虽然这会让您克服Msg 102(语法)错误，但您现在会遇到一些新错误:
消息 2718，级别 16，状态 1:服务器ASE200"，第 2 行:列或参数 #5:-- 不能在 BIT 类型的列上指定 Null 值.消息 2718，级别 16，状态 1:服务器ASE200"，第 2 行:列或参数 #6:-- 不能在 BIT 类型的列上指定 Null 值.要修复这些错误，您需要将 BIT 列指定为 NOT NULL 或将数据类型更改为 BIT 以外的其他内容(例如，tinyint? 尽管您现在可能需要添加一些应用程序代码或 check 约束以将合法值限制为 0/1 ...??):
设置quoted_identifier on创建表...……剪……4"位不为空，5"位不为空，……剪……设置quoted_identifier关闭去此时应该创建表(即没有错误)但是......您还没有走出困境.
如果您运行 sp_help，您会看到您的表格如下所示:
sp_help去名称所有者 Object_type--------------------------- ----- ------------……剪……[dbo].[JUNESALES] dbo 用户表……剪……这里的问题(当然?)是你用一对双引号将所有者和表包裹起来；并且您尝试使用 2 种不同的方法来处理非标准标识符……双引号……方括号，这无济于事；这里的主要问题是双引号告诉 ASE，方括号实际上是名为 [dbo].[JUNESALES] 的单个标识符的一部分；还要注意句点 (.) 也被视为单个标识符的一部分(而不是所有者和表名之间的分隔符).
如果您尝试通过在 [dbo] 和 [JUNESALES] 周围放置双引号来修复他的问题，则会收到以下错误消息:
设置quoted_identifier on创建表[dbo]".[JUNESALES]"……剪……去消息 2734，级别 16，状态 1:服务器ASE200"，第 2 行:sysusers 中不存在用户名 [dbo].！！！注意方括号被视为用户名的一部分！！！好的，我们可以通过从 [dbo] 中删除方括号来解决这个问题，但是如果您不为表名做同样的事情......create table 命令成功但括号成为表名的一部分(而不是作为分隔符)，例如:
设置quoted_identifier on创建表dbo".[JUNESALES]"……剪……去sp_help去名称所有者 Object_type--------------------------- ----- ------------……剪……[JUNESALES] dbo 用户表……剪……ASE 支持使用双引号作为非标准标识符的分隔符...如果您首先发出 setquoted_identifier on.
ASE 还支持使用方括号作为非标准标识符的分隔符......并且无需发出 setquoted_identier on 命令.>
我建议您弄清楚如何仅使用一种方法来分隔您的非标准标识符(方括号更简洁一些，不需要发出 setquoted_identifier on，并允许您使用用于分隔文本/字符数据的双引号).
setquoted_identifier off——如果已经设置为off"，则可选创建表 [dbo].[JUNESALES] ([0] BIGINT NULL,[1] BIGINT NULL，[2] 浮动空，[3] 文本空，[4] 位不为空，[5] 位不为空，[6] 浮动空，[7] 浮动空，[8] 浮动空，[9] 浮动空，[10] 浮动空，[11] BIGINT NULL，检查 ([4] 输入 (0, 1))，检查 ([5] 输入 (0, 1)))去sp_help去名称所有者 Object_type--------------------------- ----- ------------……剪……JUNESALES dbo 用户表……剪……当然，dbo 和 JUNESALES 周围的分隔符是不需要的(即，这些是有效的标识符)，但如果您使用方括号您希望(例如，作为处理所有分隔符(无论是标准的还是非标准的)的标准编码方法).
注意:以上代码片段是针对 ASE 15.7 (SP138) 数据服务器执行的.
I am trying to send append a pandas dataframe to an already created table, and I keep getting an error.
I connected correctly to the server. Within the server, there are many databases, and then this table is within the db_STAFF database. Initially, I was doing df.to_sql(db_STAFF.dbo.JUNESALES) but I realized I should be referencing that in connString. I tried dbo.JUNESALES as well as just JUNESALES. The table name within the error below changes based on what I call the table (dbo.JUNESALES versus JUNESALES), but the actual error remains the same.
See code and error below, minus import statements which I did include.
df = pd.DataFrame(lists_data)
connString = "DRIVER={Adaptive Server Enterprise};SERVER=XXXX,DATABASE = 'db_STAFF'...."
conn_url = quote_plus(connString)
new_connection = "sybase+pyodbc:///?odbc_connect={}".format(conn_url)
engine = create_engine(new_connection)
df.to_sql('[dbo].[JUNESALES]', con=engine, if_exists = 'append', index = False) #I also tried this without the brackets, I read that with brackets it worked for someone
engine.execute("SELECT * FROM dbo.JUNESALES ").fetchall()
cursor.execute(statement, parameters)
I am getting this error on the df.to_sql line
sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42000', "[42000] [Sybase][ODBC Driver][Adaptive Server Enterprise] Incorrect syntax near '('.\n (102) (SQLExecDirectW)") [SQL: '\nCREATE TABLE "[dbo].[JUNESALES]" (\n\t"0" BIGINT NULL, \n\t"1" BIGINT NULL, \n\t"2" FLOAT NULL, \n\t"3" TEXT NULL, \n\t"4" BIT NULL, \n\t"5" BIT NULL, \n\t"6" FLOAT NULL, \n\t"7" FLOAT NULL, \n\t"8" FLOAT NULL, \n\t"9" FLOAT NULL, \n\t"10" FLOAT NULL, \n\t"11" BIGINT NULL, \n\tCHECK ("4" IN (0, 1)), \n\tCHECK ("5" IN (0, 1))\n)\n\n'] (Background on this error at: http://sqlalche.me/e/f405)
NOTE: I'm a Sybase ASE DBA; I don't work with python/pandas/sqlalchemy/etc; so while I can tell you why ASE is generating an error, and even show you one way to correctly format the create table command ... I have no idea how to go about telling your application how to (re)code the create table command (assuming this is something you don't have direct control over).
The error message is telling us the create table command looks like the following:
CREATE TABLE "[dbo].[JUNESALES]" (
"0" BIGINT NULL,
"1" BIGINT NULL,
"2" FLOAT NULL,
"3" TEXT NULL,
"4" BIT NULL,
"5" BIT NULL,
"6" FLOAT NULL,
"7" FLOAT NULL,
"8" FLOAT NULL,
"9" FLOAT NULL,
"10" FLOAT NULL,
"11" BIGINT NULL,
CHECK ("4" IN (0, 1)),
CHECK ("5" IN (0, 1))
)
Hmmmm, where to start ...
If you were to cut-n-paste this into a ASE session (eg, via the isql command line tool), you'll get the same error:
Msg 102, Level 15, State 181:
Server 'ASE200', Line 2:
Incorrect syntax near '('.
The command appears to be using double quotes in an attempt to escape non-standard identifiers. One issue with this is that, by default, ASE does not recognize the double quotes as an escape character for non-standard identifiers. To get around this you need to enable quoted_identifier, eg:
set quoted_identifier on
CREATE TABLE ...
... snip ...
set quoted_identifier off -- or leave 'on' if you're going to continue using double quotes to designate non-standard identifiers
go
While this will get you past the Msg 102 (syntax) error, you're now presented with some new errors:
Msg 2718, Level 16, State 1:
Server 'ASE200', Line 2:
Column or parameter #5: -- can't specify Null values on a column of type BIT.
Msg 2718, Level 16, State 1:
Server 'ASE200', Line 2:
Column or parameter #6: -- can't specify Null values on a column of type BIT.
To fix these errors you'll need to either designate the BIT columns as NOT NULL or change the datatype to something other than BIT (eg, tinyint? though you may now need to add some application code or check constraints to limit legal values to 0/1 ...??):
set quoted_identifier on
CREATE TABLE ...
... snip ...
"4" BIT not NULL,
"5" BIT not NULL,
... snip ...
set quoted_identifier off
go
At this point the table should be created (ie, no errors) but ... you're not out of the woods, yet.
If you run sp_help you see your table listed as such:
sp_help
go
Name Owner Object_type
--------------------------- ----- ------------
... snip ...
[dbo].[JUNESALES] dbo user table
... snip ...
The problem here (of course?) is that you wrapped the owner and table in a single pair of double quotes; and it doesn't help that you're attempting to use 2 different methods for handling non-standard identifiers ... double quotes ... square brackets; the main issue here is the double quotes are telling ASE that the square brackets are actually part of a single identifier called [dbo].[JUNESALES]; notice too that the period (.) is also considered as part of the single identifier (as opposed to a separator between the owner and table names).
If you attempt to fix his by placing the double quotes around [dbo] and [JUNESALES] then you get the following error message:
set quoted_identifier on
CREATE TABLE "[dbo]"."[JUNESALES]"
... snip ...
go
Msg 2734, Level 16, State 1:
Server 'ASE200', Line 2:
User name [dbo] does not exist in sysusers.
!!! notice the square brackets are considered as part of the user name !!!
OK, we can get around this by removing the square brackets from [dbo], but if you don't do the same for the table name ... the create table command succeeds but the brackets become part of the table name (as opposed to serving as delimiters), eg:
set quoted_identifier on
CREATE TABLE "dbo"."[JUNESALES]"
... snip ...
go
sp_help
go
Name Owner Object_type
--------------------------- ----- ------------
... snip ...
[JUNESALES] dbo user table
... snip ...
ASE supports the use of double quotes as a delimiter for non-standard identifiers ... if you first issue set quoted_identifier on.
ASE also supports the use of square brackets as a delimiter for non-standard identifiers ... and there's no need to issue the set quoted_identier on command.
I suggest you figure out how to use just one method of delimiting your non-standard identifiers (square brackets are a bit cleaner, don't require issuing set quoted_identifier on, and allow you to use the double quotes for delimiting textual/character data).
set quoted_identifier off -- optional if already set to 'off'
CREATE TABLE [dbo].[JUNESALES] (
[0] BIGINT NULL,
[1] BIGINT NULL,
[2] FLOAT NULL,
[3] TEXT NULL,
[4] BIT not NULL,
[5] BIT not NULL,
[6] FLOAT NULL,
[7] FLOAT NULL,
[8] FLOAT NULL,
[9] FLOAT NULL,
[10] FLOAT NULL,
[11] BIGINT NULL,
CHECK ([4] IN (0, 1)),
CHECK ([5] IN (0, 1))
)
go
sp_help
go
Name Owner Object_type
--------------------------- ----- ------------
... snip ...
JUNESALES dbo user table
... snip ...
Of course, the delimiters around dbo and JUNESALES aren't needed (ie, these are valid identifiers), but you're welcome to use the square brackets if you wish (eg, as a standard coding method for addressing all delimiters whether standard or non-standard).
NOTE: Above code snippets were executed against a ASE 15.7 (SP138) dataserver.
这篇关于任何人都知道为什么我在尝试将数据帧加载到 sybase 表时会收到此错误?[sql炼金术]的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
