I saw that there were similar questions in the past as well, but unfortunately I could not find the solution there. I am relatively new to Python.
I have a pandas dataframe which I want to write to SQL Server using SQLalchemy dataframe.to_sql()
The dataframe has exactly the same # of columns and the column names are same as in the sql table. I am not able to pin point the reason behind this error. Also, I note that by default it is exporting index of the dataframe as well. So, do we need an additional column in the sql table corresponding to index as well. I have tried that as well but it still gives the same error.
This is the code that I am running:
import sqlalchemy
import pyodbc
engine = create_engine('mssql+pyodbc://workspace?driver=SQL+Server+Native+Client+11.0', echo=True)
campaign_table.to_sql("workspace.dbo.campaign_results", con= engine,if_exists = 'append')
ERROR that I GET:
ProgrammingError: (pyodbc.ProgrammingError) ('42000', '[42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Error converting data type varchar to bigint. (8114) (SQLExecDirectW)') [SQL: u'INSERT INTO [workspace.dbo.campaign_results] ([index], [Emailing_id], delivered, clickcount, [impressionCount], [errorCount], abusecount, unsubscribecount, [unknownCount], ctr, cto, destinationcount, firsttimestamp, lasttimestamp, unique_clicks, unique_impressions, [Open_Rate], campaign_id, template_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)'] [parameters: ('Emailing_id', '27619', '10606', '241', '3658', '243', '0', '0', '7797', '2', '8', '10845', u'2017-02-03 06:30:15', u'2017-02-03 06:30:15', '216', '2589', '23', '1', '1')]
Questions
- Is this the error on datatype conversion or some connection issue between Python and SQLServer?
- How can I know which variable is the source of this trouble? Trial and error is one way, but what if I have too many variables in the dataframe?
Please guide or direct me to the relevant threads as I am still finding my way through the maze.
Cheers!
UPDATE
After changing the format of a few variables, Python seems to run okay but the SQL table is still not getting updated. There was an exact same problem on Stackoverflow, but the solution stated there (adding one row manually to SQL table) did not help either. Here is the new output:
campaign_table_f.to_sql("workspace.dbo.campaign_results", con= engine,if_exists = 'append')
2017-02-15 09:46:51,773 INFO sqlalchemy.engine.base.Engine SELECT [COLUMNS_1].[TABLE_SCHEMA], [COLUMNS_1].[TABLE_NAME], [COLUMNS_1].[COLUMN_NAME], [COLUMNS_1].[IS_NULLABLE], [COLUMNS_1].[DATA_TYPE], [COLUMNS_1].[ORDINAL_POSITION], [COLUMNS_1].[CHARACTER_MAXIMUM_LENGTH], [COLUMNS_1].[NUMERIC_PRECISION], [COLUMNS_1].[NUMERIC_SCALE], [COLUMNS_1].[COLUMN_DEFAULT], [COLUMNS_1].[COLLATION_NAME]
FROM [INFORMATION_SCHEMA].[COLUMNS] AS [COLUMNS_1]
WHERE [COLUMNS_1].[TABLE_NAME] = CAST(? AS NVARCHAR(max)) AND [COLUMNS_1].[TABLE_SCHEMA] = CAST(? AS NVARCHAR(max))
2017-02-15 09:46:51,776 INFO sqlalchemy.engine.base.Engine (u'workspace.dbo.campaign_results', u'ashish.dang')
2017-02-15 09:46:51,877 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2017-02-15 09:46:51,881 INFO sqlalchemy.engine.base.Engine INSERT INTO [workspace.dbo.campaign_results] ([index], [Emailing_id], delivered, clickcount, [impressionCount], [errorCount], abusecount, unsubscribecount, [unknownCount], ctr, cto, destinationcount, firsttimestamp, lasttimestamp, unique_clicks, unique_impressions, [Open_Rate], campaign_id, template_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
2017-02-15 09:46:51,882 INFO sqlalchemy.engine.base.Engine ((0L, '27610', '381', '41', '473', '5', '0', '0', '201', '9', '19', '386', u'2017-02-03 06:15:17', u'2017-02-03 06:15:17', '34', '146', '37', 1L, 0L), (1L, '27612', '153', '12', '251', '2', '0', '0', '58', '6', '9', '155', u'2017-02-03 06:15:18', u'2017-02-03 06:15:18', '9', '86', '55', 1L, 1L))
2017-02-15 09:46:51,977 INFO sqlalchemy.engine.base.Engine COMMIT
