Python classes that connect to the database

article directory

overview
python connection MySQL
python connection PostgreSQL

overview

in general, when using python to connect to a database, I like to define a Class to facilitate the subsequent use of
Class: a collection of objects that have the same properties and methods. It defines the properties and methods that are common to each object in the collection. An object is an instance of a class.
can be likened to a Word template that you can use every time you create a new Word file, which is handy for frequently used scenarios

connects to the database to execute commands and return results and then disconnects, as is often the case when writing scripts, so define a class

python connection MySQL

connects to MySQL using pymysql

class cnMySQL:
    def __init__(self):
        self._dbhost = '172..16.56.2'
        self._dbuser = 'dba'
        self._dbpassword = 'dba1'
        self._dbname = 'test'
        self._dbcharset = 'utf8'
        self._dbport = int(3306)
        self._conn = self.connectMySQL()

        if (self._conn):
            self._cursor = self._conn.cursor(cursor=pymysql.cursors.DictCursor)
    
    def connectMySQL(self):
        try:
            conn = pymysql.connect(host=self._dbhost,
                                   user=self._dbuser,
                                   passwd=self._dbpassword,
                                   db=self._dbname,
                                   port=self._dbport,
                                   cursorclass=pymysql.cursors.DictCursor,
                                   charset=self._dbcharset)
        except Exception as e:
            raise
            #print("数据库连接出错")
            conn = False
        return conn

    def close(self):
        if (self._conn):
            try:
                if (type(self._cursor) == 'object'):
                    self._conn.close()
                if (type(self._conn) == 'object'):
                    self._conn.close()
            except Exception:
                print("关闭数据库连接异常")

    def ExecQuery(self,sql,*args):
        """
        执行查询语句
        """
        res = ''
        if (self._conn):
            try:
                self._cursor.execute(sql,args)
                res = self._cursor.fetchall()
            except Exception:
                res = False
                print("查询异常")
            self.close()
        return res

use method :
first call: conn = cnMySQL()
execute SQL example: test_sql = conn.ExecQuery(” select * from test where id = %s; Canshu)
returns a list

containing dict when the data is returned

python connection PostgreSQL

connects to PG using the package psycopg2

class PGINFO:

    def __init__(self,host, user, pwd, db, port):
        self.host = host
        self.user = user
        self.pwd = pwd
        self.db = db
        self.port = port

    def __GetConnect(self):
        """
        得到连接信息
        返回: conn.cursor()
        """
        if not self.db:
            raise(NameError, "没有设置数据库信息")
        self.conn = psycopg2.connect(database=self.db, user=self.user, password=self.pwd, host=self.host, port=self.port)
        cur = self.conn.cursor()
        if not cur:
            raise (NameError, "连接数据库失败")
        else:
            return  cur

    def ExecQuery(self, sql):
        """
        执行查询语句
        """
        if sql == 'close':
            self.conn.close()
        else:
            cur = self.__GetConnect()
            cur.execute(sql)
            # resList = cur.fetchall()
            return cur

is called first, where

using the parameter file is called

 pg = PGINFO(host=host_cus, user=user_cus, pwd=pwd_cus, db=db_cus, port=port_cus)

executes the command, fetching the returned result

cur = pg.ExecQuery("show data_directory;")
    pgdata = cur.fetchone()

ProgrammerAH

Programmer Guide, Tips and Tutorial

Python classes that connect to the database

article directory

Read More: