Python's approach to using MySQL database and an example

Usage Environment: Windows+python3.4+mysql5.5+navicat

First, create a connection

1. Prepare to use Python to operate MySQL, first need to install Mysql-python package, under Python 3.x, the package has been renamed to Mysqlclient. You can install it by using the Pip method:

Pip Install Mysqlclient

or download the package file and install it.

2.Python the process of using MySQL:

3. Start the MySQL server: Start "cmd" as an administrator and enter the command: ' net start MySQL '

Use MySQL import method in Python: Importing MySQLdb

4. Create connection

Connection: Creates a network path between the Python client and the database. His parameters are as follows

Name of parameter	Type	Description
Host	String	Server address for MySQL
Port	Int	The port number of MySQL
User	String	User name
passwd	String	Password
Db	String	The database used
CharSet	String	Connection Character Set

Connection Supported methods:

Method name	Description
Cursor ()	Create and return cursors
Commit ()	Commit the current thing
Rollback ()	Roll back the current thing R ()
Close ()	Close connection

5. Get the cursor.

Cursor: Cursors object, used to execute queries and get results, which supports the following methods:

Method name	Description
Execute ()	Query commands for executing a database
Fetchone ()	Get the next row in the result set
Fetchmany (size)	Get the next (size) row in the result set
Fetchall ()	Get all remaining rows in the result set
RowCount	Last execute returns the number of data/rows affected
Close ()	Close Cursors

Here we create an instance in Python:

ImportMysqldbconn=mysqldb.connect (host='127.0.0.1', port=3306,user='Root', passwd='199331', db='Test', charset='UTF8') Cursor=conn.cursor ()Print(conn)Print(cursor) cursor.close () conn.close ()

The result of running the program is as follows:

From the results we can see that a connection and cursor object was created successfully.

Second, establish the database, do some simple operation

1. Simply create a ' user ' table and insert some data. There are only two fields in the user table: UserID and username. The code is as follows:

ImportMysqldbconn=mysqldb.connect (host='127.0.0.1', port=3306,user='Root', passwd='199331', db='Test', charset='UTF8') cur=conn.cursor () Cur.execute ("""CREATE table if not EXISTS user (userid int (one) PRIMARY KEY, username VARCHAR )""") forIinchRange (1,10): Cur.execute ("INSERT INTO User (Userid,username) values ('%d ', '%s ')"% (int (i),'name'+str (i))) Conn.commit () Cur.close () Conn.close ( )

We open the database with Navicat and look at the results, we can see that the table was created successfully, and 10 data was inserted.

2. Let's take a look at some of the methods inside the cursor.

Execute () method: Executes SQL to fetch a result from the database to the client

Fetch* () Method: Move the RowNumber and return the data.

For example, we have the following code:

Sql= ' select * from user ' cursor.execute (SQL) print (cursor.rowcount) rs=cursor.fetchone () print (RS) rs=cursor.fetchmany (3) Print (RS)

Rs=cursor.fetchall ()
Print (rs)

The results are as follows:

We can see that after all the data is executed, ROWCOUNT is 10.

After executing the Fetchone () method, a data is returned, 3 data is returned after the execution of Fetchmany (3), and all remaining data is returned after executing fetchall ().

Again, the following code:

Res=cursor.fetchall () for row in res:    print (' userid=%s,userna=%s '%row)

The result of this is:

3. Described above is the database of the frequently mentioned select operation, the following we describe the data update, namely: INSERT, UPDATE, delete operation. It is important to note that in this part of the operation is to be aware of whether the data is abnormal, if the data is not an exception, we can directly use commit () to commit (note: If you do not use commit, the database will not have any changes). However, if an exception occurs, then a rollback is required for a long time using rollback ().

3.1 Let's look at an example of a normal commit without exception:

sql_insert= ' INSERT into user (Userid,username) VALUES ("Name10") ' sql_update= ' update user set username= ' name91 ' where Userid=9 ' sql_delete= ' delete from user where userid=3 ' cursor.execute (sql_insert) print (Cursor.rowcount) Cursor.execute (sql_update) print (Cursor.rowcount) cursor.execute (sql_delete) print (Cursor.rowcount)

Conn.commit ()

The above action is: Add a (Ten, ' Name10 ') of the data, the userid=9 username modified to ' name91 ', delete the userid=3 data, after executing the above code we will use Navicat to view the data:

From the results you can see that the code is performing properly.

3.2. Take a look at an unusual data

sql_insert= ' INSERT into user (Userid,username) VALUES ("Name10") ' sql_update= ' update user set username= ' name91 ' where Userid=9 ' # sql_delete= ' delete from user where userid=3 ' # # # #errorsql_delete = ' Delete from user where useri=3 ' try:    Cursor.execute (Sql_insert)    print (Cursor.rowcount)    cursor.execute (sql_update)    print (Cursor.rowcount )    Cursor.execute (sql_delete)    print (cursor.rowcount) except Exception as E:    print (e)    Conn.rollback ( )

The insert is the same as the update operation, except that the UserID field in the delete is incorrectly written as Useri, which executes the code:

You can see that the exception is displayed, so let's look at the database data:

There is no change in the data. This is the role of rollback ()

Therefore, we later write add and remove changes to check the operation, it is best to put the operation into a try control block, to avoid some unnecessary errors.

Python's approach to using MySQL database and an example