2

Using Python 3.7.3 and mysql-connector 8.0.18, my SELECT result looks like:

[['2019-001', datetime.date(2019, 3, 11), 'Services']]

I would like the result to look like this:

[['2019-001', '11/03/2019', 'Services']]

Code snippet:

...
table = 'Customer'
select = "SELECT * FROM %s"
cursor.execute(select % table)
for row in cursor:
    append_list = list(row)
    import_list.append(append_list)                        
print(import_list)  #import_list is actually a list of lists
...

I can't modify the SELECT statement (using ... DATE_FORMAT(Date, '%d/%m/%Y') ) as I don't know when/if a DATE field will be encountered, or the name of the field as I'm iterating over an unknown number of tables in the MySQL database.

The investigation I've done indicates a couple of possibilities (as far as I know):

  1. Iterating over import_list, and do some sort of datetime formating like: 
datetime.date(2011, 1, 3).strftime("%d-%m-%Y")

This doesn't seem very elegant but if that's what I have to do, I need to figure out how to do that.

  1. I've seen reference to creating some sort of mysql.connector.conversion.MySQLConverter class. I don't know how to do that either, but I can investigate that further if this is the best way to go. This seems like a good way of doing things as it's done in the code that builds the connector.

Or is there another Pythonic way of doing what I'm trying to accomplish?

Alan W.
  • 353
  • 4
  • 13
  • I have the same issue with the date. Did you managed to solve it? – Kelvin Low Dec 30 '20 at 18:15
  • I hate to admit it, but the way I "fixed" it was by cheating. I control the MySQL database, so I changed it from a date/time field to just a text field. I'm bearing my lack of programming skills here. May God have mercy on my soul. – Alan W. Dec 31 '20 at 21:33
  • It may be a workaround but never a permanent solution, according to one answer here. https://stackoverflow.com/a/4759039/5685946 I hope you'll find a permanent solution soon. I'll answer your question if I manage to solve it. – Kelvin Low Jan 01 '21 at 06:24

1 Answers1

1

You can use strftime("%Y/%m/%d") to format timestamp. For example:

cursor.execute(select % table)
result = cursor.fetchall()

print(result[0][1].strftime("%Y/%m/%d")) # should give you the timestamp in the format you desire
Kelvin Low
  • 678
  • 1
  • 10
  • 23
  • Do you have any idea of how to convert `datetime.timedelta(seconds=55097)` into %H:%M:%S? – Mr Krisey Jan 09 '22 at 06:33
  • Never mind, I found the answer [here](https://stackoverflow.com/questions/764184/python-how-do-i-get-time-from-a-datetime-timedelta-object) – Mr Krisey Jan 09 '22 at 06:39