Python uses mysqldb for Python to manipulate the database tutorial _python

This article details the python use of mysqldb for Python operation database method, share for everyone reference. Specifically as follows:

Generally speaking, the site is to interact with the database, otherwise do not have to do anything. Today we are going to analyze a library called MYSQLDB, which is used to interact with the MySQL database.

You can get this library from here:

Http://sourceforge.net/projects/mysql-python

If you're not sure if you have this library in your Python environment, open the Python shell, enter import mysqldb, and if you return an error message, you're not on your machine, so just download it. My machine is Win XP, So I downloaded the win environment under the EXE that, directly double-click to complete the installation.

Before we introduce specific operations, let's talk about how a program interacts with a database:

1. Establish a connection with the database
2. Execute SQL statement, receive return value
3. Close the database connection

Use MYSQLDB to follow the steps above. Let's do it step-by-step.

First, the introduction of MYSQLDB library:

Copy Code code as follows:

Import MySQLdb

Second, establish a connection with the database:

Copy Code code as follows:

Conn=mysqldb.connect (host= "localhost", user= "root", passwd= "sa", db= "MyTable")

The Connect method provided is used to establish a connection with the database, receive several parameters, and return the connection object.

The more commonly used parameters include:

Host: Database host name. The default is to use the local host.
User: Database login name. The default is the current user.
PASSWD: The Secret of database login. Null default.
DB: The name of the database to use. There is no default value.
The TCP port used by the Port:mysql service. The default is 3306.

More information on the parameters can be found here:

Http://mysql-python.sourceforge.net/MySQLdb.html

Then, this connection object also provides support for transaction operations, standard methods:

Commit () Commit
Rollback () rollback

Execute SQL statements and receive return values:

Copy Code code as follows:

Cursor=conn.cursor ()
N=cursor.execute (Sql,param)

First, we use the connection object to get a cursor object, and then we use the method provided by cursor to work. These methods include two main categories: 1. Execute command, 2. Receive return value

Cursor the method used to execute the command:

Callproc (self, procname, args): Used to execute stored procedures, the received parameters are stored procedure names and parameter lists, and the return value is the number of rows affected
Execute (Self, query, args): Executes a single SQL statement, receives the parameter is the SQL statement itself and uses the parameter list, returns the value is the number of rows affected
Executemany (self, Query, args): Executes a singled out SQL statement, but repeats the arguments in the parameter list, returning the value to the number of rows affected
Nextset (self): moving to the next result set

Cursor the method used to receive the return value:

Fetchall (self): receives all returned result rows.
Fetchmany (self, Size=none): Receives the size bar to return the result row. If the value of size is greater than the number of result rows returned, the Cursor.arraysize bar data is returned.
Fetchone (self): Returns a result row.
Scroll (self, value, mode= ' relative '): Moves the pointer to a row. If mode= ' relative ', it means moving the value bar from the current row, if mode= ' absolute ', Indicates that the value bar is moved from the first row of the result set.

The following code is a complete example:

Copy Code code as follows:

#使用sql语句, the parameters to be received here are in the%s placeholder. Note that regardless of the type of data you want to insert, placeholders will always be in%s
Sql= "INSERT into cdinfo values (%s,%s,%s,%s,%s)"
#param应该为tuple或者list
Param= (Title,singer,imgurl,url,alpha)
#执行, if successful, the value of n is 1
N=cursor.execute (Sql,param)

#再来执行一个查询的操作
Cursor.execute ("SELECT * from Cdinfo")
#我们使用了fetchall这个方法. In this way, the CDs will be saved all the results returned by the query. Each result is a tuple type of data, and these tuple form a tuple
Cds=cursor.fetchall ()
#因为是tuple, so you can use the result set
Print Cds[0][3]
#或者直接显示出来, see what the result set looks like.
Print CDs

#如果需要批量的插入数据, just do it.
Sql= "INSERT into cdinfo values (0,%s,%s,%s,%s,%s)"
#每个值的集合为一个tuple, the whole set of parameters consists of a tuple, or list
Param= ((Title,singer,imgurl,url,alpha), (TITLE2,SINGER2,IMGURL2,URL2,ALPHA2))
#使用executemany方法来批量的插入数据. It's a really cool way to do it!
N=cursor.executemany (Sql,param)

Note that (or I'm surprised), after the insert or delete or modify operation, you need to call the Conn.commit () method to commit. This way, the data is actually saved in the database. I don't know if it's my MySQL setup problem, anyway, When I started using it today, the data would not remain in the database without a commit, but the data did stay in the database. Because the AutoNumber is cumulative and the number of affected rows returned is not 0.

Close the database connection:

You need to close the pointer object and the connection object separately. They have the same method of name:

Copy Code code as follows:

Cursor.close ()
Conn.close ()

Three steps to complete, the basic database operation is the case. Here are two useful connections:

MySQLdb User's Guide: http://mysql-python.sourceforge.net/MySQLdb.html
MYSQLDB Documents: http://mysql-python.sourceforge.net/MySQLdb-1.2.2/public/MySQLdb-module.html

Now let me have a headache problem is the encoding of characters, when inserting Chinese, always garbled. I tried to change the encoding, and the "Data Too long for column" error occurred. In this respect, you can refer to the previous article Python MySQLdb the solution of garbled.

I hope this article will help you with your Python programming.