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.