Example analysis of MYSQLDB usage in Python

This example describes the use of mysqldb under Python. Share to everyone for your reference. The specific analysis is as follows:

Download Installation MySQLdb

①linux version

http://sourceforge.net/projects/mysql-python/download, the installation is to be installed first Setuptools, then in the download file directory, modify Mysite.cfg, Specifies the path to the local MySQL mysql-config file

②windows version

Online Search to a Http://www.technicalbard.com/files/MySQL-python-1.2.2.win32-py2.6.exe

After installation, import MySQLdb will appear deprecationwarning:the sets module is deprecated such a warning, Baidu

The reason is that 2.6 did not know sets this module, but the set built-in functions have been added. Locate the __init__.py in the MySQLdb folder and comment out the from sets import Immutableset class Dbapiset (immutableset): Add Class Dbapiset (Frozenset): Find converters.py comment out from sets import Baseset, Set. The set is then modified in rows 45th and 129.

Get.

Here's how to start the demo:

Python code

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28-29

#-*-Coding:utf-8-*-#mysqldb import time, MySQLdb #连接 conn=mysqldb.connect (host= "localhost", user= "root", passwd= "", DB = "Test", charset= "UTF8") cursor = conn.cursor () #写入 sql = "INSERT into user (name,created) VALUES (%s,%s)" param = ("AAA", int (Time.time ())) n = cursor.execute (sql,param) print n #更新 sql = "Update user set name=%s where id=3" param = ("bbb") n = cursor.execute (sq L,param) print n #查询 n = cursor.execute ("SELECT * from User") for row in Cursor.fetchall (): for R in Row:print r #删除 SQL = "Delete from user where name=%s" param = ("aaa") n = cursor.execute (sql,param) print n cursor.close () #关闭 conn.close ()

Basic use such as above, or very simple, further use has not been operated, first from the Internet to find some information to put up for follow-up review

1. Introduction of the MYSQLDB Library

The code is as follows:

Import MySQLdb

2. Establish a connection with the database

The code is as follows:

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

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.

CharSet: Database encoding.

More information about 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

3. Execute SQL statements and receive return values

The code is 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 single SQL statement, but repeats the parameters 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.

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20-21

#使用sql语句, the parameters to be received here are in the%s placeholder. Note that regardless of the type of data you are inserting, placeholders will always be used with%s sql= "insert into cdinfo values (%s,%s,%s,%s,%s)" # param should be tuple or list param= (Title,singer,imgurl,url,alpha) #执行, and 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 save all the results returned by the query. #每条结果都是一个tuple类型的数据, these tuple make up a tuple cds=cursor.fetchall () #因为是tuple, so you can use the result set print cds[0][3] #或者直接显示出来, Look at the true appearance of the result set the print CDs #如果需要批量的插入数据 and do sql= "insert into cdinfo values (0,%s,%s,%s,%s,%s)" #每个值的集合为一个tuple, The entire set of parameters consists of a tuple, or list param= (Title,singer,imgurl,url,alpha), (TITLE2,SINGER2,IMGURL2,URL2,ALPHA2) Use the Executemany method to bulk insert data. This is a really cool way! N=cursor.executemany (Sql,param)

4. Close the database connection

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

Cursor.close ()

Conn.close ()

Four 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

5 code (prevent garbled)

Points to note:

1 python file Set encoding Utf-8 (preceded by #encoding =utf-8)

2 MySQL Database charset=utf-8

3 python connection mysql is plus parameter Charset=utf8

4 Setting the default encoding for Python is Utf-8 (sys.setdefaultencoding (Utf-8)

?

1 2 3 4 5 6

#encoding =utf-8 Import sys import MYSQLDB reload (SYS) sys.setdefaultencoding (' Utf-8 ') db=mysqldb.connect (user= ' root '), charset= ' UTF8 ')

Note: MySQL configuration file settings must also be configured to UTF8

Set the my.cnf file for MySQL and set the default character set (usually in/etc/mysql/my.cnf) in the [Client]/[mysqld] section:

?

1 2 3 4 5 6

<!--[endif]--> [client] default-character-set = UTF8 [mysqld] default-character-set = UTF8 <!--[endif]-->

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