Python connection MySQL

Python 3.x Connection Database (Pymysql mode)

==================pymysql===================

Since the MySQLdb module does not yet support python3.x, python3.x need to install the Pymysql module if you want to connect to MySQL.

The Pymysql module can be installed via PIP. But if you're using the Pycharm IDE, you can use Project Python to install third-party modules.

[File] >> [settings] >> [project:python] >> [Project Interpreter ] >> [install button ]

650) this.width=650; "Src=" http://images2015.cnblogs.com/blog/875028/201610/875028-20161002000403750-123452875. PNG "style=" margin:0px;padding:0px;border:0px; "/>

Because Python unifies the interface of the database connection, Pymysql and mysqldb are similar in usage:

Pymysql. Connect () parameter description Host (str):       mysql server address port (int):       mysql Server port number User (str):       username passwd (str):     password db (str ):         database name CharSet (str):    Connection encoding Connection object supported methods cursor ()          Use this connection to create and return a cursor commit ()           Commit current transaction rollback ()        rollback current transaction close ()            close the methods supported by the Cursor Object Execute (OP)       Execute a query command for a database Fetchone ()        get the next line of the result set Fetchmany (size)   Get the next few lines of the result set Fetchall ()        get all rows in the result set ROWCOUNT ()        returns the number of data bars or affects the number of rows close ()           close a Cursor object

==================MySQL===================

First, before connecting to the database, create a trading table that is easy to test the Pymysql function:

drop table if exists  ' Trade '; create table  ' Trade '   (   ' id '  int (4)  unsigned not null auto_ increment,   ' name '  varchar (6)  NOT NULL COMMENT  ' User real name ',   ' Account '  varchar '  NOT NULL COMMENT  ' bank savings accounts ',   ' Saving '  decimal (8,2)  unsigned NOT NULL DEFAULT  ' 0.00 '  COMMENT  ' Account savings amount ',   ' expend '  decimal (8,2)  unsigned NOT NULL DEFAULT  ' 0.00 '  COMMENT  ' Total account spending ',    ' Income '  decimal (8,2)  unsigned NOT NULL DEFAULT  ' 0.00 '  COMMENT  ' Total account revenue ',  primary key  (' id '),  unique key  ' Name_unique '   (' name '))  ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;INSERT INTO  ' Trade '   values  (1, ' Jobs ', ' 18012345678 ', 0.00,0.00,0.00);

==================Python===================

Use Python script to implement additions and deletions and transaction processing, the source code is as follows:

import pymysql.cursors#  Connect to Database Connect = pymysql. Connect (    host= ' localhost ',    port=3310,     User= ' Woider ',     passwd= ' 3243 ',     db= ' python ',     charset= ' UTF8 ') #  get cursor cursor = connect.cursor () #  Insert Data sql =  "INSERT  into trade  (name, account, saving)  VALUES  (  '%s ',  '%s ',  %.2f  ) "data =  (' Lei June ',  ' 13512345678 ',  10000) Cursor.execute (sql % data) Connect.commit () print (' Successfully insert ', cursor.rowcount,  ' data ') #  modify data sql =  "Update trade  SET saving = %.2f WHERE account =  '%s '   ' data =  (8888,   ' 13512345678 ') cursor.execute (sql % data) connect.commit () print (' Successful modification ',  cursor.rowcount,   ' data ') #  query data sql =  "select name,saving from trade&nbsp where account =  '%s '   ' data =  (' 13512345678 ',) Cursor.execute (sql % data ) For row in cursor.fetchall ():     print ("name:%s\tsaving:%.2f"  %  Row) print (' co-locate ', cursor.rowcount,  ' data ') #  delete data sql =  "Delete from trade  WHERE account =  '%s '  limit %d "data =  (' 13512345678 ',  1) Cursor.execute (Sql % data) connect.commit () print (' successfully delete ', cursor.rowcount,  ' bar data ') #  Transaction processing sql_1 =  "Update trade set saving = saving + 1000 where  account =  ' 18012345678 '   ' sql_2 =  ' update trade set expend =  expend + 1000 WHERE account =  ' 18012345678 '   ' sql_3 =  ' update trade set income = income + 2000 where account =   ' 18012345678 '   TRY:    cursor.execute (Sql_1)   #  Savings increase 1000    cursor.execute (sql _2)   #  expenditure increased 1000    cursor.execute (Sql_3)   #  revenue increased by 2000except  exception as e:    connect.rollback ()   #  transaction rollback      print (' transaction failed ',  e) else:    connect.commit ()   #  Transaction commit      print (' Transaction success ',  cursor.rowcount) #  close Connection cursor.close () Connect.close ()

================== test Results ===================

650) this.width=650; "Src=" http://images2015.cnblogs.com/blog/875028/201610/875028-20161002001957094-148578946. PNG "style=" margin:0px;padding:0px;border:0px; "/>

Python connection MySQL