Python连接Mysql数据库

1、环境配置及依赖安装
参考：https://pypi.org/project/mysqlclient/
sudo apt-get install libmysqlclient-dev
pip3 install mysqlclient

Note on Python 3 : if you are using python3 then you need to install python3-dev using the following command :

sudo apt-get install python3-dev # debian / Ubuntu

sudo yum install python3-devel # Red Hat / CentOS

2、使用Python连接数据库
查看资料：https://pypi.org/project/mysqlclient/
查看资料：https://mysqlclient.readthedocs.io/
3、用Python查询数据库

import MySQLdb

""" 获取连接 """
try:
  conn = MySQLdb.connect(
    host = "localhost",
    port = 3306,
    user = "dog",
    passwd = "123456",
    db = "news",
    charset = 'utf8'
  )
  
  """ 获取数据 """
  cursor = conn.cursor()
  cursor.execute('SELECT * FROM `news` ORDER BY `created_at` DESC;')
  rest = cursor.fetchone()
  print(rest)

  """ 关闭连接 """
  conn.close() 
except MySQLdb.Error as e:
  print('Error: %s' %(e))

4、新增数据到数据库

import MySQLdb

class MysqlSearch(object):
  def __init__(self):
    self.get_conn()

  def get_conn(self):
    try:
      self.conn = MySQLdb.connect(
      host = "localhost",
      port = 3306,
      user = "dog",
      passwd = "123456",
      db = "news",
      charset = "utf8"
    )   
    
    except MySQLdb.error as e:
      print('Error: %s' % e)
  
  def close_conn(self):
    try:
      if self.conn:
        self.conn.close()
    except MySQLdb.Error as e:
      print('Error: %s' % e)
  
  def get_one(self):
    sql = 'SELECT * FROM `news` WHERE `types` = %s ORDER BY `created_at` DESC';
    cursor = self.conn.cursor()
    cursor.execute(sql, ('时尚',))
    rest = dict(zip([k[0] for k in cursor.description], cursor.fetchone()))
    cursor.close()
    self.close_conn()
    return rest

  def get_more(self, offset, page_size):
    sql = 'SELECT * FROM `news` WHERE `types` = %s ORDER BY `created_at` DESC LIMIT %s,%s';
    cursor = self.conn.cursor()
    cursor.execute(sql, ('时尚', offset, page_size))
    rest = dict(zip([k[0] for k in cursor.description], cursor.fetchall()))
    cursor.close()
    self.close_conn()
    return rest

def add_one(self):
    try:
      sql = 'INSERT INTO `news` (`title`,`image`,`content`,`types`,`author`,`created_at`,`view_count`,`is_valid`) VALUE(%s,%s,%s,%s,%s,%s,%s,%s)';
      # 获取cursor
      cursor = self.conn.cursor()
      cursor.execute(sql,("TEST TITLE3","http://p1.ifengimg.com/2018_51/C8E8ADC78CDD8D082105C97478D4A8D11FA5C39E_w660_h380.jpg","这次中央经济工作会议，对全面贯彻党的十九大精神开局之年中国经济发展的成绩与>经验进行了全面总结，就重要战略机遇期新内涵、宏观政策要强化逆周期调节等做出了一系列新的重大判断，并对2019年>    中国经济工作进行了具体的战略部署。这次会议对于我们决胜全面建成小康社会、确保“十三五”规划顺利>实施具有重要指导意义和引领作用。","时政","央视新闻","2018-12-22 16:48:47","200","1"))
      cursor.execute(sql,("TEST TITLE2","http://p1.ifengimg.com/2018_51/C8E8ADC78CDD8D082105C97478D4A8D11FA5C39E_w660_h380.jpg","这次中央经济工作会议，对全面贯彻党的十九大精神开局之年中国经济发展的成绩与>经验进行了全面总结，就重要战略机遇期新内涵、宏观政策要强化逆周期调节等做出了一系列新的重大判断，并对2019年>    中国经济工作进行了具体的战略部署。这次会议对于我们决胜全面建成小康社会、确保“十三五”规划顺利>实施具有重要指导意义和引领作用。","时政","央视新闻","2018-12-22 16:48:47","200","niao"))
      self.conn.commit()
      cursor.close()
    except:
      print('error')
      self.conn.rollback()    # 回滚
    self.close_conn()

def main():
  obj = MysqlSearch()
  '''
  rest = obj.get_more()
  for item in rest:
    print(item)
    print('------------')
  '''
  obj.add_one()

if __name__ == '__main__':
    main()