pymysql pymysql1.py

最新推荐文章于 2022-05-31 14:29:16 发布

学无止境慢慢来

最新推荐文章于 2022-05-31 14:29:16 发布

阅读量195

点赞数

CC 4.0 BY-SA版权

分类专栏： pymysql 知识点

本文链接：https://blog.youkuaiyun.com/weixin_42193179/article/details/103665078

pymysql 知识点专栏收录该内容

2 篇文章

订阅专栏

本文详细介绍pymysql模块在Python中的使用方法，包括数据库连接、执行查询、获取查询结果等核心功能。涵盖pymysql.connect函数参数说明，cursor对象的方法如execute、fetchone、fetchall，以及如何通过description获取字段信息。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

"""
功能：pymysql 使用。
知识点：
1.
pymysql.connect(host=None, user=None, password='', database=None,
port=0, unix_socket=None, charset='', sql_mode=None, read_default_file=None,
conv=None, use_unicode=None, client_flag=0, cursorclass=<class 'pymysql.cursors.Cursor'>,
init_command=None, connect_timeout=10, ssl=None, read_default_group=None,
compress=None, named_pipe=None, autocommit=False, db=None, passwd=None,
local_infile=False, max_allowed_packet=16777216, defer_connect=False,
auth_plugin_map=None, read_timeout=None, write_timeout=None, bind_address=None,
binary_prefix=False, program_name=None, server_public_key=None)
Connect to the database; -> connection.

2.
db.cursor(cursor=None) method of pymysql.connections.Connection instance
    Create a new cursor to execute queries with.
    :param cursor: The type of cursor to create; one of :py:class:`Cursor`,
        :py:class:`SSCursor`, :py:class:`DictCursor`, or :py:class:`SSDictCursor`.
        None means use Cursor.

3.
cursor.execute(query, args=None) method of pymysql.cursors.Cursor instance
    Execute a query

    :param str query: Query to execute.
    :param args: parameters used with query. (optional)
    :type args: tuple, list or dict

    :return: 查询到的记录（元组）的条数。
    :rtype: int

    If args is a list or tuple, %s can be used as a placeholder in the query.
    If args is a dict, %(name)s can be used as a placeholder in the query.

4.
cursor.fetchone() method of pymysql.cursors.Cursor instance
    Fetch the next row
    -> （从第一行开始的）下一行数据。

5.
dir(cursor)
['DataError', 'DatabaseError', 'Error', 'IntegrityError', 'InterfaceError', 'InternalError',
'NotSupportedError', 'OperationalError', 'ProgrammingError', 'Warning', '__class__',
'__delattr__', '__dict__', '__dir__', '__doc__', '__enter__', '__eq__', '__exit__', '__format__',
'__ge__', '__getattribute__', '__gt__', '__hash__', '__init__', '__init_subclass__',
'__iter__', '__le__', '__lt__', '__module__', '__ne__', '__new__', '__reduce__',
'__reduce_ex__', '__repr__', '__setattr__', '__sizeof__', '__str__', '__subclasshook__',
'__weakref__', '_check_executed', '_clear_result', '_conv_row', '_do_execute_many',
'_do_get_result', '_ensure_bytes', '_escape_args', '_executed', '_get_db', '_nextset',
'_query', '_result', '_rows', 'arraysize', 'callproc', 'close', 'connection',
'description', 'execute', 'executemany', 'fetchall', 'fetchmany', 'fetchone',
'max_stmt_length', 'mogrify', 'nextset', 'rowcount', 'rownumber', 'scroll',
'setinputsizes', 'setoutputsizes']

6.
cursor.rowcount -> 查询到的记录（元组）的条数。

7.
cursor.rownumber -> 当前游标所在的记录的索引（下标）。
 cursor.rownumber
0
 cursor.fetchone()
(1, 'Ann', 30, 'Beijing', Decimal('2000.00'))
 cursor.rownumber
1
 cursor.fetchone()
(2, 'Kate', 28, 'Shanghai', Decimal('1500.00'))
 cursor.rownumber
2

8.
cursor.fetchall() method of pymysql.cursors.Cursor instance
    Fetch all the rows
    注：获取从当前游标位置（刚执行完查询后是 0）开始的所有记录。
    数据类似：((1, 'Ann', 30, 'Beijing', Decimal('2000.00')),
    (2, 'Kate', 28, 'Shanghai', Decimal('1500.00')), ...)

9.
获取已查询表的字段
 cursor.description
(('id', 3, None, 11, 11, 0, True), ('name', 253, None, 80, 80, 0, True),
('age', 3, None, 11, 11, 0, True), ('address', 254, None, 100, 100, 0, True),
('salary', 246, None, 20, 20, 2, True))


"""
import pymysql

# 建立连接
db = pymysql.connect('localhost', 'root', '123456', 'test')
# 使用cursor()方法创建一个游标对象cursor
cursor = db.cursor()
# 使用 execute() 方法执行 sql 语句
SQL = 'select * from customers;'
rowcount = cursor.execute(SQL)
print("rowcount:", rowcount)
# rowcount: 8
print("rownumber:", cursor.rownumber)
# rownumber: 0
firstLineData = cursor.fetchone()
print('第一行记录:', firstLineData)
secondLineData = cursor.fetchone()
print('第二行记录:', secondLineData)
print("rownumber:", cursor.rownumber)
# rownumber: 2
print()

# 使用　fetchall() 接受全部的返回结果行，返回结果保存在元组中
cursor.execute(SQL)
customers = cursor.fetchall()
print('表中所有记录为:', customers)


# 获取表中每行内容
for customer in customers:
    # print(customer)
    id = customer[0]
    name = customer[1]
    age = customer[2]
    address = customer[3]
    salary = customer[4]
    print(id, name, age, address, salary)