python 操作Mariadb 数据库，pythonmariadb

python 操作Mariadb 数据库，pythonmariadb

首先我使用的是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 MySQLdb

conn = 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 MySQLdb

conn = 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 data
data = cur.fetchone()
print data
data = cur.fetchone()
print data
cur.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 MySQLdb

class 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 result

db = {"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)