Hello! I have been writing code that would allow me to read from an Excel
sheet and writing it in a MS SQL Database. The code was functioning well,
meaning it was running and writing all cells until it got to cell E,23 ,
where it crashes. Column E is called DueDate, and the first 22 cells print
similarly to datetime when using nvarchar(max), date or datetime as the
columns datatype. If I allow the program to read and write E1 - E22, it
won't have a problem writing them to the database. It is when I include
this cell that the code gives me that error.
I have looked at all the resources online, yet still find no solution.
I first ran this program in Python 3.7.3 and it gave me the same exact
problem
I played around with the datatypes in my SQL create table query but that
did not help. Nvarchar(max) didn't either.
Then after reading about some similar issues, I tried changing the drivers
in my connection string (after installing them of course) to older drivers
to no avail.
Next, I tried using a virtual environment in Visual Studio running multiple
versions of python, including 2.7(32,64) again with no results.
At first, I thought it was a problem with the read_excel function but
Visual Studio hinted towards my To_SQL.
Code:
import pandas as pd
import sqlalchemy
from sqlalchemy import create_engine
import pyodbc
#Connection to SQL Server (pyodbc)
server = 'SERVERNAME'
db = 'DATABASENAME'
user = 'NAME'
pwd = 'PASSWORD'
driver = '{SQL Server Native Client 11.0}'
conn = pyodbc.connect(
'DRIVER='+ driver + ';SERVER='+ server + ';DATABASE=' + db + ';UID=' +
user + ';PWD=' + pwd )
#Connection to SQL Server 2(SQLAlchemy)
engine = create_engine(
'mssql+pyodbc://NAME:PASSWORD@SERVERNAME/DATABASENAME?driver=SQL+Server')
#Query To Create Table
def make_table(SQLquery):
query1 = SQLquery
cur = conn.cursor()
cur.execute(query1)
conn.commit()
#Extract from Excel
def read_sheet(filePath,sheetName,columns='A:Z', rows=None,skrows=None):
global df
file = filePath
sheet = sheetName
cols = columns
rows1 = rows
rows2 = skrows
df = pd.read_excel(file, sheet, usecols= cols,index_col=0,nrows=rows1,
skiprows=rows2)
return df
print('Reading Completed. Writing now...')
#Import to SQL Server
def write_to_sql(tableName, chunks=1000):
col_name= 'E'
df.to_sql(tableName, engine, if_exists= 'append', chunksize=chunks,dtype
={col_name:DATETIME for col_name in df})
print('Writing Complete!')
make_table(
'''
CREATE TABLE test4 (Something1 nvarchar(max), Something2 nvarchar(max),
Something3 nvarchar(max), Something4 varchar(max), DueDate nvarchar(max),
Something5 int, Something6 nvarchar(max), Something7 nvarchar(max),
Something8 nvarchar(max), Notes nvarchar(max) )
''')
read_sheet('C:/Users/USER/Documents/ExcelToSQL/SAMPLE.xlsx','SHEET',columns=
'E',rows=23)
write_to_sql('test')
conn.close()
Full Error & Trace:
Traceback (most recent call last):
File "c:\program files (x86)\microsoft visual
studio\2019\community\common7\ide\extensions\microsoft\python\core\ptvsd_launcher.py",
line 119, in <module>
vspd.debug(filename, port_num, debug_id, debug_options, run_as)
File "c:\program files (x86)\microsoft visual
studio\2019\community\common7\ide\extensions\microsoft\python\core\Packages\ptvsd\debugger.py",
line 37, in debug
run(address, filename, *args, **kwargs)
File "c:\program files (x86)\microsoft visual
studio\2019\community\common7\ide\extensions\microsoft\python\core\Packages\ptvsd\_local.py",
line 79, in run_file
run(argv, addr, **kwargs)
File "c:\program files (x86)\microsoft visual
studio\2019\community\common7\ide\extensions\microsoft\python\core\Packages\ptvsd\_local.py",
line 140, in _run
_pydevd.main()
File "c:\program files (x86)\microsoft visual
studio\2019\community\common7\ide\extensions\microsoft\python\core\Packages\ptvsd\_vendored\pydevd\pydevd.py",
line 1925, in main
debugger.connect(host, port)
File "c:\program files (x86)\microsoft visual
studio\2019\community\common7\ide\extensions\microsoft\python\core\Packages\ptvsd\_vendored\pydevd\pydevd.py",
line 1283, in run
return self._exec(is_module, entry_point_fn, module_name, file,
globals, locals)
File "c:\program files (x86)\microsoft visual
studio\2019\community\common7\ide\extensions\microsoft\python\core\Packages\ptvsd\_vendored\pydevd\pydevd.py",
line 1290, in _exec
pydev_imports.execfile(file, globals, locals) # execute the script
File "C:\Users\USER\Documents\ExcelToSQL\main2.py", line 69, in <module>
write_to_sql('test4')
File "C:\Users\USER\Documents\ExcelToSQL\main2.py", line 57, in
write_to_sql
df.to_sql(tableName, engine, if_exists= 'append',
chunksize=chunks,dtype={col_name:DATETIME for col_name in df})
File "C:\Python27\lib\site-packages\pandas\core\generic.py", line 2531,
in to_sql
dtype=dtype, method=method)
File "C:\Python27\lib\site-packages\pandas\io\sql.py", line 460, in to_sql
chunksize=chunksize, dtype=dtype, method=method)
File "C:\Python27\lib\site-packages\pandas\io\sql.py", line 1174, in
to_sql
table.insert(chunksize, method=method)
File "C:\Python27\lib\site-packages\pandas\io\sql.py", line 686, in insert
exec_insert(conn, keys, chunk_iter)
File "C:\Python27\lib\site-packages\pandas\io\sql.py", line 599, in
_execute_insert
conn.execute(self.table.insert(), data)
File "C:\Python27\lib\site-packages\sqlalchemy\engine\base.py", line 988,
in execute
return meth(self, multiparams, params)
File "C:\Python27\lib\site-packages\sqlalchemy\sql\elements.py", line
287, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
File "C:\Python27\lib\site-packages\sqlalchemy\engine\base.py", line
1107, in _execute_clauseelement
distilled_params,
File "C:\Python27\lib\site-packages\sqlalchemy\engine\base.py", line
1248, in _execute_context
e, statement, parameters, cursor, context
File "C:\Python27\lib\site-packages\sqlalchemy\engine\base.py", line
1466, in _handle_dbapi_exception
util.raise_from_cause(sqlalchemy_exception, exc_info)
File "C:\Python27\lib\site-packages\sqlalchemy\util\compat.py", line 383,
in raise_from_cause
reraise(type(exception), exception, tb=exc_tb, cause=cause)
File "C:\Python27\lib\site-packages\sqlalchemy\engine\base.py", line
1224, in _execute_context
cursor, statement, parameters, context
File "C:\Python27\lib\site-packages\sqlalchemy\dialects\mssql\pyodbc.py",
line 347, in do_executemany
cursor, statement, parameters, context=context
File "C:\Python27\lib\site-packages\sqlalchemy\engine\default.py", line
549, in do_executemany
cursor.executemany(statement, parameters)
sqlalchemy.exc.DBAPIError: (pyodbc.Error) ('HYC00', u'[HYC00]
[Microsoft][ODBC SQL Server Driver]Optional feature not implemented (0)
(SQLBindParameter)')
[SQL: INSERT INTO [test4] ([DueDate]) VALUES (?)]
[parameters: ((datetime.datetime(2017, 6, 1, 0, 0),),
(datetime.datetime(2017, 7, 15, 0, 0),), (datetime.datetime(2017, 8, 1, 0,
0),), (datetime.datetime(2017, 9, 30, 0, 0),), (datetime.datetime(2017, 10,
1, 0, 0),), (datetime.datetime(2017, 12, 31, 0, 0),),
(datetime.datetime(2018, 5, 1, 0, 0),), (datetime.datetime(2018, 1, 14, 0,
0),) ... displaying 10 of 23 total bound parameter sets ...
(datetime.datetime(2018, 7, 13, 0, 0),), (datetime.time(0, 0),))]
(Background on this error at: http://sqlalche.me/e/dbapi)
Press any key to continue . . .
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/3db0b9bc-f029-402d-9b63-4ae30257c6a3%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
