Pyhton working with MySQL database

MySQL Database interface program: https://sourceforge.net/projects/mysql-python/

The approximate steps for manipulating the database are as follows:

1: First create a database of the connection object connection;

2: The cursor object is then created with the connection object;

3: Manipulate the database with the cursor object (for example: adding and deleting);

4: Last COMMIT TRANSACTION or ROLLBACK TRANSACTION.

The Connection object connection has the following methods:

Cursor ()-------------------------------Create and return cursors

Commit ()-------------------------------commit the current transaction

Rollback ()-------------------------------Roll back the current transaction

Close ()--------------------------------closing the connection

Cursor objects cursors have the following methods:

Execute ()---------------------------------execute database queries and commands

Fetchone ()---------------------------------get the next row of the result set

Fetchmany ()---------------------------------get the next few lines of the result set

Fetchall ()---------------------------------get all the rows left in the result set

RowCount---------------------------------last Execute () returns the number of rows of data or affects the number of rows

Close ()---------------------------------closing the cursor object

COMMIT TRANSACTION: Once you have manipulated the database (adding and removing changes), you must commit the transaction, otherwise your previous operation will not have the result.

To Roll back a transaction: If an exception occurs during the operation of the database, you can rollback the transaction to restore it to a database that is not operational.

The following code is used to deepen understanding:

1 #-*-coding:utf-8-*-2 ImportMySQLdb3 4Con =MySQLdb.connect (5Host ="Your database host address",6user ="Your database user name",7passwd ="Your database Password",8db ="Your database name",9Port = 3306,TenCharaet ="Encoding Type" One  A ) -  -Cur =con.cursor ()

The connection object is created above and then the cursor object is created with the connection object, and steps 1 and 2 are completed. The database is then manipulated, which is the data for a table (user) of my database:

Then the code:

1sql ="SELECT * from user"2 cur.execute (SQL)3  4 PrintCur.rowcount5 6 PrintCur.fetchone ()7 8 PrintCur.fetchmany (1)9 Ten PrintCur.fetchall () One  A cur.close () -Con.close ()

The printing results are as follows:

3
(U ' name1 ', U ' 1 ')
(U ' name2 ', U ' 2 '),)
(U ' name3 ', U ' 3 '),)

I think the code is not clear, because some of the methods in the code above have been informed of the usefulness. Note: Close the cursor object and the connection object (with the Close () method) After you have finished the operation

Next operation, Increase:

1 ' inser into user (username, password) VALUES ("Name4", "4") ' 2 cur.execute (SQL) 3 4 cur.close () 5 Con.close ()

Run the above code to discover that the database does not increase the record (Autocommit is closed), then commit the transaction (with the Commit () method), in the third line of the code above add this code: Con.commit () and run the Code Discovery database added a record.

-------------------the next change and delete will not be demonstrated, change the SQL statement can be, then see the next ROLLBACK TRANSACTION-------------------------------

1Insert_sql ='INSERT into user (username, password) VALUES ("Name5", "5")'2Del_sql ="Delete from user where word= ' 5 '"3 Try:4 5 Cur.execute (insert_sql)6 7 Cur.execute (del_sql)8 9 Con.commit ()Ten except: One Con.rollback () A  - cur.close () -Con.close ()

The above code has two SQL statements, one is to add username=name5,password=5, one is to delete the Word field is equal to 5 of the record, but my table does not have word this field. So the added statement executes successfully, and the deleted statement fails. I put the execution of these two statements into the try block, all executed successfully commits the transaction, and once the execution fails, the error is caught and the transaction is rolled back. After running the code, it is found that the database is unchanged. Although the execution was successful, the transaction was rolled back when the deletion failed. So the rollback transaction is restored until the database is not operational (this is before the execution increment), so the data for the database is not changed.

Operating the database is not difficult, as long as you can SQL statements!

Pyhton working with MySQL database