Python study notes (3)

Python study notes (3) database connection

Database Connectivity

You need to install the dependency pip install PyMySQL first, if you haven't set the global mirror for python dependency download, please jump to

Test data connection usage

import pymysql

# Create database connection
db = pymysql.connect("192.168.10.112","xiongchao","xiongchao","xiongchao")

# Use cursor()Method to create a cursor object
cursor = db.cursor()

# Use excuse method to execute query database version

# Statements that can perform basic crud operations in execute
try:
 cursor.execute("select * from user")
 # Get a piece of data
 data = cursor.fetchall()
 cursor.fetchone()    print(data)for e in data:
  id = e[0]
  name = e[1]        print('id :{},name:{}'.format(id,name))
except expression as identifier:    print("Error: unable to find data from db")

db.close()

The tool class package is similar to the dao layer in java

The file is named db.py here, and the tested class is placed in the same path as it

import pymysql
classdb_utils:    def __init__(self):
  self.ip ='192.168.10.112'
  self.username ='xiongchao'
  self.password ='xiongchao'
  self.database ='xiongchao'
  self.cursor =''
  self.db =''"""Create database connection"""    def create_db(self):try:
   db = pymysql.connect(self.ip,self.username,self.password,self.database)
   self.db = db
   cursor = db.cursor()
   self.cursor = cursor
  except :            print("Error: connect to {} timeout ,please check config, and try agin".format(self.ip))"""Single query"""    def find_one(self,sql):try:
   self.cursor.execute(sql)
   data = self.cursor.fetchone()return data
  except :            print("Error: no data find ")
   self.db.close()"""Multiple queries"""    def find_all(self,sql):try:
   self.cursor.execute(sql)
   data = self.cursor.fetchall()
   res =[]if len(data)>0:for item in data :
     res.append({'id': item[0],'name':item[1]})return res
  except:            print('Error no data find')
   self.db.close()    def update(self,sql):try:
   self.cursor.execute(sql)
   # Data submission
   self.db.commit()
  except:
   # Rollback on error
   self.db.rollback()
  self.db.close()
   

# test
""" if __name__ == "__main__":
 test = db_utils()
 cursor = test.create_db()
 data = test.find_one("select * from user ")
 # What is returned is that the value of the primitive type is taken by subscript
 print(type(data))    print(data[0],data[1])"""

Test

# Reference your own package
import db

if __name__ =="__main__":
 # Object instantiation
 test = db.db_utils()
 test.create_db()

 data = test.find_all("select * from user")
 # Convert Yuanzu type to dictionary form
 print(data)
 test.close_cont()

The remaining modification and deletion usage is basically the same