Invalid datetime format in python-mysql query

Question

I wanted to fetch a record from database which is having the datetime field a part of primary key. I am querying from python using mysqldb package which is resulting in error

Invalid datetime format

wherein the same query is working fine when executed through command line and mysql workbench.

My code looks like this.

def get_all_list(table_name, modify_dt, params):
    cursr = connections['default'].cursor()
    stmt = 'select * from ' + table_name + ' where ' + params 
    cursr.execute(stmt)
    data = cursr.fetchall()

the params is the string which contains my primary key value.

for instance

params is "join_date = '2016-09-08 00:00:00+00:00'"

my final query looks similiar to this

select * from employee where join_date = '2016-09-08 00:00:00+00:00'

When executed, the same is providing result in mysql workbench while when executed through my program , I am getting error as

Incorrect datetime value: '2016-09-08 00:00:00+00:00' for column 'join_date' at row 1

score 1 · Accepted Answer · edited May 23 '17 at 10:32

1

Date format should be

'2016-09-08 00:00:00'

if you need to set time zone, you need a separate SQL query

set time_zone='+00:00';

See also: How do I set the time zone of MySQL?

If you are using django, you can set time_zone for the entire app in the settings file:

DATABASES['default'] = {
    'ENGINE': 'your_db_engine',
    'NAME': 'my_database',
    'USER': 'username',
    'PASSWORD': 'password',
    'HOST': 'localhost',
    'PORT': 3306,
    'OPTIONS': {
        "init_command": "SET storage_engine=InnoDB; set time_zone='+00:00';",
    },
}

edited May 23 '17 at 10:32

Community

1
1

answered Oct 14 '16 at 08:16

2ps

15,099
2
27
47

Thanks.. But where am i supposed to set the time_zone for my app.. Should it be in my settings.py file – Arun Oct 14 '16 at 08:19
Are you using django? – 2ps Oct 14 '16 at 08:19
Hey.. thanks for the above update but i am not using default storage engine currently. Also, when i am trying to set it, it is showing unknown system variable storage_engine. – Arun Oct 14 '16 at 08:35
The issue is resolved when i used USE_TZ = False in my settings.py – Arun Oct 15 '16 at 08:44

Invalid datetime format in python-mysql query

1 Answers1