Error in pyodbc: 'pyodbc.Cursor' object has no attribute 'commit'

Question

I want to connect to SQL SERVER database from Python with pyodbc and freetds.

My connection is OK.

My code:

class GetSystems(Resource):
def get(self):
    try:
        cur = Connection.conn.cursor()
        cur.execute(
            "SELECT id,systemName,SystemDescription FROM MEFSystem")
        rows = cur.fetchall()
        objects_list = []
        for row in rows:
            d = collections.OrderedDict()
            d['id'] = row[0]
            d['systemName'] = row[1]
            d['systemDescription'] = row[2]
            objects_list.append(d)
        logger.info(objects_list)
        cur.commit()
        cur.close()
        logger.info(objects_list)
    except Exception as inst:
        cur.rollback()
        cur.close()
        print type(inst)
        print inst.args
        print inst
        logger.error(type(inst))
        logger.error(inst.args)
        logger.error(inst)
    return objects_list

This generates an error in cur.commit(): pyodbc.Cursor object has no attribute 'commit' and returns unknown data:

[
{
    "id": 2, 
    "systemDescription": "", 
    "systemName": "\uda00\udc53\ud940\udc41"
}, 
{
    "id": 3, 
    "systemDescription": "", 
    "systemName": "\uda00\udc53\ud800\udc47"
}, 
{
    "id": 4, 
    "systemDescription": "", 
    "systemName": "\ud900\udc52\ud8c0\udc4e\ud880\udc41"
}
]

The data should be:

[
{
    "id": 2, 
    "systemDescription": "", 
    "systemName": "SIAF"
}, 
{
    "id": 3, 
    "systemDescription": "", 
    "systemName": "SIGA"
}, 
{
    "id": 4, 
    "systemDescription": "", 
    "systemName": "RENTAS"
}
]

UPDATE
I commented the commit, but return data unknown from database. look => "systemName": "\uda00\udc53\ud940\udc41", should be "systemName": "SIGA"

Solution

The solution for the problem is the version of pyodbc, download pyodbc from this link and install.

THANKS!!!

Comments

[Steven Rumbalski]

That's odd. I thought maybe commit belonged to the connection rather than the cursor. But I do help(pyodbc.Cursor.commit) and I get the documentation "Commits any pending transaction to the database on the current connection, including those from other cursors."

[Steven Rumbalski]

Why are you trying to commit after a select-statement?

[Oscar Ordoñez Mego]

i commented the commit, but return data unknown from database. look => "systemName": "\uda00\udc53\ud940\udc41", should be "systemName": "SIGA"

[Muposat]

cur.commit() works for me. What is your Connection.conn.autocommit set to?

[Mattias Nilsson]

pyodbc supports the "Python Database API Specification v2.0", which says there is a commit() function on the connection. So are you sure you should call commit on the cursor?

[Steven Rumbalski]

@MattiasNilsson: Look at the documentation for pyodbc I quoted above. It looks like calling cursor.commit() passes that up to the connection.

[Gord Thompson]

Are you using a reasonably current version of pyodbc? What does print(pyodbc.version) say?