On Fri, May 17, 2019 at 4:39 PM Alex Net <howle...@gmail.com> wrote:
>
> 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.
So I wouldn't think that the program would work at all, as you've
created the columns as NVARCHAR and are inserting datetime() objects
directly. I guess pyodbc is calling str() on these, or perhaps
Pandas is but that would be weird. I'd be using datetime columns or
converting the datetimes to an explicit date format like ISO8601.
But you're saying it only crashes on the 23rd row. Your error message
actually shows the parameter in position 23 and it is:
datetime.time(0, 0)
which is nothing like the rest of the datetime() objects being
inserted and if my 30 years of messing with spreadsheets is any guide
I'd say that value is coming from a blank row or something like that.
You want to get that time(0, 0) value out of there or convert it to a
string (convert all the data to strings if your table has NVARCHAR
columns in any case).
I dont actually understand how the to_sql or whatever is working in
any case as the SQL it renders has just one column in it but the table
has a bunch, so I'm sure the actual details are different.
>
> 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.
--
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/CA%2BRjkXGdwP%3DZXyCQr783%3D6nQPnj5m2LmkLMNgSGgNtRQNZXcZA%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.
