使用python连接mysql数据库之pymysql模块的使用

安装pymysql

pip install pymysql

2|0使用pymysql

2|1使用数据查询语句

查询一条数据fetchone()

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

from pymysql import *   conn = connect (    host= '127.0.0.1' ,    port=3306, user = 'root' ,    password = '123456' ,    database = 'itcast' ,    charset= 'utf8' )   # 创建游标 c = conn. cursor () # 执行sql语句 c. execute ( "select * from student" ) # 查询一行数据 result = c.fetchone() print(result) # 关闭游标 c. close () # 关闭数据库连接 conn. close () "" " (1, '张三', 18, b'\x01') " ""

查询多条数据fetchall()

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

from pymysql import *   conn = connect (    host= '127.0.0.1' ,    port=3306, user = 'root' ,    password = '123456' ,    database = 'itcast' ,    charset= 'utf8' )   # 创建游标 c = conn. cursor () # 执行sql语句 c. execute ( "select * from student" ) # 查询多行数据 result = c.fetchall() for item in result:    print(item) # 关闭游标 c. close () # 关闭数据库连接 conn. close () "" " (1, '张三', 18, b'\x01') (2, '李四', 19, b'\x00') (3, '王五', 20, b'\x01') " ""

更改游标的默认设置，返回值为字典

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

from pymysql import *   conn = connect (    host= '127.0.0.1' ,    port=3306, user = 'root' ,    password = '123456' ,    database = 'itcast' ,    charset= 'utf8' )   # 创建游标，操作设置为字典类型 c = conn. cursor (cursors.DictCursor) # 执行sql语句 c. execute ( "select * from student" ) # 查询多行数据 result = c.fetchall() for item in result:    print(item) # 关闭游标 c. close () # 关闭数据库连接 conn. close () "" " {'id': 1, 'name': '张三', 'age': 18, 'sex': b'\x01'} {'id': 2, 'name': '李四', 'age': 19, 'sex': b'\x00'} {'id': 3, 'name': '王五', 'age': 20, 'sex': b'\x01'} " ""

返回一条数据时也是一样的。返回字典或者时元组看个人需要。

2|2使用数据操作语句

执行增加、删除、更新语句的操作其实是一样的。只写一个作为示范。

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

from pymysql import *   conn = connect (    host= '127.0.0.1' ,    port=3306, user = 'root' ,    password = '123456' ,    database = 'itcast' ,    charset= 'utf8' )   # 创建游标 c = conn. cursor () # 执行sql语句 c. execute ( "insert into student(name,age,sex) values (%s,%s,%s)" ,( "小二" ,28,1)) # 提交事务 conn. commit () # 关闭游标 c. close () # 关闭数据库连接 conn. close ()

和查询语句不同的是必须使用commit()提交事务，否则操作就是无效的。

3|0编写数据库连接类

普通版

MysqlHelper.py

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67

from pymysql import connect ,cursors   class MysqlHelper:    def __init__(self,           host= "127.0.0.1" ,           user = "root" ,           password = "123456" ,           database = "itcast" ,           charset= 'utf8' ,           port=3306):      self.host = host      self.port = port      self. user = user      self. password = password      self. database = database      self.charset = charset      self._conn = None      self._cursor = None      def _open(self):      # print( "连接已打开" )      self._conn = connect (host=self.host,                 port=self.port,                 user =self. user ,                 password =self. password ,                 database =self. database ,                 charset=self.charset)      self._cursor = self._conn. cursor (cursors.DictCursor)      def _close(self):      # print( "连接已关闭" )      self._cursor. close ()      self._conn. close ()      def one(self, sql, params=None):      result: tuple = None      try:        self._open()        self._cursor. execute (sql, params)        result = self._cursor.fetchone()      except Exception as e:        print(e)      finally:        self._close()      return result      def all (self, sql, params=None):      result: tuple = None      try:        self._open()        self._cursor. execute (sql, params)        result = self._cursor.fetchall()      except Exception as e:        print(e)      finally:        self._close()      return result      def exe(self, sql, params=None):      try:        self._open()        self._cursor. execute (sql, params)        self._conn. commit ()      except Exception as e:        print(e)      finally:        self._close()

该类封装了fetchone、fetchall、execute，省去了数据库连接的打开和关闭和游标的打开和关闭。
下面的代码是调用该类的小示例：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49

from MysqlHelper import *   mysqlhelper = MysqlHelper() ret = mysqlhelper. all ( "select * from student" ) for item in ret:    print(item) "" " {'id': 1, 'name': '张三', 'age': 18, 'sex': b'\x01'} {'id': 2, 'name': '李四', 'age': 19, 'sex': b'\x00'} {'id': 3, 'name': '王五', 'age': 20, 'sex': b'\x01'} {'id': 5, 'name': '小二', 'age': 28, 'sex': b'\x01'} {'id': 6, 'name': '娃哈哈', 'age': 28, 'sex': b'\x01'} {'id': 7, 'name': '娃哈哈', 'age': 28, 'sex': b'\x01'} " "" 上下文管理器版 mysql_with.py   from pymysql import connect , cursors   class DB:    def __init__(self,           host= 'localhost' ,           port=3306,           db= 'itcast' ,           user = 'root' ,           passwd= '123456' ,           charset= 'utf8' ):      # 建立连接      self.conn = connect (        host=host,        port=port,        db=db,        user = user ,        passwd=passwd,        charset=charset)      # 创建游标，操作设置为字典类型      self.cur = self.conn. cursor ( cursor =cursors.DictCursor)      def __enter__(self):      # 返回游标      return self.cur      def __exit__(self, exc_type, exc_val, exc_tb):      # 提交数据库并执行      self.conn. commit ()      # 关闭游标      self.cur. close ()      # 关闭数据库连接      self.conn. close ()

如何使用：

?

1 2 3 4 5 6 7 8 9 10

from mysql_with import DB   with DB() as db:    db. execute ( "select * from student" )    ret = db.fetchone()    print(ret)   "" " {'id': 1, 'name': '张三', 'age': 18, 'sex': b'\x01'} " ""

总结

以上所述是小编给大家介绍的使用python连接mysql数据库之pymysql模块的使用，希望对大家有所帮助，如果大家有任何疑问欢迎给我留言，小编会及时回复大家的！

原文链接：https://www.cnblogs.com/lxy0/archive/2019/09/01/11441723.html