python - UPDATE 和 INSERT 在 Python 中不起作用

Question

我使用此代码来检索 id。它有效:

        db = MySQLdb.connect("localhost","root","","proyectoacademias" )

        cursor = db.cursor()

        sql = "SELECT id FROM test WHERE url=\'"
        sql = sql + self.start_urls[0]
        sql = sql + "\'"

        cursor.execute(sql)

        data = cursor.fetchone()

        for row in data:
            self.id_paper_web=str(row)

        db.close()

它为我提供了我必须更新的当前行的 ID...

但是当我尝试更新或插入时，它不起作用......

    def guardarDatos(self):
    db = MySQLdb.connect("localhost","root","","proyectoacademias" )

    cursor = db.cursor()

    sql = "UPDATE test SET abstract=\'"+str(self.abstracto)+"\', fecha_consulta=\'"+str(self.fecha_consulta)+"\', anio_publicacion=\'"+str(self.anio_publicacion)+"\', probabilidad="+str(self.probabilidad)+" WHERE id = "+str(self.id_paper_web)

    print "\n\n\n"+sql+"\n\n\n"
    cursor.execute(sql)      

    for i in range (len(self.nombres)):
        sql = "INSERT INTO test_autores VALUES (\'"+self.nombres.keys()[i]+"\', "+str(self.id_paper_web)+", \'"+self.instituciones[self.nombres[self.nombres.keys()[i]]]+"\', "+str((i+1))+")"
        print "\n\n\n"+sql+"\n\n\n"
        cursor.execute(sql)

    db.close()

我打印了我发送的每个 sql 查询，它们似乎都很好...没有抛出异常，只是数据库中没有更新或插入...

Answer 1

您必须提交...或将数据库设置为自动提交

db.commit()

有很多 py sqlite3 教程

By default, the sqlite3 module opens transactions implicitly before a Data Modification Language (DML) statement (i.e. INSERT/UPDATE/DELETE/REPLACE), and commits transactions implicitly before a non-DML, non-query statement (i. e. anything other than SELECT or the aforementioned).

So if you are within a transaction and issue a command like CREATE TABLE ..., VACUUM, PRAGMA, the sqlite3 module will commit implicitly before executing that command. There are two reasons for doing that. The first is that some of these commands don’t work within transactions. The other reason is that sqlite3 needs to keep track of the transaction state (if a transaction is active or not).

You can control which kind of BEGIN statements sqlite3 implicitly executes (or none at all) via the isolation_level parameter to the connect() call, or via the isolation_level property of connections.

If you want autocommit mode, then set isolation_level to None.

Otherwise leave it at its default, which will result in a plain “BEGIN” statement, or set it to one of SQLite’s supported isolation levels: “DEFERRED”, “IMMEDIATE” or “EXCLUSIVE”.

• > http://docs.python.org/library/sqlite3.html第11.13.6节