Why does MySQLdb fail to insert data? InsertdatafailedusingMySQLdb?

After pythonmysqlplug-In fails conn.exe cute (,), you need to add the following sentence: conn. commit () is required for transaction processing, which is not mentioned in many places .. I don't know how their code works-

After the python mysql insertion failure conn.exe cute (,), you need to add the following sentence: conn. commit () requires transaction processing, which is not mentioned in many places .. I don't know how their code is successful--kiss, it's that simple .. Why does MySQLdb fail to insert data? Insert data failed using MySQLdb? Currently, the project uses mySQL DATA

Failed to insert python mysql

Conn.exe cute (..., ...) After

Add one sentence:

Conn. commit ()

This sentence is required for transaction processing, which is not mentioned in many places .. I don't know how their code works --

It's that simple ..

Why does MySQLdb fail to insert data? Insert data failed using MySQLdb?

Now the project uses the mySQL database. At first, I chose pymysql as the driver. Later, it was found thatMySQLdbThere are quite a few, so I changed it. No one has maintained pymysql for a long time. Let's talk about the problem, that is, executeInsertAlwaysFailedBut it does not throw any exceptions, and mySQL is still blocked.

An important point is to execute and then commit it.

There are three specific methods:

1. Add a COMMIT command after the SQL statement

Cursor.exe cute ('InsertInto? User? (Id ,? Name )? Values? (1 ,? 'Luchanghong); COMMIT; ') 2. Reserve a commit for the database connection conn.

Conn? =?MySQLdb. Connect (host? =? 'Localhost ',? User? =? 'Root ',? Passwd? =? 'Root ',? Db? =? 'Test') conn. autocommit (1) 3. commit before releasing the database connection

Conn? =?MySQLdb.Connect(export cursor.exe cute ('InsertInto ?... ') Cursor. close () conn. commit () conn. close ()

I have verified all three methods.

Why does MySQLdb fail to insert data? Insert data failed using MySQLdb?

Now the project uses the mySQL database. At first, I chose pymysql as the driver. Later, I found that MySQLdb was quite a lot used on the Internet, so I changed it, And pymysql was not maintained for a long time. Let's talk about the problem, that is, when executing the insert statement, it always fails, but does not throw any exception, and mySQL is still blocked.

In the company, it is estimated that all of them are dizzy, so they did not come up with the results. After I got off work, I used my cell phone to access the Internet. I found that an important point was to execute the execute Command before making a commit. There are three specific methods:

1. Add a COMMIT command after the SQL statement

cursor.execute('insert into user (id, name) values (1, 'luchanghong);COMMIT;')

2. Reserve a commit for the database connection conn

conn = MySQLdb.connect(host = 'localhost', user = 'root', passwd = 'root', db = 'test')conn.autocommit(1)

3. commit before releasing the database connection

conn = MySQLdb.connect()cursor.execute('insert into ...')cursor.close()conn.commit()conn.close()

PS: I am using the third method. The other two have not been verified yet. If you are interested, test it.

• Solution 1: Use the ignore keyword
• Solution 2: Use replace
• Solution 3: on duplicate key update?

Solution 1: Use the ignore keyword

If you use the primary key primary or the unique index unique to distinguish the uniqueness of a record, you can use the following to avoid repeated insertion of records:

Insert? Ignore? Into table_name (email, phone, user_id) values ('test9 @ 163.com ', '000000', '000000').

The record is ignored. After execution, the number 0 is returned. Another application is to copy the table to avoid repeated records:

Insert? Ignore? Into table (name )? Select? Name from table2

Solution 2: Use replace

The syntax format of replace is:

1. replace into table_name (col_name ,...) Values (...)

2. replace into table_name (col_name ,...) Select...

3. replace into table_name set col_name = value ,...

Algorithm Description:

The REPLACE operation is similar to the INSERT operation. However, if the old record has the same value as the new record, the old record is deleted before the new record is inserted, that is:

1 .???? Try to Insert a new row into the table

2 .???? When insertion fails due to a duplicate keyword error for the primary key or unique Keyword:

Delete conflicting rows with duplicate keyword values from the table

Try to Insert a new row into the table again

The criteria for determining the values of the old record and the new record are as follows: The table has a primary key or UNIQUE index. Otherwise, using a REPLACE statement is meaningless.

. This statement is the same as INSERT, because no index is used to determine whether other rows have been copied in the new row.

Return Value:

The REPLACE statement returns a number to indicate the number of affected rows. This is the sum of the number of deleted and inserted rows.

The number of affected rows can be easily determined whether REPLACE only adds one row, or whether REPLACE also replaces other rows: Check whether this number is 1 (Added) or

Larger (replace ).

Example:

Eg: (the phone field is a unique index)

Replace? Into table_name (email, phone, user_id) values ('test569', '000000', '000000 ′)

In addition, in SQL Server, you can perform the following operations:

If not exists (select phone from t where phone = '1 ′)

Insert into t (phone, update_time) values ('1', getdate ())

Else

Update t set update_time = getdate () where phone = '1 ′

For more information, see: http://dev.mysql.com/doc/refman/5.1/zh/sql-syntax.html#replace

Solution 3: ON DUPLICATE KEY UPDATE

Such? You can also insert ..... The on duplicate key update method is added later.

If you specify on duplicate key update and insert a row

Or duplicate values in the primary key,

The old row is updated. For example, if column a is defined as UNIQUE and contains a value of 1, the following two statements have

Same effect:

Mysql> insert into table (a, B, c) VALUES (1, 2, 3)

-> On duplicate key update c = c + 1;

Mysql> UPDATE table SET c = c + 1 WHERE a = 1;

If a row is inserted as a new record, the value of the affected row is 1. If the original record is updated, the value of the affected row is 2.

NOTE: If Column B is also a unique column, INSERT is equivalent to this UPDATE statement:

Mysql>? UPDATE table SET c = c + 1 WHERE a = 1 OR B = 2 LIMIT 1;

If a = 1 OR B = 2 matches multiple rows, only one row is updated. Generally, you should avoid using the on duplicate key clause for tables with multiple unique keywords.

You can use the VALUES (col_name) function from INSERT... The INSERT part of the UPDATE statement references the column value.

In other words, if no duplicate keyword conflict occurs, the VALUES (col_name) in the UPDATE clause can reference the inserted

The value of col_name. This function is particularly applicable to multiline inserts. The VALUES () function is only used in INSERT... The UPDATE statement makes sense. In other cases

Returns NULL.

Mysql> insert into table (a, B, c) VALUES (1, 2, 3), (4, 5, 6)

-> On duplicate key update c = VALUES (a) + VALUES (B );

This statement serves the same purpose as the following two statements:

Mysql> insert into table (a, B, c) VALUES (1, 2, 3)

-> On duplicate key update c = 3;

Mysql> insert into table (a, B, c) VALUES (4, 5, 6)

-> On duplicate key update c = 9;

When you use on duplicate key update, the DELAYED option is ignored.

Example :?This example is used in actual projects: It is used to import data from a table to another table, so data duplication must be considered (as follows ).

Unique index: email

Insert? Table_name1 (title, first_name, last_name, email, phone, user_id

, Role_id, status, campaign_id)

SELECT ?",",", Table_name2.email, table_name2.phone, NULL, NULL, 'Pending', 29

? FROM? Table_name2?

WHERE? Table_name2.status = 1?

On duplicate key update? Table_name1.status = 'Pending'

The key points of the statement are highlighted ~

Add another example:

Insert into class select * from class1

On duplicate key update class. course = class1.course

Other key: DELAYED? As a fast insert, it is not very concerned about the invalidation and improves the insert performance.

IGNORE? Only the records corresponding to the primary key do not exist. If no record exists, the record is added. If yes, the record is ignored.

For more information, see :?? Http://dev.mysql.com/doc/refman/5.1/zh/sql-syntax.html#insert

?Note:In MYSQL, the UNIQUE index will invalidate the null field, that is, (a UNIQUE index is created on field ):

Insert into test (a) values (null)

Insert into test (a) values (null)

Yes, it can be inserted repeatedly (the same is true for the union of unique indexes ).

REF:

SQL insert duplicate records, query first, and then insert

Http://segmentfault.com/q/1010000000260844

MySQL "replace"

Note: In the MS architecture, because it is first deleted and then added, will cause the MS autoId is inconsistent between the two sides.

Http://blog.xupeng.me/2013/10/11/mysql-replace-into-trap/

Original article address: Why does MySQLdb fail to insert data? Insert data failed using MySQLdb ?, Thanks to the original author.