Jan-09-2022, 05:17 PM
Hello All,
Disclaimer (Python Newbie):
No matter what I do I get the 'TypeError: Variable.dtype datetime64[ns] not supported' error. Even after conversion. The stored Proc, two columns are DATETIME.
Disclaimer (Python Newbie):
No matter what I do I get the 'TypeError: Variable.dtype datetime64[ns] not supported' error. Even after conversion. The stored Proc, two columns are DATETIME.
import pandas as pd
import pyodbc
import xport
import xport.v56
import numpy as np
import traceback
# Trusted Connection to Named Instance
try:
connection = pyodbc.connect('DRIVER={SQL Server};SERVER=MyServer;DATABASE=myDB;USERID=MyUserID;PASSWORD=MyPassword;')
except Exception as err:
print('Exception occured while trying to create a connection ', err)
else:
sql_query = pd.read_sql_query('''EXEC SP.GetData @ID=0''', connection)
# here, the 'connection' is the variable that contains your database connection information from step 2
df = pd.DataFrame(sql_query)
df['dttmupd'] = pd.to_datetime(df['dttmupd'])
df['dtiden'] = pd.to_datetime(df['dtiden'])
ds = xport.Dataset(df, name='DATA', dataset_label='Wonderful Data')
ds = df.rename(columns={k: k.upper()[:8] for k in ds})
# Other SAS metadata can be set on the columns themselves.
for k, v in ds.items():
v.label = k.title()
if v.dtype == 'object':
v.format = '$CHAR20.'
else:
v.format = '10.2'
# Libraries can have multiple datasets.
df.info()
library = xport.Library({'DATA': ds})
finally:
connection.close()Output:C:\Users\AppData\Local\Programs\Python\Python310\lib\site-packages\xport\v56.py:635: UserWarning: Converting column dtypes {'SPONNAM': 'string', 'SPONID': 'float', 'PROJNAM': 'string', 'PROJID': 'float', 'STUDNAM': 'string', 'STUDNO': 'string', 'STUDID': 'float', 'SITNAM': 'string', 'SITNNO': 'string', 'SITID': 'float', 'COUNTRY': 'string', 'CASEID': 'float',
'CASENAM': 'string', 'VERSNO': 'float', 'SUBJNO': 'string', 'CASESTAT': 'string', 'PANNAM': 'string', 'PANID': 'float', 'DSETNAM': 'string', 'DSETID': 'float', 'TYPEV': 'string', 'ISFATEV': 'string', 'DTIDEN': 'string', 'CASEPRTY': 'string', 'ISLOCKED': 'string', 'DISTRUL': 'string', 'PTPROFST': 'string', 'UPDNAM': 'string', 'DTTMUPD': 'string', 'CHGREAS': 'string', 'SPONCAS': 'string', 'DSETVER': 'float'}
warnings.warn(f'Converting column dtypes {conversions}')
Converting column 'SPONNAM' from object to string
Converting column 'SPONID' from int64 to float
Converting column 'PROJNAM' from object to string
Converting column 'PROJID' from int64 to float
Converting column 'STUDNAM' from object to string
Converting column 'STUDNO' from object to string
Converting column 'STUDID' from int64 to float
Converting column 'SITNNO' from object to string
Converting column 'SITID' from int64 to float
Converting column 'COUNTRY' from object to string
Converting column 'CASEID' from int64 to float
Converting column 'CASENAM' from object to string
Converting column 'VERSNO' from int64 to float
Converting column 'SUBJNO' from object to string
Converting column 'CASESTAT' from object to string
Converting column 'PANNAM' from object to string
Converting column 'PANID' from int64 to float
Converting column 'DSETNAM' from object to string
Converting column 'DSETID' from int64 to float
Converting column 'TYPEV' from object to string
Converting column 'ISFATEV' from object to string
Converting column 'CASEPRTY' from object to string
Converting column 'ISLOCKED' from object to string
Converting column 'DISTRUL' from object to string
Converting column 'PTPROFST' from object to string
Converting column 'UPDNAM' from object to string
Converting column 'CHGREAS' from object to string
Converting column 'SPONCAS' from object to string
Converting column 'DSETVER' from int64 to float
PS C:\Users\Desktop\Test Files> python -u "c:\Users\Inc\Desktop\Test Files\xport_file.py"
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 241 entries, 0 to 240
Data columns (total 32 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 sponnam 241 non-null object
1 sponid 241 non-null int64
2 projnam 241 non-null object
3 projid 241 non-null int64
4 studnam 241 non-null object
5 studno 241 non-null object
6 studid 241 non-null int64
7 sitnam 241 non-null object
8 sitnno 241 non-null object
9 sitid 241 non-null int64
10 country 241 non-null object
11 caseid 241 non-null int64
12 casenam 241 non-null object
13 versno 241 non-null int64
14 subjno 241 non-null object
15 casestat 241 non-null object
16 pannam 241 non-null object
17 panid 241 non-null int64
18 dsetnam 241 non-null object
19 dsetid 241 non-null int64
20 typev 217 non-null object
21 isfatev 208 non-null object
22 dtiden 206 non-null datetime64[ns]
23 caseprty 241 non-null object
24 islocked 241 non-null object
25 distrul 241 non-null object
26 ptprofst 117 non-null object
27 updnam 241 non-null object
28 dttmupd 241 non-null datetime64[ns]
29 chgreas 241 non-null object
30 sponcas 66 non-null object
31 dsetver 241 non-null int64
dtypes: datetime64[ns](1), int64(9), object(22)
memory usage: 60.4+ KBError:File "C:\Users\AppData\Local\Programs\Python\Python310\lib\site-packages\xport\v56.py", line 731, in __bytes__
return self._bytes()
File "C:\Users\AppData\Local\Programs\Python\Python310\lib\site-packages\xport\v56.py", line 739, in _bytes
b'members': b''.join(bytes(Member(member)) for member in self.values()),
File "C:\Users\AppData\Local\Programs\Python\Python310\lib\site-packages\xport\v56.py", line 739, in <genexpr>
b'members': b''.join(bytes(Member(member)) for member in self.values()),
File "C:\Users\AppData\Local\Programs\Python\Python310\lib\site-packages\xport\v56.py", line 618, in __bytes__
return self._bytes()
File "C:\Users\AppData\Local\Programs\Python\Python310\lib\site-packages\xport\v56.py", line 647, in _bytes
header = bytes(MemberHeader.from_dataset(self))
File "C:\Users\AppData\Local\Programs\Python\Python310\lib\site-packages\xport\v56.py", line 358, in from_dataset
ns = Namestr.from_variable(v, number=i)
File "C:\Users\AppData\Local\Programs\Python\Python310\lib\site-packages\xport\v56.py", line 122, in from_variable
raise TypeError(f'{type(variable).__name__}.dtype {variable.dtype} not supported')
TypeError: Variable.dtype datetime64[ns] not supported