0

I am a newbie so please bear with me. I am trying to do a very simple insert from Python into a MS SQL database. I have tried pure pyodbc resulting in "pyodbc.ProgrammingError: No results. Previous SQL was not a query.":

import pyodbc
server = 'server'
database = 'db'
username = 'user'
password = 'pswd'
driver= '{ODBC Driver 17 for SQL Server}'
cnxn = pyodbc.connect('DRIVER='+driver+';SERVER='+server+';PORT=1433;DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = cnxn.cursor()

query = "SET NOCOUNT ON INSERT INTO foo ([x],[y],[z]) VALUES(1,1,1)  "

cursor.execute(query)

row = cursor.fetchone() 
while row: 
    print (row)
    row = cursor.fetchone()

And I have tried a combination of sqlalchemy, pyodbc and pandas resulting in "sqlalchemy.exc.ResourceClosedError: This result object does not return rows. It has been closed automatically."

import sqlalchemy
import pyodbc
import urllib
import pandas as pd

server = 'server'
database = 'db'
username = 'user'
password = 'pswd'
driver= '{ODBC Driver 17 for SQL Server}'

params = urllib.parse.quote_plus('DRIVER='+driver+';SERVER='+server+';PORT=1433;DATABASE='+database+';UID='+username+';PWD='+ password)
engine = sqlalchemy.create_engine("mssql+pyodbc:///?odbc_connect=%s" % params)

sql = "SET NOCOUNT ON INSERT INTO foo ([x],[y],[z]) VALUES(1,1,1)"
sql_df = pd.read_sql(sql=sql, con = engine)

I have tried playing around with SET NOCOUNT ON, putting the code into a stored procedure and calling the procedure and all kinds of other combinations. Nothing seems to work.

I would greatly appreciate any help. Thank you.

Jan Buzek
  • 1
  • As your error messages says this is not a statement that returns any rows. In other words you shouldn't fetching any rows from executing your query with ```cursor.fetchone()```. Also check this-> https://stackoverflow.com/questions/20199569/pyodbc-insert-into-sql – Felix.leg May 27 '20 at 17:23
  • Thank you, it worked! – Jan Buzek May 27 '20 at 17:42

1 Answers1

0

Why do you use the SET NOCOUNT ON?

Remove it and it will do the insert.

What do you expect to get with row = cursor.fetchone()?

You didn't wrote a select query you which results you expect to get?

Dharman
  • 30,962
  • 25
  • 85
  • 135
Nir Elbaz
  • 556
  • 4
  • 19