Detailed explanation of examples of python operations on mysql database

This article mainly introduces the method of operating mysql database in python3, which has certain reference value. Interested friends can refer to it

Software and hardware environment

OS Python also has many solutions for databases. This article takes mysql in python3 as an example to introduce the use of the pymysql module.


Prepare the database

Create a mysql database named testdb, create a table called testtable, it has 3 fields, namely id, and the data type is INT (11), set as primary key, non-null, UNSIGNED, AUTO INCREMENT, name, data type is VARCHAR(45), set as non-null, unique, sex, data type is VARCHAR(45), set as non-null

python3 source code

# -*- coding: utf-8 -*-
__author__ = 'djstava@gmail.com'

import logging
import pymysql

class MySQLCommand(object):
  def __init__(self,host,port,user,passwd,db,table):
    self.host = host
    self.port = port
    self.user = user
    self.password = passwd
    self.db = db
    self.table = table

  def connectMysql(self):
    try:
      self.conn = pymysql.connect(host=self.host,port=self.port,user=self.user,passwd=self.password,db=self.db,charset='utf8')
      self.cursor = self.conn.cursor()
    except:
      print('connect mysql error.')

  def queryMysql(self):
    sql = "SELECT * FROM " + self.table

    try:
      self.cursor.execute(sql)
      row = self.cursor.fetchone()
      print(row)

    except:
      print(sql + ' execute failed.')

  def insertMysql(self,id,name,sex):
    sql = "INSERT INTO " + self.table + " VALUES(" + id + "," + "'" + name + "'," + "'" + sex + "')"
    try:
      self.cursor.execute(sql)
    except:
      print("insert failed.")

  def updateMysqlSN(self,name,sex):
    sql = "UPDATE " + self.table + " SET sex='" + sex + "'" + " WHERE name='" + name + "'"
    print("update sn:" + sql)

    try:
      self.cursor.execute(sql)
      self.conn.commit()
    except:
      self.conn.rollback()


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

The above is the detailed content of Detailed explanation of examples of python operations on mysql database. For more information, please follow other related articles on the PHP Chinese website!