Python uses the MYSQLDB Connection database operation Method sample detailed explanation _python

Copy Code code as follows:

#-*-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 ()
Copy Code

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

Import MySQLdb

2. Establish a connection with 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 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
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.
#使用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)

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)

Copy Code code as follows:

#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:
[Client]
Default-character-set = UTF8
[Mysqld]
Default-character-set = UTF8