When I try to read data from table in one database(using Python, sqlalchemy or pandas to make some manipulations with data) and send this data to the same table in another database (with the same shema of table) values with the type datetime/timestamp in mysql which are equal to 0000-00-00 00:00:00 value or NULL (so this column can be NULL, some date(include 0000-00-00 00:00:00)), in Python they have the same value as None(if it's just sqlalchemy), or NaT(if we read to pandas dataframe). How can I save this values, and send to another table valid data(if was 0000-00-00 00:00:00, send 0000-00-00 00:00:00, if was NULL send NULL)
example shema of a table:
create table some_table
(
userid int auto_increment,
username varchar(255) not null,
email varchar(255) not null,
lastLogin_Date datetime null,
primary key (userid)
)
collate = utf8mb4_unicode_ci;
Data in MYSQL
column:
lastLogin_Date
null
0000-00-00 00:00:00
null
null
null
How this data reads python sqlalchemy -
Output if sqlalchemy: [(None,), (None,), (None,), (None,), (None,)]
Output pandas:
0 NaT
1 NaT
2 NaT
3 NaT
4 NaT
5 NaT
How I make it in Python,so if just use sqlalchemy we will get as above None (also as I know under the hood in pandas sqlalchemy):
engine3 = create_engine(
'mysql+mysqlconnector://' + 'root' + ':' + 'root' +
'@localhost:' + '3306' + '/' + 'testdb',
echo=False)
for chunk_dataframe in pd.read_sql(
"SELECT * FROM table_name", engine3, chunksize=10000):
pass
After the data is returned to mysql with the method df.to_sql(...), instead of 0000-00-00 00:00:00, it will be NULL, respectively, how to save the original values?
