Changing datetime format ready for sql database in pandas-CodePudding

I have this datetime format 2016-01-31T20:13:48.000 02:00 as an object in pandas. What would be the best way for changing it, for transfering to sql database. For sql, iam using mysql. I need to store all this format, including time zone.

CodePudding user response：

Up to you how to store this into a SQL database, answering your main question though, here's how you could convert this into a datetime class type.

import pandas as pd
from datetime import datetime


def convert_to_datetime(input):
    # function that reformats input string to datetime type
    return datetime.strptime(input, "%Y-%m-%dT%H:%M:%S.%f%z")

# example DataFrame
df = pd.DataFrame ({'date_col': ['2016-01-02T20:13:48.000 02:00',
                                    '2016-02-02T20:13:48.000 02:00',
                                    '2016-03-03T20:13:48.000 02:00']})
# convert string values into datetime type for all rows
df['new_date_col'] = df.apply(lambda x: convert_to_datetime(x['date_col']), axis=1)
# drop original column
df = df.drop(columns=['date_col'])

Then you could use pandas.DataFrame.to_sql() to store into any SQL database supported by sqlalchemy package. See https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_sql.html