Python USES the mysqldb connection database operation method example

# -*- coding: utf-8 -*-     
#mysqldb    
import time, MySQLdb    

# The connection     
conn=MySQLdb.connect(host="localhost",user="root",passwd="",db="test",charset="utf8")  
cursor = conn.cursor()    

# write     
sql = "insert into user(name,created) values(%s,%s)"   
param = ("aaa",int(time.time()))    
n = cursor.execute(sql,param)    
print n    

# update     
sql = "update user set name=%s where id=3"   
param = ("bbb")    
n = cursor.execute(sql,param)    
print n    

# The query     
n = cursor.execute("select * from user")    
for row in cursor.fetchall():    
    for r in row:    
        print r    

# delete     
sql = "delete from user where name=%s"   
param =("aaa")    
n = cursor.execute(sql,param)    
print n    
cursor.close()    

# Shut down     
conn.close()
 Copy the code 

The basic use as above, or very simple, further use has not yet operated, from the Internet to find some information put up, in order to follow up to see

1. Introduce the MySQLdb library

The import MySQLdb

2. Establish a connection to the database
Conn = MySQLdb. Connect (host = "localhost", user = "root", the passwd = "sa", the db = "mytable", charset = "utf8")
The connect method is provided to establish a connection to the database, receive several parameters, and return the connection object.

More commonly used parameters include
Host: database hostname. The default is to use localhost.
User: database login name. The default is the current user.
Passwd: database login secret. Default is empty.
Db: database name to use. No default value.
Port: the TCP port used by the MySQL service. The default is 3306.
Charset: database encoding.

More information about parameters can be found here
http://mysql-python.sourceforge.net/MySQLdb.html

Then, this connection object also provides support for transaction operations, the standard method
Commit ()
Rollback () rolled back

3. Execute the SQL statement and receive the return value
Cursor = conn. Cursor ()
N = cursor. The execute (SQL, param)
First, we obtain a cursor object by using the connection object, and then we use the methods provided by cursor to do our work

Methods used to execute commands with cursor:
Callproc (self, procname, args): used to execute the stored procedure. The parameters received are the stored procedure name and parameter list, and the return value is the number of affected rows
Execute (self, query, args): executes a single SQL statement, takes the parameters of the SQL statement itself and the list of parameters used, and returns the number of affected rows
Executemany (self, query, args): executes a single SQL statement, but repeats the arguments in the argument list, returning the number of affected rows
Nextset (self): moves to the next result set

Method used to receive return values with cursor:
Fetchall (self): receives all returned rows.
Fetchmany (self, size=None): receives a size bar to return a 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', moves the value bar from the current row, and if mode='absolute', moves the value bar from the first row of the result set.

The following code is a complete example.
Use SQL statements where the parameters to be received are placeholders for %s. Note that placeholders are always %s no matter what type of data you are inserting
SQL = "insert into cdinfo values (% s, % s, % s, % s, % s)"
#param should be a tuple or a list
Param = (title, singer, imgurl, url, alpha)
# execute. If successful, the value of n is 1
N = cursor. The execute (SQL, param)

Execute one more query
Cursor. The execute (" select * from cdinfo ")
We used the fetchall method, so that all the results returned from the query will be stored in the CDS. Each result is a tuple type of data, and these tuples form a tuple
CDS = cursor. Fetchall ()
# because it's a tuple, you can use the result set this way
Print CDS [0] [3]
Or simply display it and see what the result set really looks like
Print CDS

If you need to insert data in batches, do so
SQL = "insert into cdinfo values (0, % s, % s, % s, % s, % s)"
Each set of values is a tuple, and the entire set of arguments is a tuple, or list
Param = ((title, singer, imgurl, url, alpha), (title2, singer2 imgurl2, url2, alpha2))
Use the executemany method to insert data in bulk. This is a really cool method!
N = cursor. Executemany (SQL, param)

Close the database connection
Need to close the pointer object and connect the object separately. They have the same name method
Cursor. The close ()
Conn. Close ()

Four steps complete, and that's all for basic database operations. 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 coding (to prevent confusion)

Points to note:

  1 Python file set encoding utf-8 (#encoding=utf-8)
  2 MySQL database charset=utf-8
  3 Python connection to MySQL is with the parameter charset=utf8
  4 set the default coding of Python to utf-8 (sys.setdefaultencoding(utf-8))

    #encoding=utf-8 
     import sys 
     import MySQLdb 

     reload(sys) 
     sys.setdefaultencoding('utf-8') 

     db=MySQLdb.connect(user='root',charset='utf8') 

Note: the MySQL configuration file Settings must also be configured as utf8

Set the my.cnf file of MySQL, and set the default character set in the [client]/[mysqld] section (usually /etc/mysq/my.cnf) :
[client]
Default - character - set = utf8
[mysqld]
Default - character - set = utf8