当前位置: 动力学知识库 > 问答 > 编程问答 >

I use python mysql API to insert data into mysql but it didn't work when i didn't add statement 'with conn:'

问题描述:

#!/usr/bin/python

#Program:

# insert data into mysql and the display them

import MySQLdb as mdb

conn = mdb.connect(host = 'localhost', user = 'root', passwd = '8023xue0526', db ='contact')

cur = conn.cursor()

cur.execute("insert into contact values('123221', 'ni')")

cur.execute("select * from contact")

row_num = int(cur.rowcount)

for i in range(row_num):

row = cur.fetchone()

print row

I use those code to insert a data into mysql, the program worked. but after that, i check it in mysqlclient, the data didn't exist.

But when I add a statement 'with conn:' before 'cur = conn.cursor(), the data really insert into mysql. the code like this

#!/usr/bin/python

#Program:

# to get some information from mysql

import MySQLdb as mdb

import sys

conn = mdb.connect(host = 'localhost', user = 'root', passwd = '8023xue0526', db = 'contact')

with conn:

cur = conn.cursor()

cur.execute("insert into contact values('122221', 'ni')")

cur.execute("select * from contact")

row_num = int(cur.rowcount)

for i in range(row_num):

row = cur.fetchone()

print row

网友答案:

with conn: (using the connection object as a context manager) ensures that the transaction is committed if no exceptions occurred within the code block governed by the with statement.

Without the context manager, use conn.commit() to explicitly commit the transaction.

网友答案:

Martijn Pieters answer is the right one. Just to develop a little bit more, you have to understand that databases are designated both with "concurrent access" and "possibility of failure" in mind.

In that case, is would be unacceptable if someone started to make changes to the DB, showing that (incomplete) changes to other DB users, and suddenly, for some reason (bug, kill, etc.) aborting its modifications thus leaving the DB in an inconsistent state.

To prevent that, when your DB run in a decent isolation level you have to explicitly state that your changes are ready to publish. That is the purpose of the commit statement.

In Python you either have to explicitly call conn.commit()yourself. Or let the context manager with conn: do it for you if there is no exception. The two fragments below does globally the same thing:

>>> with conn:
...     c = conn.cursor()
...     c.doSomething()
...     # implicit commit here


>>> conn = sqlite3.connect(....)
>>> c = conn.cursor()
>>> c.doSomething()
>>> conn.commit()  # explicit commit here

Please note that, in either cases, the commit operation might fail. For example, if a concurrent transaction has already committed incompatible changes to the database.

分享给朋友:
您可能感兴趣的文章:
随机阅读: