Python - Pandas & SQLAlchemy - Datetime to Date

Question

I'm trying to only keep the data portion of the column uplod_tmstp when loading to SQL server.

df['uplod_tmstp'] = df['uplod_tmstp'].dt.floor('d')

did not work and neither did :

df['uplod_tmstp'] = df['uplod_tmstp'].dt.normalize()

What is the proper way to remove the excess Time from the Datetime? Seems the column is either changed to a string or when the sql load happens still has the full datetime.datetime(2020,5,5,0,0) instead of datetime.datetime(2020,5,5). I only want the date to be uploaded to the SQL Server

import sqlalchemy as sa
import pandas as pd
import pyodbc
import urllib

class myengine(object):
    def engine():
        params = urllib.parse.quote_plus("DRIVER={SQL Server Native Client 11.0};"
                                         "SERVER=myserver;"
                                         "DATABASE=DB1;"
                                         "Trusted_Connection=yes;"
                                         )
        engine = sa.create_engine("mssql+pyodbc:///?odbc_connect={}".format(params),echo=True)
        @sa.event.listens_for(engine, 'before_cursor_execute')
        def receive_before_cursor_execute(conn, cursor, statement, params, context, executemany):            
            if executemany:
                cursor.fast_executemany = True
                cursor.commit() 
        return engine

def sqlcol(df):
    dtypedict = {}
    for c in df:
        if  'object' in str(df[c].dtype):            
            dtypedict.update({c: sa.types.NVARCHAR(length=df[c].map(len).max())})
        if "datetime" in str(df[c].dtype):
            dtypedict.update({c: sa.types.Date()})    
    return dtypedict


def LoadData (f):
    df = pd.read_csv(f, dtype='str',parse_dates=['uplod_tmstp'])
    #change datetime.datetime(2020,5, 5 ,0, 0 ) to  datetime.datetime(2020,5, 5 )
    df['uplod_tmstp'] = df['uplod_tmstp'].dt.floor('d')
    df.to_sql('Myable',
              myengine.engine() ,
              if_exists='replace',
              schema='dbo',
              index=False,
              chunksize=1000,
              dtype=sqlcol(df))
    
if __name__ == "__main__":  
    LoadData('test.csv')

Python - Pandas & SQLAlchemy - Datetime to Date

0 Answers