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"

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, Nov 06 '15 at 18:10
i commented the commit, but return data unknown from database. look => "systemName": "\uda00\udc53\ud940\udc41", should be "systemName": "SIGA" — Oscar Ordoñez Mego, Nov 06 '15 at 19:19
cur.commit() works for me. What is your Connection.conn.autocommit set to? — Muposat, Nov 06 '15 at 19:23
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? — Mattias Nilsson, Nov 06 '15 at 19:38
@MattiasNilsson: Look at the documentation for pyodbc I quoted above. It looks like calling `cursor.commit()` passes that up to the connection. — Steven Rumbalski, Nov 06 '15 at 20:26
Are you using a reasonably current version of pyodbc? What does `print(pyodbc.version)` say? — Gord Thompson, Nov 07 '15 at 19:28

score 3 · Accepted Answer · answered Nov 09 '15 at 17:59

3

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

THANKS!!!

answered Nov 09 '15 at 17:59

Oscar Ordoñez Mego

121
1
1
11

score 0 · Answer 2 · answered Nov 06 '15 at 19:48

Not sure how you get those strange value, could it be a encoding problem? Looking at your data (look at the data in bold):

\uda00\udc53\ud940\udc41 - should be SIAF, bold data is ascii for "S" and "A"

\uda00\udc53\ud800\udc47 - should be SIGA, bold data is ascii for "S", "G"

\ud900\udc52\ud8c0\udc4e\ud880\udc41 - RENTAS, bold is "R", "N", "A"

So it seems you sort of picked up the right data, but it isn't communicated to you in a meaningful way.

No idea why though, just sharing observations...

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

2 Answers2