MySQL interacts with Python

sudo apt-get install Python-mysql

Suppose there is a database test1, there is a product information table products, insert a product information into it, the program is as follows:

#-*-Coding:utf-8-*-
Import MySQLdb
Try
Conn=mysqldb.connect (host= ' localhost ', port=3306,db= ' test1 ', user= ' root ', passwd= ' MySQL ', charset= ' UTF8 ')
Cs1=conn.cursor ()
Count=cs1.execute ("INSERT into products (prod_name) VALUES (' iphone ')")
Print Count
Conn.commit ()
Cs1.close ()
Conn.close ()
Except Exception,e:
Print E.message

Connection object: Used to establish a connection to the database
        Create object: Call the Connect () method
Conn=connect ( Parameter list)
    parameters Host: Connected MySQL host, if this machine is ' localhost '
    parameter port: The ports of the connected MySQL host, Default is 3306
    parameter db: Database name
    parameter User: Connected username
    Parameter password: the password of the connection
    parameter charset: The encoding method used by the communication, the default is ' gb2312 ', the requirement is consistent with the encoding specified at the time of database creation, otherwise the Chinese will be garbled

Methods of the Object
Close () Closes the connection
Commit () transaction, so a commit is required before it takes effect
Rollback () transaction, discarding the previous operation
Cursor () Returns the cursor object for executing the SQL statement and obtaining the result

Cursor object: Executing SQL statement
Create object: Call the cursor () method of the Connection object
Cursor1=conn.cursor ()

Methods of the Object
Close () off
Execute (Operation [, parameters]) executes the statement, returning the number of rows affected
Fetchone () Executes the query statement, gets the first row data of the query result set, returns a tuple
Next () Gets the next row of the current row when executing a query statement
Fetchall () When executing a query, gets all the rows of the result set, one row to form a tuple, and then assembles the elements into a tuple to return
Scroll (Value[,mode]) moves the row pointer to a location
Mode means the way it moves.
The default value of mode is relative, which means moving to value,value based on the current line and moving downward, and value being negative.
The value of mode is absolute, which represents the position based on the first data, where the first data is 0

Modify/delete

#-*-Coding:utf-8-*-
Import MySQLdb
Try
Conn=mysqldb.connect (host= ' localhost ', port=3306,db= ' test1 ', user= ' root ', passwd= ' MySQL ', charset= ' UTF8 ')
Cs1=conn.cursor ()
# Modify
Count=cs1.execute ("Update products set prod_name= ' Xiaomi ' where id=6")
# Delete
Count=cs1.execute ("Delete from Products where id=6")
Print Count
Conn.commit ()
Cs1.close ()
Conn.close ()
Except Exception,e:
Print E.message

Insert a piece of data

#-*-Coding:utf-8-*-
Import MySQLdb
Try
Conn=mysqldb.connect (host= ' localhost ', port=3306,db= ' test1 ', user= ' root ', passwd= ' MySQL ', charset= ' UTF8 ')
Cs1=conn.cursor ()
Prod_name=raw_input ("Please enter product Name:")
Params=[prod_name]
Count=cs1.execute (' INSERT into products (sname) values (%s) ', params)
Print Count
Conn.commit ()
Cs1.close ()
Conn.close ()
Except Exception,e:
Print E.message

Query A

#-*-Coding:utf-8-*-
Import MySQLdb
Try
Conn=mysqldb.connect (host= ' localhost ', port=3306,db= ' test1 ', user= ' root ', passwd= ' MySQL ', charset= ' UTF8 ')
Cs1=conn.cursor ()
Cur.execute (' select * FROM Products where id=2 ')
Result=cur.fetchone ()
Print result
Conn.commit ()
Cs1.close ()
Conn.close ()
Except Exception,e:
Print E.message

Querying multiple lines

#-*-Coding:utf-8-*-
Import MySQLdb
Try
Conn=mysqldb.connect (host= ' localhost ', port=3306,db= ' test1 ', user= ' root ', passwd= ' MySQL ', charset= ' UTF8 ')
Cs1=conn.cursor ()
Cur.execute (' select * from Prod_name ')
Result=cur.fetchall ()
Print result
Conn.commit ()
Cs1.close ()
Conn.close ()
Except Exception,e:
Print E.message

Encapsulation: Observe the previous program found that, in addition to SQL statements and parameters, the other statements are the same, you can encapsulate and then call

#-*-Coding:utf-8-*-
Import MySQLdb

Class Mysqlhelper ():
def __init__ (self,host,port,db,user,passwd,charset= ' UTF8 '):
Self.host=host
Self.port=port
Self.db=db
Self.user=user
self.passwd=passwd
Self.charset=charset

def connect (self):
Self.conn=mysqldb.connect (host=self.host,port=self.port,db=self.db,user=self.user,passwd=self.passwd,charset= Self.charset)
Self.cursor=self.conn.cursor ()

def close (self):
Self.cursor.close ()
Self.conn.close ()

    def get_one (self,sql,params= ()):
        Result=none
        try:
             self.connect ()
            Self.cursor.execute (SQL, params)
            result = Self.cursor.fetchone ()
            self.close ()
        except Exception, E:
             print E.message
        return result

def get_all (self,sql,params= ()):
List= ()
Try
Self.connect ()
Self.cursor.execute (Sql,params)
List=self.cursor.fetchall ()
Self.close ()
Except Exception,e:
Print E.message
Return list

def insert (self,sql,params= ()):
Return Self.__edit (Sql,params)

def update (self, SQL, params= ()):
return self.__edit (SQL, params)

def delete (self, SQL, params= ()):
return self.__edit (SQL, params)

def __edit (self,sql,params):
Count=0
Try
Self.connect ()
Count=self.cursor.execute (Sql,params)
Self.conn.commit ()
Self.close ()
Except Exception,e:
Print E.message
return count

Save As mysqlhelper.py file

#-*-Coding:utf-8-*-
From mysqlhelper Import *

Sql= ' Insert intoproducts (prod_name,price) VALUES (%s,%s) '
Prod_name=raw_input ("Please enter product Name:")
Price=raw_input ("Please enter Unit Price:")
Params=[prod_name,price]

Mysqlhelper=mysqlhelper (' localhost ', 3306, ' test1 ', ' root ', ' MySQL ')
Count=mysqlhelper.insert (Sql,params)
If count==1:
print ' OK '
Else
print ' ERROR '

Invoking a class query query

#-*-Coding:utf-8-*-
From mysqlhelper Import *

Sql= ' Select Prod_name,price from Products order by ID '

Helper=mysqlhelper (' localhost ', 3306, ' test1 ', ' root ', ' MySQL ')
One=helper.get_one (SQL)
Print One

MySQL interacts with Python