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()