Python for mysql and pythonmysql

Python for mysql and pythonmysql

My python version (2.7)

Install the python mysql driver

Sudo pip install MySQL-python

If an error is reported, mysql_config cannot be found:

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

Set up a soft connection according to the actual situation, and the driver will be able to find mysql_config, because mysql is installed under/usr/local/mysql57.

If an error is reported, libmysqlclient. so.20 cannot be found, and a corresponding soft connection is also established (Please create a soft link in the corresponding directory based on your computer's actual situation)

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

Python mysql operation process:

Start-> create a connection object (connection)-> obtain the cursor object cursor-> execute selectsentence (cursor.exe cute ()-> Use cursor. fetch * (fetchone, fetchall, fetchbatch) to get and process data-> close cursor-> close connect

-> End

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

When the data volume is small, you can read it at one time to traverse the fetchall data.

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

Add, delete, and modify mysql operations using python:

#!/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()

Modify Table engine and test transaction

alter table user_info engine = innodb

#! /Usr/bin/python # coding: utf-8import MySQLdbconn = MySQLdb. connect (host = '2017. 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, 'wukong')" cursor.exe cute (SQL _insert) conn. commit () print cursor. rowcountcursor. close () conn. close ()

If the statement conn. commit () is not run, the newly inserted records are not displayed in the mysql table. By default, python mysql disables automatic transaction commit.

Rollback Test

#! /Usr/bin/python # coding: utf-8import MySQLdbconn = MySQLdb. connect (host = '2017. 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, 'wukong')" SQL _insert1 = "insert into user_info (user_id, user_name) values (null, 'BA Jie ') "SQL _insert2 =" insert into user_info (user_id, user_name1) values (null, 'White horse') "try: cursor.exe cute (SQL _insert) cursor.exe cute (SQL _insert1) cursor.exe cute (SQL _insert2) failed t Exception as e: print e conn. rollbackcursor. close () conn. close ()