Example analysis of MYSQLDB usage under Python

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

Download and install MySQLdb

①linux version

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

②windows version

Online Search for 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 does not know sets this module, but has added the set built-in functions. Locate the __init__.py in the MySQLdb folder, comment out the from sets import Immutableset class Dbapiset (immutableset): Add Class Dbapiset (Frozenset): Find converters.py comment out from sets import Baseset, Set. Then modify the set in line 45th and row 129 to set.

Get.

Here's how to start the demo:

Python code

#-*-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 (sql,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 as above, or is very simple, further use has not yet operated, first from the Internet to find some information to put up, for follow-up view

1. Introduction of MYSQLDB Library
Copy the Code code as follows:

Import MySQLdb

2. Establish a connection to the database
Copy the Code code 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 to the database, receive several parameters, and return the connection object.

The more commonly used parameters include
Host: the database hostname. By default, the local host is used.
User: Database login name. The default is the current user.
PASSWD: The Secret of database landing. Default is empty.
DB: The name of the database to use. No default value.
The TCP port used by the Port:mysql service. The default is 3306.
CharSet: Database encoding.

More information on the parameters can be found here.
Http://mysql-python.sourceforge.net/MySQLdb.html

The connection object is then provided with support for transactional operations, and the standard method
Commit () Commit
Rollback () rollback

3. Execute SQL statements and receive return values

Copy the 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 the cursor to do the work. These methods include two main classes: 1. Execute command, 2. Receive return value

Cursor the method used to execute the command:

Callproc (self, procname, args): Used to execute stored procedure, received parameter is stored procedure name and parameter list, return value is the number of rows affected

Execute (Self, query, args): Executes a single SQL statement, receives the parameters for the SQL statement itself and the parameter list used, and returns the number of rows affected

Executemany (self, Query, args): Executes a single SQL statement, but repeats the parameters in the list of parameters, with the returned value being the number of rows affected

Nextset (self): move to the next result set

The cursor is used to receive the return value of the method:

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

The following code is a complete example.

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 this. Here are two useful connections
MYSQLDB User guide: http://mysql-python.sourceforge.net/MySQLdb.html
MySQLdb Document: http://mysql-python.sourceforge.net/MySQLdb-1.2.2/public/MySQLdb-module.html

5 encoding (prevents garbled)

Points to note:

1 python file Set encoding utf-8 (file front plus #encoding =utf-8)
2 MySQL Database charset=utf-8
3 python connection mysql is plus parameter Charset=utf8
4 Set Python's default encoding to Utf-8 (sys.setdefaultencoding (Utf-8)

Note: The configuration file settings for MySQL must also be configured as UTF8

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

 
  [client]default-character-set = Utf8[mysqld]default-character-set = UTF8
 
  

Hopefully this article will help you with Python programming.

