MySQLdb under Python is used

MySQLdb under Python is used

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 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.
#使用sql语句, the parameters to be received here are in the%s placeholder. Note that no matter what type of data you want to insert, the placeholder will always use%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

#如果需要批量的插入数据, just 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方法来批量的插入数据. This is a really cool way!
N=cursor.executemany (Sql,param)

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)

1. #encoding =utf-8
2. Import Sys
3. Import MySQLdb
5. Reload (SYS)
6. Sys.setdefaultencoding (' Utf-8 ')
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

MySQLdb under Python is used