Python 3.4.1 INSERT INTO SQL Azure (pyodbc)

Question

I am trying to INSERT some data into a table that has been created in SQL Azure.

SQL Structure

Field 1 DATE
Field 2 INT
Field 3 INT

Python code used:

#I know I have connected to the correct database.
Connection = pyodbc.connect(conn.conn()) 
cursor = Connection.cursor()

SQLCommand = ('INSERT INTO table_name ([Field 1], [Field 2], [Field 3]) VALUES ('31-Dec-14', 1, 2);')
cursor.execute(SQLCommand)
Connection.commit()

I get the following error

pyodbc.ProgrammingError: ('42S22', "[42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name '31-DEC-2014'. (207)

If I replace it with

SQLCommand = ('INSERT INTO table_name ([Field 1], [Field 2], [Field 3]) VALUES (?, ?, ?);', ('31-DEC-2014',1,2))
cursor.execute(SQLCommand)
Connection.commit()

I get the following error

TypeError: The first argument to execute must be a string or unicode query.

How should I input dates and integers into an SQL azure table via python?

Thanks

meet-bhagdev · Accepted Answer · 2015-07-13 07:49:25Z

3

Thanks for the question. I highly recommend you use pymssql if you are trying to connect to Azure SQL DB using Python. Coming to your question, it depends on what the datetime format is used when you create your SQL table.

Here is how you would insert dates and integers using pymssql against the AdventureWorks schema(AdventureWorks schema is a pre loaded schema that you can create your database with for testing).

import pymssql
conn = pymssql.connect(server='yourserver.database.windows.net', user='yourusername@yourserver', password='yourpassword', database='AdventureWorks')
cursor = conn.cursor()
cursor.execute("INSERT SalesLT.Product (Name, ProductNumber, StandardCost, ListPrice, SellStartDate) OUTPUT INSERTED.ProductID VALUES ('SQL Server Express', 'SQLEXPRESS', 0, 0, CURRENT_TIMESTAMP)")
row = cursor.fetchone()
while row:
    print "Inserted Product ID : " +str(row[0])
    row = cursor.fetchone()

If you have questions about how to install pymssql on your machine, here is some reference documentation that will help you :)

- Windows
- Mac
- Linux

If you have any issues with using pymssql with Azure SQL DB do let me know as I would love to help.

Best,
Meet Bhagdev
Program Manager, Microsoft

edited Jul 13, 2015 at 7:49

answered Jul 13, 2015 at 0:25

meet-bhagdev

2,74820 silver badges22 bronze badges

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

5 Comments

Harris Over a year ago

Comment 1: I think pymssql requires the use of 'FreeTDS'? (i.e. SQL Native Client 11.0) won't work in a windows system?

Harris Over a year ago

Comment 2: I still receive a Invalid Column Name error using pymssql. I think it is something to do with putting a date format via python into sql.

meet-bhagdev Over a year ago

Hello Harris, it should work on Windows. Here is how you would do it - azure.microsoft.com/en-us/documentation/articles/…. Also what how does your date format look like in your database. The code that is in the link above should work for the default date format.

Harris Over a year ago

I have resolved the date format issue by the following 'SQLCommand = ('INSERT INTO table_name ([Field 1], [Field 2], [Field 3]) VALUES (?, ?, ?);') cursor.execute(SQLCommand, (datetime.datetime.strptime('31-DEC-2014', "%d-%m-%Y",1,2) ) Connection.commit() ' ... But this is done via pyodbc instead of pymssql. It seems that pyodbc doesn't like all the input data to be in the SQLCommand line.

meet-bhagdev Over a year ago

Hi Harris, I am glad you were able to figure this out. I work at Microsoft and I am currently working on python client drivers for Azure SQL DB and SQL Server. Would you be willing to chat with me sometime this week? Would love to learn about your pain points and get some feedback :) My email is mebha at microsoft dot com

Thane Plummer · Accepted Answer · 2015-07-12 16:00:47Z

1

The date parser does not like your format. See the Microsoft documentation for a list of valid formats

The following syntax should work:

SQLCommand = ("INSERT INTO table_name ([Field 1], [Field 2], [Field 3]) VALUES ('2014-12-31', 1, 2);")
cursor.execute(SQLCommand)
Connection.commit()

answered Jul 12, 2015 at 16:00

Thane Plummer

10.9k3 gold badges28 silver badges31 bronze badges

Collectives™ on Stack Overflow

Python 3.4.1 INSERT INTO SQL Azure (pyodbc)

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related