Using Python to operate MySQL database under Linux

installing Mysql-python

1. Download Mysql-python

Open Terminal:

Cd/usr/local

sudo wget http://nchc.dl.sourceforge.net/sourceforge/mysql-python/MySQL-python-1.2.2.tar.gz

Website address: http://sourceforge.net/projects/mysql-python/

2. Decompression

sudo tar-zxvf mysql-python-1.2.2.tar.gz

CD mysql-python-1.2.2

3, before the installation needs to be configured

A, modify the setup_posix.py in the Mysql_config.path for your MySQL installation directory mysql_config path

b, modify Threadsafe = False in site.cfg, remove the comment before Mysql_config, and change to Mysql_config =/usr/local/mysql/bin/mysql_config

C, execute the command:

Export Ld_library_path=/usr/local/mysql/lib/mysql

sudo ln-s/usr/local/mysql/lib/mysql/libmysqlclient.so/usr/lib/libmysqlclient.so.14

sudo ldconfig (this is important, otherwise an error will be followed Importerror:libmysqlclient.so.14:cannot open shared object file)

4. Compile and install

1) Python setup.py build

If this is not successful, you need to manually install the Setuptools:

sudo apt-get install Python-setuptools
2) sudo python setup.py install

5. Testing

Python

>>> Import MySQLdb

Without errors, the installation was successful.

using Python to manipulate MySQL

Use Python to connect to MySQL, create a database, create tables, insert/query data. The python_mysql.py code is as follows:

#!/usr/bin/python#Coding:utf-8ImportMySQLdb#ConnectionCxN = MySQLdb.connect (host ='127.0.0.1', user ='Root', passwd ='Root')#CursorsCur =cxn.cursor ()Try: Cur.execute ("DROP DATABASE pytest")exceptException as E:Print(e)finally:    Pass#Create a databaseCur.execute ("CREATE DATABASE pytest") Cur.execute ("Use pytest")#Create a tableCur.execute ("CREATE TABLE users (id INT, name VARCHAR (8))")#InsertCur.execute ("INSERT into Users VALUES (1, ' Tina '), (2, ' Tom '), (3, ' Tony '), (4, ' Sala ' )")#EnquiryCur.execute ("SELECT * from Users") forRowinchCur.fetchall ():Print('%s\t%s'%row)#Closecur.close () cxn.commit () Cxn.close ( )

Effect

If there is a warning similar to this:

/USR/LOCAL/LIB/PYTHON2.6/DIST-PACKAGES/MYSQL_PYTHON-1.2.2-PY2.6-LINUX-I686.EGG/MYSQLDB/__INIT__.PY:34: Deprecationwarning:the sets module is deprecated from sets import Immutableset

Locate the __init__.py file under the path mysqldb above
1) in the file "__init__.py", Commented out:
from sets import Immutableset
class Dbapiset (Immutableset):

class Dbapiset (Frozenset):

2) in the file "converters.py", comment out the from sets import Baseset, Set this sentence.

3) in the file "converters.py", modify the "set" to become "set" (only two places need to be modified, that is, uppercase to lowercase )
Probably line 45:return set ([i-I in S.split (', ') if I]) is changed to return Set ([i-I in S.split (', ') if I])
Probably line 129:set:set2str, instead of SET:SET2STR,

Related information of MySQLdb

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

1. Introduction of MYSQLDB Library

Import MySQLdb

2. Establish a connection to 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 to the database, receive several parameters, and return the connection object.

The more commonly used parameters include

Host: the database hostname. By default, the local host is used.

User: Database login name. The default is the current user.

PASSWD: The Secret of database landing. Default is empty.

DB: The name of the database to use. No default value.

The TCP port used by the Port:mysql service. The default is 3306.

CharSet: Database encoding.

The connection object is then provided with support for transactional operations, and the standard method

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 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)

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 ()