python 操作Mariadb 数据库

首先我使用的是Pycharm，在操作数据库之前需要先导入相关的模块。

这个包叫做MySQLdb，按照网上的教程进行安装。

先看看数据库内要查询的信息。

MariaDB [test]> select * from test.Userinfo;+----+-------+----------+| id | name  | password |+----+-------+----------+|  1 | Jack  | red123   ||  2 | Tom   | bule456  ||  3 | Jerry | cent789  |+----+-------+----------+3 rows in set (0.00 sec)

一个简单的查询实例。

#!/usr/bin/python# -*- coding: utf-8 -*-import MySQLdb# 连接数据库conn = MySQLdb.connect(host='127.0.0.1'                       ,user='root'                       ,passwd='123'                       ,db='test')# 获得一个游标cur = conn.cursor()# 执行SQL语句  (返回值是查询表中的行数，影响的行数)reCount = cur.execute('select * from Userinfo')# 获取数据库的信息data = cur.fetchall()# 关闭数据库conn.close()print 'The rows of this table is %d' %(reCount)print  data

执行结果：

The rows of this table is 3
((1L, 'Jack', 'red123'), (2L, 'Tom', 'bule456'), (3L, 'Jerry', 'cent789'))

其中的话fetchall() 是返回所有，而fetchone是返回一行数据。

程序可以根据查出来的结果，进行下一步操作。举个例子，假如是一个登陆系统。先判断有没有Jack这个人，再取出登录时提供的密码进行比对。

import MySQLdb# 连接数据库conn = MySQLdb.connect(host='127.0.0.1'                       ,user='root'                       ,passwd='123'                       ,db='test')# 获得一个游标cur = conn.cursor()# 执行SQL语句  (返回值是查询表中的行数)reCount = cur.execute('select * from Userinfo')# 获取数据库的信息data = cur.fetchall()user = 'Jack'password = 'red123'if user in [_[1] for _ in data]:    sql = "select password from Userinfo where name='%s'" %(user)    cur.execute(sql)    if cur.fetchone()[0] == password:        print 'login successful'    else:        print 'password error'else:    print 'no user named "%s"' %(user)# 关闭数据库conn.close()

数据库的插入操作：

import MySQLdbconn = MySQLdb.connect(host='localhost', \                     user='root', \                     passwd='123', \                     db='test')cur = conn.cursor()sql = 'insert into Userinfo (id,name,password) values(%s,%s,%s)'param = (4,'Laura','Xman007')try:    cur.execute(sql,param)    conn.commit()except:    conn.rollback()conn.close()

这里注意，sql里的%s是占空符，内容在param中，且无论是什么类型都是用%s。

同时利用异常来提交或者回滚。

其他的如删，创建库，授权，等等都是这么去做的。

还有一点，就是查询的时候用的是元祖。也可以以字典的形式去查询。

cur = conn.cursor(cursorclass= MySQLdb.cursors.DictCursor)

查出来的结果是

({'password': 'red123', 'id': 1L, 'name': 'Jack'}, {'password': 'bule456', 'id': 2L, 'name': 'Tom'}, {'password': 'cent789', 'id': 3L, 'name': 'Jerry'}, {'password': 'Xman007', 'id': 4L, 'name': 'Laura'})

还有一个就是要插入大量数据的时候可以使用 executemany函数。

import MySQLdbconn = MySQLdb.connect(host='localhost', \                     user='root', \                     passwd='123', \                     db='test')cur = conn.cursor()sql = 'insert into Userinfo (id,name,password) values(%s,%s,%s)'param = [('%d' %(i),'user_%d' %(i),'password_%d' %(i)) for i in xrange(5,11)]try:    cur.executemany(sql,param)    conn.commit()except:    conn.rollback()conn.close()

插入结果：

MariaDB [test]> select * from Userinfo;+----+---------+-------------+| id | name    | password    |+----+---------+-------------+|  1 | Jack    | red123      ||  2 | Tom     | bule456     ||  3 | Jerry   | cent789     ||  4 | Laura   | Xman007     ||  5 | user_5  | password_5  ||  6 | user_6  | password_6  ||  7 | user_7  | password_7  ||  8 | user_8  | password_8  ||  9 | user_9  | password_9  || 10 | user_10 | password_10 |+----+---------+-------------+10 rows in set (0.00 sec)

接着说一下查询时候的fetchall和fetchone，前面已经看到了，fetchall是拿出所有的数据，fetchone是拿出一条，其实和yield很像。

for i in xrange(reCount):    data = cur.fetchone()    print data

打印结果

(1L, 'Jack', 'red123')(2L, 'Tom', 'bule456')(3L, 'Jerry', 'cent789')(4L, 'Laura', 'Xman007')

绝对定位：

data = cur.fetchone()print datadata = cur.fetchone()print datadata = cur.fetchone()print datacur.scroll(0,mode='absolute')data = cur.fetchone()print data

结果是当查到第四个的时候，又回到第一个。

(1L, 'Jack', 'red123')(2L, 'Tom', 'bule456')(3L, 'Jerry', 'cent789')(1L, 'Jack', 'red123')

cur还有一个小属性就是lastrowid。可以获取自增数据现在自增到哪里了。

最后数据库的增删改查可以封装到一个类里面，最后好统一管理。

# -*- coding: utf-8 -*-import MySQLdbclass MySQL(object):    def __init__(self):        pass    def sql_init(self,db_info,mode='tuple'):        db_host = db_info.get("host")        db_user = db_info.get("user")        db_passwd = db_info.get("passwd")        db_name = db_info.get("db")        conn = MySQLdb.connect(host=db_host,user=db_user, \                               passwd=db_passwd,db=db_name)        if mode == 'tuple':            cur = conn.cursor()        else:            cur = conn.cursor(cursorclass=MySQLdb.cursors.DictCursor)        return conn,cur    @classmethod    def query_sql(cls,sql,mode='tuple',**db_info):        res = cls().sql_init(db_info,mode)        conn = res[0]        cur  = res[1]        try:            cur.execute(sql)            data = cur.fetchall()        except Exception:            data = None            print 'SQL ERROR!!'        conn.close()        return data    @classmethod    def insert_sql(cls,sql,param,**db_info):        res = cls().sql_init(db_info)        conn = res[0]        cur  = res[1]        try:            cur.executemany(sql,param)            conn.commit()            result = True        except:            conn.rollback()            result = False        conn.close()        return result    @classmethod    def delete_sql(cls,sql,param,**db_info):        res = cls().sql_init(db_info)        conn = res[0]        cur  = res[1]        try:            cur.execute(sql,param)            conn.commit()            result = True        except:            conn.rollback()            result = False        conn.close()        return result    @classmethod    def update_sql(cls, sql, param, **db_info):        res = cls().sql_init(db_info)        conn = res[0]        cur = res[1]        try:            cur.execute(sql, param)            conn.commit()            result = True        except:            conn.rollback()            result = False        conn.close()        return result    @classmethod    def do_mysql(cls,sql,**db_info):        """            其他任意的SQL语句，如创建表        """        res = cls().sql_init(db_info)        conn = res[0]        cur = res[1]        try:            cur.execute(sql)            result = True        except:            result = False        conn.close()        return resultdb = {"host":"127.0.0.1","user":"root","passwd":"123","db":"test"}## 插入的例子# sql = 'insert into Userinfo (id,name,password) values(%s,%s,%s)'# param = [('%d' %(i),'user_%d' %(i),'passwd_%d' %(i)) for i in xrange(5,11)]# print MySQL.insert_sql(sql,param,**db)## 查询的例子sql = 'select * from Userinfo'data = MySQL.query_sql(sql,dict,**db)for i in data:    print '%s' %(i)