python操作mysql

我的python版本( 2.7 )

需要安装python mysql驱动

sudo pip install MySQL-python

如果报错，找不到mysql_config:

sudo ln -s /usr/local/mysql57/bin/mysql_config /usr/local/bin/mysql_config

这里自己根据实际情况，建立一个软连接，驱动就能够找到mysql_config，因为我mysql装在/usr/local/mysql57下面

如果报错，找不到libmysqlclient.so.20，同样建立一个对应的软连接( 请根据自己电脑的实际情况在相应的目录建立软链接 )

sudo ln -s /usr/local/mysql57/lib/libmysqlclient.so.20 /usr/lib/libmysqlclient.so.20

python mysql操作流程：

开始->创建连接对象( connection )->获取游标对象cursor->执行select语句(cursor.execute() )->使用cursor.fetch*( fetchone,fetchall,fetchmany )获取并处理数据->关闭cursor->关闭connect

->结束

mysql> select * from user_info;
+---------+-----------+
| user_id | user_name |
+---------+-----------+
|       1 | ghostwu   |
|       2 | zhangsan  |
|       3 | zhangsan  |
|       4 | zhangsan  |
+---------+-----------+
4 rows in set (0.00 sec)

mysql> 

#!/usr/bin/python
#coding:utf-8

import MySQLdb

conn = MySQLdb.Connect(
           host = '127.0.0.1',
           port = 3306,
           user = 'root',
           passwd = 'abc123',
           db = 'shop',
           charset = 'utf8'
        )

cursor = conn.cursor()

sql = 'select * from user_info'

cursor.execute( sql )

print cursor.rowcount

res = cursor.fetchone()
print res

res = cursor.fetchmany( 2 )
print res

res = cursor.fetchall()
print res

cursor.close()
conn.close()

当数据量比较小的时候，可以一次性读取出来，遍历fetchall的数据

#!/usr/bin/python
#coding:utf-8

import MySQLdb

conn = MySQLdb.Connect(
           host = '127.0.0.1',
           port = 3306,
           user = 'root',
           passwd = 'abc123',
           db = 'shop',
           charset = 'utf8'
        )

cursor = conn.cursor()
sql = 'select * from user_info'
cursor.execute( sql )

res = cursor.fetchall()
for row in res:
    print 'userid=%s, username=%s' % row

cursor.close()
conn.close()

python操作mysql增删改:

#!/usr/bin/python
#coding:utf-8

import MySQLdb

conn = MySQLdb.Connect(
           host = '127.0.0.1',
           port = 3306,
           user = 'root',
           passwd = 'abc123',
           db = 'shop',
           charset = 'utf8'
        )

cursor = conn.cursor()

'''
sql_insert = "insert into user_info( user_id, user_name ) values( null, 'hello' )"
cursor.execute( sql_insert )
print cursor.rowcount
'''

'''
sql_delete = "delete from user_info where user_name = 'hello'"
cursor.execute( sql_delete )
print cursor.rowcount
'''

sql_update = "update user_info set user_name = 'ghostwu' where user_id = 4"
cursor.execute( sql_update )
print cursor.rowcount

cursor.close()
conn.close()

修改表引擎，测试事务

alter table user_info engine = innodb

#!/usr/bin/python
#coding:utf-8

import MySQLdb

conn = MySQLdb.Connect(
           host = '127.0.0.1',
           port = 3306,
           user = 'root',
           passwd = 'abc123',
           db = 'shop',
           charset = 'utf8'
        )

cursor = conn.cursor()

sql_insert = "insert into user_info( user_id, user_name ) values( null, '悟空' )"
cursor.execute( sql_insert )
conn.commit()
print cursor.rowcount

cursor.close()
conn.close()

如果没有conn.commit()这条语句，执行之后，不会在mysql表中，看到新插入的记录。python mysql默认是关闭自动提交事务的

回滚测试

#!/usr/bin/python
#coding:utf-8

import MySQLdb

conn = MySQLdb.Connect(
           host = '127.0.0.1',
           port = 3306,
           user = 'root',
           passwd = 'abc123',
           db = 'shop',
           charset = 'utf8'
        )

cursor = conn.cursor()

sql_insert = "insert into user_info( user_id, user_name ) values( null, '悟空' )"
sql_insert1 = "insert into user_info( user_id, user_name ) values( null, '八戒' )"
sql_insert2 = "insert into user_info( user_id, user_name1 ) values( null, '白龙马' )"

try:
    cursor.execute( sql_insert )
    cursor.execute( sql_insert1 )
    cursor.execute( sql_insert2 )
except Exception as e:
    print e 
    conn.rollback
cursor.close()
conn.close()

使用事务处理银行转账：

mysql> select * from account;
+--------+-------+
| acctid | money |
+--------+-------+
|      1 |   110 |
|      2 |    10 |
+--------+-------+
2 rows in set (0.00 sec)

mysql> show create table account G
*************************** 1. row ***************************
       Table: account
Create Table: CREATE TABLE `account` (
  `acctid` int(11) DEFAULT NULL COMMENT '账户id',
  `money` int(11) DEFAULT NULL COMMENT '余额'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> 

#!/usr/bin/python
#coding:utf-8

import sys
import MySQLdb

class TransferMoney( object ):
    def __init__( self, conn ):
        self.conn = conn

    def check_acct_available( self, acctid ):
        cursor = self.conn.cursor()
        try:
            sql = "select * from account where acctid=%s" % acctid
            print sql
            cursor.execute( sql )
            res = cursor.fetchall()
            if len( res ) != 1:
                raise Exception( "账号%s不存在" % acctid )
        finally:
            cursor.close()

    def has_enough_money( self, acctid, money ):
        cursor = self.conn.cursor()
        try:
            sql = "select * from account where acctid=%s and money > %s" % ( acctid, money )
            print sql
            cursor.execute( sql )
            res = cursor.fetchall()
            if len( res ) != 1:
                raise Exception( "账号%s上的钱不足%s" % ( acctid, money ) )
        finally:
            cursor.close()

    def reduce_money( self, acctid, money ):
        cursor = self.conn.cursor()
        try:
            sql = "update account set money = money-%s where acctid = %s" % ( money, acctid )
            print sql
            cursor.execute( sql )
            if cursor.rowcount != 1:
                raise Exception( "账号%s减款失败" % acctid )
        finally:
            cursor.close()

    def add_money( self, acctid, money ):
        cursor = self.conn.cursor()
        try:
            sql = "update account set money = money+%s where acctid = %s" % ( money, acctid )
            print sql
            cursor.execute( sql )
            if cursor.rowcount != 1:
                raise Exception( "账号%s加款失败" % acctid )
        finally:
            cursor.close()


    def transfer( self, source_acctid, target_acctid, money ):
        try:
            self.check_acct_available( source_acctid )
            self.check_acct_available( target_acctid )
            self.has_enough_money( source_acctid, money )
            self.reduce_money( source_acctid, money )
            self.add_money( target_acctid, money )
            self.conn.commit()
        except Exception as e:
            self.conn.rollback()
            raise e

if __name__ == "__main__":
    source_acctid = sys.argv[1]
    target_acctid = sys.argv[2]
    money = sys.argv[3]

    conn = MySQLdb.Connect(
           host = '127.0.0.1',
           port = 3306,
           user = 'root',
           passwd = 'abc123',
           db = 'shop',
           charset = 'utf8'
        )
    tr_money = TransferMoney( conn )

    try:
        tr_money.transfer( source_acctid, target_acctid, money )
    except Exception as e:
        print "转账出现问题:" + str( e )
    finally:
        conn.close()

ghostwu@ghostwu:~/python/db$ python transfer.py 1 2 100
select * from account where acctid=1
select * from account where acctid=2
select * from account where acctid=1 and money > 100
update account set money = money-100 where acctid = 1
update account set money = money+100 where acctid = 2
ghostwu@ghostwu:~/python/db$ python transfer.py 1 2 100
select * from account where acctid=1
select * from account where acctid=2
select * from account where acctid=1 and money > 100
转账出现问题:账号1上的钱不足100