Latest use of Python to operate MySQL full parse

1. To operate MySQL here via MySQLdb, first sudo apt-get install Libmysqlclient-dev, how to appear

withPackagewith MergeList /var/lib/apt/lists/dl.google.com_linux_chrome_deb_dists_stable_main_i18n_Translation-en%5packageorfilenotor opened.

If you also have the above error, use the following method to solve,

• sudo rm/var/lib/apt/lists/*-VF
• sudo apt-get update

Then sudo apt-get install Libmysqlclient-dev, installing the development environment.

sudo pip install Mysql-pythonInstall MySQL for python, how do I check if it is installed correctly? In the python environment, the import MYSQLDB does not display an error and the installation succeeds. As shown in,

2.python and MySQL interaction 2.1 Create a database, before creating, look at the existing database, if the various concepts in the MySQL database is not clear can refer to here

Using Python to create a database

#!/usr/bin/python#coding=utf-8import MySQLdbconn = MySQLdb.connect(host=‘localhost‘,user=‘root‘,passwd=‘123‘)curs = conn.cursor()curs.execute("create database fristDb")conn.close()

Looking at the database again, you can see our new database Fristdb.

2.2 Connect our new database Fristdb and view the version

#!/usr/bin/python#coding=utf-8import MySQLdbconn = MySQLdb.connect(host=‘localhost‘,user=‘root‘,passwd=‘123‘,db=‘fristDb‘)curs = conn.cursor()curs.execute("select version()")data = curs.fetchone()print"our database version: %s" % dataconn.close()

Output results

2.3 Creating a data table in the database Fristdb fristtable

#!/usr/bin/python#coding=utf-8import MySQLdbconn = MySQLdb.connect(host=‘localhost‘,user=‘root‘,passwd=‘123‘,db=‘fristDb‘)curs = conn.cursor()curs.execute("drop table if exists fristTable""""create table fristTable (first_name char(20) not null,last_name char(20),age int,sex char(1))"""curs.execute(sql)conn.close()

Review the data table again after executing the above code

2.4 Insert operation of database, view data in data table before inserting

#!/usr/bin/python#coding=utf-8import MySQLdbconn = MySQLdb.connect(host=‘localhost‘,user=‘root‘,passwd=‘123‘,db=‘fristDb‘"""insert into fristTable (first_name,last_name,age,sex) values (‘dragon‘,‘great‘,20,‘M‘)"""try:    curs.execute(sql)    conn.commit()except:    conn.rollback()conn.close()

3. Other operations of the database can be pushed on their own, basically this looks like 4. Add:

• Common parameters of the Connect function
• User #用户名
• Password #用户密码
• Host #主机名
• Database #数据库名
• The Connect function returns the connection object, the Connection object method
• Close () #关闭连接之后, the Connection object and its cursors are not available
• Commit () #如果支持的话就提交挂起的事务, or do nothing
• Rollback () #回滚挂起的事务 (may not be available)
• Cursor () #返回连接的游标对象

4.1 Cursor Object Methods

• Close () #关闭游标
• Execute (oper[,params]) #执行SQL操作, parameters may be used
• Executemany (OPER,PSEQ) #对序列中的每个参数执行SQL操作
• Fetchone () #把查询的结果集中的下一行保存为序列, or + None
• Fetchmany ([size]) #获取查询结果集中的多行, default is 1
• Fetchall () #将所有 (remaining) rows as sequences of sequences

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

#encoding=utf-8 import sys  import MySQLdb  reload(sys)  sys.setdefaultencoding(‘utf-8‘)  db=MySQLdb.connect(user=‘root‘,charset=‘utf8‘

Latest use of Python to operate MySQL full parse