Use mysqldb in Python

Use mysqldb in Python

18:39:02

Label: Python
Mysqldb
MySQL

Database
Leisure

Download and install mysqldb

<1> Linux version

Http://sourceforge.net/projects/mysql-python/ download, installation is to install setuptools first, and then under the download file directory, modify mysite. cfg, specify the local MySQL mysql-config file path

<2> Windows

Find a http://www.technicalbard.com/files/MySQL-python-1.2.2.win32-py2.6.exe online

After installation, import mysqldb will receive a warning like deprecationwarning: The sets module is deprecated, Google

The reason is that 2.6 does not know the sets module, but the set built-in function has been added. Find _ init _ in the mysqldb folder __. PY, comment out from sets import immutableset class dbapiset (immutableset): Add class dbapiset (frozenset):; find converters. PY comment out from sets import baseset, set. Then, set the values of rows 45th and 129 to set.

Done.

The following demo is started:

Python code

 
 

  
  
#-*-Coding: UTF-8 -*-
  
  
# Mysqldb
  
  
Import time, mysqldb
  
  

  
  
# 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.exe cute (SQL, Param)
  
  
Print n
  
  

  
  
# Update
  
  
SQL = "update user set name = % s where id = 3"
  
  
Param = ("BBB ")
  
  
N = cursor.exe cute (SQL, Param)
  
  
Print n
  
  

  
  
# Query
  
  
N = cursor.exe cute ("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.exe cute (SQL, Param)
  
  
Print n
  
  
Cursor. Close ()
  
  

  
  
# Disable
  
  
Conn. Close ()
 
 

 

The basic usage is as simple as above. For further use, you have not performed any operations. First, you can find some information on the Internet for future reference.

1. Introduce 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 is used to establish a connection with the database, receive several parameters, and return the connection object.

Common parameters include
HOST: specifies the Database Host name. The local host is used by default.
User: Database login name. The default value is the current user.
Passwd: Password for database login. Empty by default.
DB: name of the database to be used. No default value exists.
Port: the TCP port used by the MySQL service. The default value is 3306.
Charset: Database encoding.

For more information about the parameters, see here.
Http://mysql-python.sourceforge.net/MySQLdb.html

Then, this connection object also provides support for transaction operations, standard methods
Commit () Submit
Rollback () rollback

3. Execute SQL statements and receive return values 
Cursor = conn. cursor ()
N=cursor.exe cute (SQL, Param)
First, we use the connection object to obtain a cursor object. Next, we will use the method provided by cursor to work. These methods include: 1. execute commands; 2. Receive returned values.

Cursor is used to execute commands:
Callproc (self, procname, argS): used to execute a stored procedure. The received parameters are the stored procedure name and parameter list. The returned values are the number of affected rows.
Execute (self, query, argS): executes a single SQL statement. The received parameters are the SQL statement itself and the list of parameters used. The returned values are the affected rows.
Executemany (self, query, argS): executes a single SQL statement, but repeats the parameters in the parameter list. The returned value is the number of affected rows.
Nextset (Self): Move to the next result set

Cursor is used to receive the returned value:
Fetchall (Self): receives all returned result rows.
Fetchmany (self, size = none): receives the size of returned results rows. If the size value is greater than the number of returned results rows, the returned cursor. arraysize data is returned.
Fetchone (Self): returns a result line.
Scroll (self, value, mode = 'relative '): Move the pointer to a row. if mode = 'relative ', the value bar is moved from the current row. If mode = 'absolute', the value bar is moved from the first row of the result set.

The following code is a complete example.
# When using SQL statements, the parameters to be received here use 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 )"
# Param should be tuple or list
Param = (title, singer, imgurl, URL, alpha)
# Execution. If the execution succeeds, the value of N is 1.
N=cursor.exe cute (SQL, Param)

# Execute another query operation
Cursor.exe cute ("select * From cdinfo ")
# We use the fetchall method. In this way, all results returned by the query will be saved in the CDs. Each result is a tuple-type data, which forms a tuple.
CDS = cursor. fetchall ()
# Because it is a tuple, you can use the result set in this way.
Print CDs [0] [3]
# Or directly display the result set to see how it looks.
Print CDs

# If you want to insert data in batches, do this.
SQL = "insert into cdinfo values (0, % s )"
# The set of each value is a tuple, and the entire parameter set is a tuple or list
Param = (title, singer, imgurl, URL, alpha), (title2, singer2, imgurl2, url2, alpha2 ))
# Use the executeplugin method to insert data in batches. This is a really cool method!
N=cursor.exe cute.pdf (SQL, Param)

4. Close the database connection 
You need to close the pointer object and connection object respectively. They have the same name.
Cursor. Close ()
Conn. Close ()

The following are two useful connections.
Mysqldb User Guide: http://mysql-python.sourceforge.net/MySQLdb.html
Mysqldb documentation: http://mysql-python.sourceforge.net/MySQLdb-1.2.2/public/MySQLdb-module.html

5 encoding (prevent garbled characters)

Notes:

1. encode the python file with UTF-8
(Add # encoding = UTF-8 before the file)
2 MySQLThe database charset = UTF-8
3 PythonConnect to MySQL with the charset = utf8 Parameter
4Set the default encoding 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: MySQL Configuration File Settings must also be configured as utf8

Set the MySQL my. CNF file. In the [client]/[mysqld] section, set the default character set (usually in/etc/MySQL/My. CNF ):

[Client]
Default-character-set = utf8
[Mysqld]
Default-character-set = utf8