3

I am facing a problem where I am trying to add data from a python script to mysql database with InnonDB engine, it works fine with myisam engine of the mysql database. But the problem with the myisam engine is that it doesn't support foreign keys so I'll have to add extra code each place where I want to insert/delete records in database.

Does anyone know why InnonDB doesn't work with python scripts and possible solutions for this problem ??

Aniket
  • 279
  • 3
  • 8
  • 21
  • 1
    What is the problem you're facing? Do you have some error messages? Could you post them to us? – brandizzi Apr 13 '11 at 19:02
  • What do you mean, "InnoDB doesn't work with Python scripts"? It's worked fine all the times I've used it. – Daniel Roseman Apr 13 '11 at 19:31
  • @Daniel In non-autocommit mode if you disconnect before commiting MySQL will toss your updates out the window. Sounds borken to me. – Johan Apr 14 '11 at 07:32
  • Not exactly broken, as the behavior is intentional. Though maybe the python dbapi should warn if there's a transaction in progress when the connection is closed. –  Apr 14 '11 at 08:15
  • @Thouis, Off course transactions rule, but for newbies they suck. – Johan Apr 15 '11 at 15:19

2 Answers2

6

InnoDB is transactional. You need to call connection.commit() after inserts/deletes/updates.

Edit: you can call connection.autocommit(True) to turn on autocommit.

  • 1
    NO That depends on your connection parameters. You **can** set it to NOT autocommit, but autocommit is the default. – Johan Apr 13 '11 at 20:45
  • 3
    Read the answer above. Python disables autocommit. –  Apr 14 '11 at 06:58
4

Python DB API disables autocommit by default

Pasted from google (first page, 2nd result)

MySQL :: MySQL 5.0 Reference Manual :: 13.2.8 The InnoDB ... By default, MySQL starts the session for each new connection with autocommit ... dev.mysql.com/.../innodb-transaction-model.html

However

Apparently Python starts MySQL in NON-autocommit mode, see:
http://www.kitebird.com/articles/pydbapi.html

From the article:

The connection object commit() method commits any outstanding changes in the current transaction to make them permanent in the database. In DB-API, connections begin with autocommit mode disabled, so you must call commit() before disconnecting or changes may be lost.

Bummer, dunno how to override that and I don't want to lead you astray by guessing. I would suggest opening a new question titled:

How to enable autocommit mode in MySQL python DB-API?

Good luck.

Johan
  • 74,508
  • 24
  • 191
  • 319
  • >How to enable autocommit mode in MySQL python DB-API? conn.autocommit(True) From here: http://stackoverflow.com/q/12059424/719812 – Davide Andrea Feb 04 '17 at 01:37