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

Community · Accepted Answer · 2017-05-23 10:32:30Z

2

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, 2017 at 10:32

CommunityBot

11 silver badge

answered Oct 14, 2016 at 8:16

2ps

16.1k2 gold badges33 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Arun Over a year ago

Thanks.. But where am i supposed to set the time_zone for my app.. Should it be in my settings.py file

2ps Over a year ago

Are you using django?

Arun Over a year ago

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 Over a year ago

The issue is resolved when i used USE_TZ = False in my settings.py

Collectives™ on Stack Overflow

Invalid datetime format in python-mysql query

1 Answer 1

4 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Linked

Related