0

I'm trying to insert date and time to SQL server in Linux (raspbian) environment using python language.So far i was able connect to MS Sql and also i created a table and im using pyodbc.

#! /user/bin/env python
import pyodbc 
import datetime

dsn = 'nicedcn'
user = myid
password = mypass
database = myDB

con_string = 'DSN=%s;UID=%s;PWD=%s;DATABASE=%s;' % (dsn, user, password, database)
cnxn = pyodbc.connect(con_string)
cursor = cnxn.cursor()

string = "CREATE TABLE Database3([row name] varchar(20), [my date] date), [my time]    time)"
cursor.execute(string)
cnxn.commit()

This part complied without any error.That means i have successfully created a table right? Or is there any issue?

I try to add date and time this way.

   now = datetime.datetime.now()
    d1 = now.date()
    t2 = now.strftime("%H-%M-%S")
    cursor.execute("insert into Database3([row name], [my date], [my time]) values (?,?,?)", 
    ('new1', d1, t2))
    cnxn.commit()

But i get this error. pyodbc.ProgrammingError:

('HY004', '[HY004] [FreeTDS] [SQL Server]Invalid data type (O) (SQLBindParameter)')

help me please. thanks in advance

Kevin777
  • 45
  • 1
  • 10
  • I'm not overly familiar with python but have you tried with colons instead of dashes for the time `"%H:%M:%S"`? – T I Jun 30 '14 at 14:24
  • What version of the SQL Server native client are you using? – Bryan Jun 30 '14 at 14:26
  • I'm Using MS SQL 2008 R2 Express – Kevin777 Jun 30 '14 at 14:37
  • "%H:%M:%S" yes i tried this but i get the same result – Kevin777 Jun 30 '14 at 14:38
  • @Kevin777 2008 R2 Express is the version of SQL Server you are using, not the version of the ODBC driver. What version of the ODBC driver for SQL Server is used in your DSN? This can be found using the [ODBC Data Source Administrator](http://i.stack.imgur.com/ngj4m.png). – Bryan Jun 30 '14 at 14:42
  • Sorry for that. I'm using UnixODBC – Kevin777 Jun 30 '14 at 14:46
  • and also i had use FreeTDS to establish the connection. and also pyodbc – Kevin777 Jun 30 '14 at 14:48
  • I'm still learning. i think the answer is pyodbc – Kevin777 Jun 30 '14 at 14:49
  • FreeTDS version is what matters. I'm not familiar with using FreeTDS, and don't know what version supports the `date` and `time` types in SQL Server. See the link to Microsoft's ODBC driver that has support for these types in my answer. – Bryan Jun 30 '14 at 15:05
  • IT is TDS 7.1(8.0 before) and it supported for SQL Server 2000. Includes support for big integer (64-bit int) and "variant" datatypes. – Kevin777 Jun 30 '14 at 15:25

1 Answers1

3

If you are on Windows, install the latest version of the Microsoft ODBC Driver for SQL Server to ensure the DATE and TIME types are supported.

If you are on Linux or macOS, use this page to determine the latest driver available for your distribution.

Use parameter placeholders and pass the values as date and time objects for the current datetime value.

now = datetime.datetime.now()
sql = "insert into Database3([row name], [my date], [my time]) values (?,?,?)"
cursor.execute(sql, ('new1', now.date(), now.time()))
cnxn.commit()

Note that the above code was only tested on Windows.

Bryan
  • 17,112
  • 7
  • 57
  • 80