Python using MySQLdb to connect to a database how-to examples

#-*-Coding:utf-8-*-

#mysqldb
# site Www.jbxue.com
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

Import MySQLdb

2. Establish a connection to the database
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.

More commonly used parameters include  
host: The database hostname. The default is to use localhost.  
user: Database login name. The default is the current user.  
PASSWD: The Secret of database landing. Default is null.  
DB: The name of the database to use. No default value.  
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  
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 consist of 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 procedures, received parameters are 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 of the SQL statement itself and the parameter list used, the return value is the number of rows affected  
executemany (self, Query, args): Executes a single SQL statement, but repeats the parameters in the parameter list, the returned value is the number of rows affected  
nextset (self): move to the next result set

cursor method to receive the return value: 
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 bar 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, and if mode= ' absolute ', the value bar is moved from the first row of the result set.

#使用sql语句, The parameters to be received here are in the%s placeholder. Note that no matter what type of data you are inserting, the placeholder will always be%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 as the full result of the query return. Each result is a tuple-type data that consists of a tuple
Cds=cursor.fetchall ()
#因为是tuple, so you can use result sets like this
Print Cds[0][3]
#或者直接显示出来 to see what the result set looks like.
Print CDs

#如果需要批量的插入数据, do it  
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))  
#使用executemany方法来批量的插入数据. It's really A cool way to! 
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 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)

The code is as follows:
#encoding =utf-8
Import SYS
Import MySQLdb

Reload (SYS)
sys.setdefaultencoding (' utf-8 ')

Db=mysqldb.connect (user= ' root ', charset= ' UTF8 ')

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

Python using MySQLdb to connect to a database how-to examples