python操作mysql，,我的python版本

python操作mysql，,我的python版本

我的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> 

 1 #!/usr/bin/python 2 #coding:utf-8 3  4 import MySQLdb 5  6 conn = MySQLdb.Connect( 7            host = ‘127.0.0.1‘, 8            port = 3306, 9            user = ‘root‘,10            passwd = ‘abc123‘,11            db = ‘shop‘,12            charset = ‘utf8‘13         )14 15 cursor = conn.cursor()16 17 sql = ‘select * from user_info‘18 19 cursor.execute( sql )20 21 print cursor.rowcount22 23 res = cursor.fetchone()24 print res25 26 res = cursor.fetchmany( 2 )27 print res28 29 res = cursor.fetchall()30 print res31 32 cursor.close()33 conn.close()

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

 1 #!/usr/bin/python 2 #coding:utf-8 3  4 import MySQLdb 5  6 conn = MySQLdb.Connect( 7            host = ‘127.0.0.1‘, 8            port = 3306, 9            user = ‘root‘,10            passwd = ‘abc123‘,11            db = ‘shop‘,12            charset = ‘utf8‘13         )14 15 cursor = conn.cursor()16 sql = ‘select * from user_info‘17 cursor.execute( sql )18 19 res = cursor.fetchall()20 for row in res:21     print ‘userid=%s, username=%s‘ % row22 23 cursor.close()24 conn.close()

python操作mysql增删改:

#!/usr/bin/python#coding:utf-8import MySQLdbconn = 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.rowcountcursor.close()conn.close()

修改表引擎，测试事务

alter table user_info engine = innodb

#!/usr/bin/python#coding:utf-8import MySQLdbconn = 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.rowcountcursor.close()conn.close()

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

回滚测试

#!/usr/bin/python#coding:utf-8import MySQLdbconn = 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.rollbackcursor.close()conn.close()

python操作mysql