python3数据库配置,远程连接mys

python配置数据库mysql

• 安装mysql数据库，方法如下

  安装配置mysql数据库服务器

• 安装pymysql

  
  #在python虚拟环境中安装，我采用的是这种方法
  
  $ pip install PyMySQL
  Collecting PyMySQL
  Downloading PyMySQL-0.8.0-py2.py3-none-any.whl (83kB)
    100% |████████████████████████████████| 92kB 222kB/s 
  Installing collected packages: PyMySQL
  Successfully installed PyMySQL-0.8.0
  
  
  #PyMySQL 是在 Python3.x 版本中用于连接 MySQL 服务器的一个库，Python2中则使用mysqldb。
  
  
  
  #PyMySQL 遵循 Python 数据库 API v2.0 规范，并包含了 pure-Python MySQL 客户端库。
  
  
  
  #安装成功
  
  
  # eric @ ray in ~/.local/virtualenvs/my_env3.6/code [18:54:30] 
  
  $ python
  Python 3.6.3 (default, Oct  6 2017, 08:44:35) 
  [GCC 5.4.0 20160609] on linux
  Type "help", "copyright", "credits" or "license" for more information.
  >>> import pymysql#输入不报错
  >>> 
  

• 连接mysql数据库

  查询mysql版本信息

  
  #!/usr/bin/python3                                                                                                              
  
  
  # -*- coding: utf-8 -*-
  
  """
  
  # Author: EricRay
  
  
  # Created Time : 2017-12-27 16:45:02
  
  
  
  # File Name: db.py
  
  
  # Description:查询Mysql版本信息
  
  
  """
  import pymysql
  
  
  #打开数据库连接
  
  
  db = pymysql.connect("192.168.122.58","eric","eric2017","test")
  
  #pymysql.connect("localhost/IP","username","pwd","databs" )
  
  
  
  
  #使用cursor()方法创建一个游标对象 cursor
  
  
  cursor = db.cursor()
  
  
  #使用execute()方法执行sql查询
  
  cursor.execute("select VERSION()")
  
  
  #使用 fetchone()方法获取单条数据
  
  data = cursor.fetchone()
  
  print ("database version : %s" % data)
  
  
  #关闭连接
  
  db.close()
  

  建表

  
  #!/usr/bin/python3                                                                                                                     
  
  
  # -*- coding: utf-8 -*-
  
  """
  
  # Author: EricRay
  
  
  # Created Time : 2017-12-27 18:31:13
  
  
  
  # File Name: cr_t.py
  
  
  # Description:
  
  
  """
  import pymysql
  
  
  #打开数据库连接
  
  db = pymysql.connect("192.168.122.58","eric","lyd2017","test")
  
  cursor = db.cursor()
  
  cursor.execute("drop table if exists employee")
  
  
  #预处理语句建表
  
  sql = """create table employee(
            tname varchar(20) not null default '',
            age int,
            sex varchar(1)) ENGINE=InnoDB DEFAULT CHARSET=utf8"""
  
  cursor.execute(sql)
  
  db.close()
  

  插入数据

  
  #!/usr/bin/python3                                                                                                                     
  
  
  # -*- coding: utf-8 -*-
  
  """
  
  # Author: EricRay
  
  
  # Created Time : 2017-12-27 18:38:56
  
  
  
  # File Name: insert.py
  
  
  # Description:
  
  
  """
  
  
  #!/usr/bin/python3
  
  
  import pymysql
  
  
  # 打开数据库连接
  
  db = pymysql.connect("192.168.122.58","eric","lyd2017","test" )
  
  
  # 使用cursor()方法获取操作游标
  
  cursor = db.cursor()
  
  sql = "insert into employee(tname,age,sex) \
    values('%s','%d','%s')" % \ 
    ('Mary',18,'F')
  
  
  try:
    cursor.execute(sql)
    db.commit()
  except:
    db.rollback()
  
  db.close()
  

  查询

  Python查询Mysql使用 fetchone() 方法获取单条数据, 使用fetchall() 方法获取多条数据。

  • fetchone(): 该方法获取下一个查询结果集。结果集是一个对象

  • fetchall(): 接收全部的返回结果行.

  • rowcount: 这是一个只读属性，并返回执行execute()方法后影响的行数。

  
  #!/usr/bin/python3                                                                                                                     
  
  
  # -*- coding: utf-8 -*-
  
  """
  
  # Author: EricRay
  
  
  # Created Time : 2017-12-27 19:07:30
  
  
  # File Name: select.py
  
  
  # Description:
  
  """
  import pymysql
  
  
  # 打开数据库连接
  
  db = pymysql.connect("192.168.122.58","eric","lyd2017","test" )
  
  
  # 使用cursor()方法获取操作游标
  
  cursor = db.cursor()
  
  
  #查询
  
  sql = "select * from employee"
  
  try:
      cursor.execute(sql)
  
      result = cursor.fetchall()
  
      for row in result:
          tname = row[0]
          age = row[1]
          sex = row[2]
  
          #打印结果
          print('tname = %s, age = %d, sex = %s' % \ 
                (tname,age,sex))
  
  except:
      print("Error: unable to fetch data")
  db.close()   

  结果

  (my_env3.6) 
  
  # eric @ ray in ~/.local/virtualenvs/my_env3.6/code [19:14:05] 
  
  $ python select.py 
  tname = eric, age = 28, sex = M
  tname = Mary, age = 18, sex = F
  

  ​