Python's approach to MySQL in multiple environments

Multi-Environment Toss Python-mysql

Recently in the toss write a test case scheduling execution of the script, and MySQL will have some interaction. Developing tests under a Mac is OK, but porting to the server has found too many problems.

Installed on the server on CentOS 5 mysqldb encountered such a pit, tossing all afternoon. Of course, MYSQLDB should be the more common external lib of Python, like the Django project that uses MySQLdb.

In summary, MYSQLDB is using C module to link MySQL, all will need to have the following prerequisites:

C Compiler
Python's development library and its header files
MySQL's development library and its header files

All, the corresponding successful loading mysqldb, please make the following confirmation:

Confirm Python version 2.3 ~ 2.7
Confirm that you have GCC installed
Confirm the MySQL is installed
Confirm that the Python-devel is installed
If you are compiling the installation locally, you must confirm that you have already installed the Setuptools

In short: installation process, encountered problems, basic online can find solutions, after all, the use of this module has been a number of versions of the baptism. is a very mature solution.

However, I do not recommend that you continue to use it. In the process of solving the above problems, I found that the MySQL official has introduced a new solution. Mysql

Connector/python

The most attractive part of the product is that it basically supports all the features that are included with MySQL server and supports Python 2/3. (Note that it is not supported for MySQL's old encryption verification, so MySQL 4.1 is not supported). There is the official recommendation, the follow-up maintenance is certainly the most timely.

and switching from MySQLdb to Connector/python is basically seamless. The commonly used function basically has nothing to change
Connector/python example

Concrete can refer to: http://dev.mysql.com/doc/connector-python/en/ connector-python-examples.html

Connect:

Import mysql.connector From mysql.connector import errorcode Try:     cnx  = mysql.connector.connect (user= ' Scott ',  password= ' Tiger ',                                    host= ' 127.0.0.1 ',                                    database= ' employees ') except mysql.connector.error  as err:     if err.errno == errorcode. Er_access_denied_error:         print ("Something is wrong  with your user name or password ")          elif err.errno == errorcoDe. Er_bad_db_error:         print ("Database does not exist") )      else:         print (Err) Else:      cnx.close ()

CREATE table

Import Mysql.connector from
mysql.connector import errorcode
cnx = Mysql.connector.connect (user= ' Scott ')
cursor = cnx.cursor ()
try:
cursor.execute ("CREATE DATABASE {} DEFAULT CHARACTER SET ' UTF8 '". Format (Db_na ME)
except Mysql.connector.Error as err:
print ("Failed Creating database: {}". Format (ERR))
exit (1)

Query

Import mysql.connector
conn = mysql.connector.connect (host= ' 127.0.0.1 ', user= ' root ', password= ' root ', database= ') Athena ')
cursor = conn.cursor (dictionary=true)
try:
cursor.execute (sql)
rows = Cursor.fetchall () 
  finally:
conn.close ()