CODEWITHSUNDEEP
pythonmysqlsqlpandas
DGMS89
DGMS89

Reputation: 1677

Fixing a type error when passing data from csv to SQL

Scenario: Following up from a previous question (Removing the timestamp from a datetime in pandas dataframe) I have a code that reads data from excel into a pandas dataframe and uses the command dataframe.to_sql to insert that data into a given SQL database.

Problem: Since my dates are retrieved in the american format (mm/dd/yyyy) and my database is in the international format (dd/mm/yyyy), I get a type error trying to upload my data to SQL.

What I already tried: I tried the suggestion given in the previous question and in that way the data changing runs, but I get the error when I try to upload to SQL.

Line that I am currently using:

fnl['Date'] = pd.to_datetime(fnl['Maturity'], errors='coerce')

which allows the code to run, but yields the problem in SQL.

I also tried:

fnl['Date'] = pd.to_datetime(fnl['Date'], format='%m/%d/%Y', errors:'coerce')

and

fnl['Date'] = pd.to_datetime(fnl['Date'], format='%m/%d/%Y')

Still with no success.

I previously changed all my encoding to utf-8 in the beginning of the code, so I can't see where the problem lies.

Question: How can I solve this problem?

Data example:

Date
1/15/2023
1/15/2023
6/30/2023
6/30/2023
8/1/2022
8/1/2022
7/25/2022
7/25/2022
7/19/2024
7/13/2022
7/13/2022

Upvotes: 2

Views: 474

Answers (1)

cs95
cs95

Reputation: 403278

If your Date column returns dates in international format, you'll want to reflect that when you're converting to datetime.

fnl['Date'] = pd.to_datetime(fnl['Date'], format='%m/%d/%Y', errors='coerce')

You'll need to specify the format in the source so that the conversion can be done properly.

If you want to convert your datetime back to a string in the %m/%d/%Y format, use the .dt.strftime function:

fnl['DateString'] = fnl['Date'].dt.strftime('%d/%m/%Y')

s = pd.to_datetime(df['Date'], format='%m/%d/%Y', errors='coerce')
s

0    2023-01-15
1    2023-01-15
2    2023-06-30
3    2023-06-30
4    2022-08-01
5    2022-08-01
6    2022-07-25
7    2022-07-25
8    2024-07-19
9    2022-07-13
10   2022-07-13
Name: Date, dtype: datetime64[ns]

s = s.dt.strftime('%d/%m/%Y')
s

0     15/01/2023
1     15/01/2023
2     30/06/2023
3     30/06/2023
4     01/08/2022
5     01/08/2022
6     25/07/2022
7     25/07/2022
8     19/07/2024
9     13/07/2022
10    13/07/2022
Name: Date, dtype: object

Upvotes: 2

Related Questions