Date Conversion issue

[Racer_x]

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.

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+ KB

Error:
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

[Larz60+]

Is that the complete and unaltered error traceback? Looks to be missing part, and all is important for debugging.

[Racer_x]

This is it, should I try to produce more info add something to the code?