python - Insert DateTime field in Ms-Sql

Question

I want to insert a date into Ms-SQL DB. How can I do that?

Here's what I'm doing:-

a = (datetime.datetime.now()).strftime("%Y-%m-%d %H:%M:%S")
data =  {'AWB_Number':'1','Weight':'1','Length':'1','Height':'1','Width':'1','Customer_Name':'Naaptol','Scan_Time': a,'Series_Flag':'Others'}

data = (
        data['AWB_Number'], data['Weight'], data['Length'], data['Height'],
        data['Width'], data['Customer_Name'], data['Scan_Time'] ,data['Series_Flag']
        )

print data


con_string = 'DSN=%s;UID=%s;PWD=%s;DATABASE=%s;' % (aramex_dsn, aramex_user, aramex_password, aramex_database)
cnxn = pyodbc.connect(con_string)

cursor = cnxn.cursor()

cursor.execute("insert into data_AutoScale_DELHUB VALUES (%s, %s, %s, %s, %s, %s, %s, %s)" % data)
cnxn.commit()

cnxn.close()

It returns an error saying

Traceback (most recent call last):
  File "tests.py", line 39, in <module>
    cursor.execute("insert into data_AutoScale_DELHUB VALUES (%s, %s, %s, %s, %s, %s, %s, %s)" % data)
pyodbc.ProgrammingError: ('42000', "[42000] [FreeTDS][SQL Server]Incorrect syntax near '09'. (102) (SQLExecDirectW)")

What is the problem?

Following is the Database structure:-

    AWB_Number = models.CharField(max_length = 255)
    Weight = models.CharField(max_length = 255)
    Length = models.CharField(max_length = 255)
    Width = models.CharField(max_length = 255)
    Height = models.CharField(max_length = 255)
    Customer_Name = models.CharField(max_length = 255)
    Scan_Time = models.DateTimeField(db_index = True)
    Series_Flag = models.CharField(max_length = 255)

I've not the value of 'a' so not all values of 'data' − I think the error is on this value because MS-SQL need a DateTime, so maybe the value is not correctly set. — Quentin THEURET, Aug 14 '14 at 09:50

score 7 · Accepted Answer · answered Aug 14 '14 at 10:08

7

As I saw here, I think for datetime values in database, you must have a datetime.datetime object, not a string. So, just replace

a = (datetime.datetime.now()).strftime("%Y-%m-%d %H:%M:%S")

by

a = datetime.datetime.now()

answered Aug 14 '14 at 10:08

Quentin THEURET

1,222
6
12

Then is any change is required in `VALUES (%s, %s, %s, %s, %s, %s, %s, %s)` ? `%s` for datetime needs to be changed to something else or not? – Praful Bagai Aug 14 '14 at 10:13
You can do that : cursor.execute("insert into data_AutoScale_DELHUB VALUES (?, ?, ?, ?, ?, ?, ?, ?)", list(data)) – Quentin THEURET Aug 14 '14 at 10:17
I think you should read the documentation of pyodbc if you want to use it correctly, at least the GettingStarted page : https://code.google.com/p/pyodbc/wiki/GettingStarted – Quentin THEURET Aug 14 '14 at 10:17
I did `cursor.execute("insert into data_AutoScale_DELHUB VALUES (?, ?, ?, ?, ?, ?, ?, ?)", data`, but it was throwing `TypeError: not all arguments converted during string formatting`. Upon Googling, it replaces `data` with `[data]` and now it says `pyodbc.Error: ('HY000', 'The driver did not supply an error!')` . What is the possible outcome of the this? – Praful Bagai Aug 14 '14 at 10:23
I set the autocommit to True as well as read on http://stackoverflow.com/questions/14577757/pyodbc-return-error-but-why. but still the same error. – Praful Bagai Aug 14 '14 at 10:28
Can you please provide your inputs on this? Thanks – Praful Bagai Aug 14 '14 at 10:34
Can you give us how you fix the problem with 'HY000' error ? – Quentin THEURET Aug 14 '14 at 11:55
It was a simple syntax problem. Noob mistake. Double Commas `,,`. :) – Praful Bagai Aug 14 '14 at 12:43
Can you please provide your suggestions on http://stackoverflow.com/questions/25308351/python-data-truncated-and-spaces-are-added-between-data-while-inserting-into-mss – Praful Bagai Aug 14 '14 at 12:44

score 0 · Answer 2 · edited Feb 23 '18 at 01:20

For anyone that lands here looking to do this in an MS Access database, you can define the field in your database table as a Date/Time type, and then place your date-time value as a single-quote-delimited string in your INSERT or UPDATE Query.

This string needs to be in a format that Access recognizes as a Date/Time, such as: '2/19/2018 11:44:22 PM' or '02/19/2018 23:44:22'. The ODBC driver takes care of the rest, and the date-time will end up in your table as a valid database Date/Time.

I haven't tried this with MS-SQL, but experience says it should work pretty much the same way. Here's some code that creates the proper format string for MS Access:

import pandas as pd
def MSDate_Format_from_Article(self, datestr: str) -> str:
    # Start with the format: 2018-02-14T21:57:55Z
    try:
        datetime_obj = pd.to_datetime(datestr)
    except:
        log("ERROR: Bad Date!")
        return "01/01/1980 00:00:00"
    else:
        year = "{:04d}".format(datetime_obj.year)
        month = "{:02d}".format(datetime_obj.month)
        day = "{:02d}".format(datetime_obj.day)
        hour ="{:02d}".format(datetime_obj.hour)
        minute = "{:02d}".format(datetime_obj.minute)
        second = "{:02d}".format(datetime_obj.second)
        # Return the date as a string in the format: 02/19/2018 23:44:22
        return month + '/' + day + '/' + year + ' ' + hour + ':' + minute + ':' + second

python - Insert DateTime field in Ms-Sql

2 Answers2