Python 3.4.1 INSERT INTO SQL Azure (pyodbc)
I am trying to INSERT some data into a table 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 am getting 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 am getting the following error
TypeError: The first argument to execute must be a string or unicode query.
How do I enter dates and integers into an azure SQL table via python?
thank
source to share
Thanks for the question. I highly recommend that you use pymssql if you are trying to connect to Azure SQL DB using Python. Coming to your question, it depends on what date format is used when creating the SQL table.
This is how you insert dates and integers using pymssql against the AdventureWorks schema (AdventureWorks schema is a preloaded schema you can create for your database 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 are some reference documents to help you :)
If you have any problems using pymssql with Azure SQL DB, let me know how I would like to help.
Best,
Meet Bhagdev
Program Manager, Microsoft
source to share
The date parser doesn't like your format. See 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()
source to share