python与mysql的交互，pymysql的使用、建立连接、pymysql的查询

pycharm安装pymysql
连接数据库
- 游标
- - 游标作用
  - 代码展示
- 游标字典
- cursor.fetchmany()、cursor.fetchone()、cursor.fetchall()
- - 先cursor.fetchall()，再cursor.fetchone()
光标定位移动
- 光标绝对移动
- 光标相对移动
- - 先cursor.fetchall()，再cursor.fetchone()
  - fetchall（）返回的数据类型是一个元组，其中元组里面的每条数据还是元组
增删改
- sql = "insert into digital_image values(13,"pumysql001");"
- ret = cursor.execute(sql)
- cursor.fetchone()
- 关闭游标cursor.close()
- 关闭连接conn.close()
总结步骤

pycharm安装pymysql

导入

import pymysql

连接数据库游标游标作用

代码展示

获取游标，就是为了执行sql语句

# This is a sample Python script.

# Press Shift+F10 to execute it or replace it with your code.
# Press Double Shift to search everywhere for classes, files, tool windows, actions, and settings.
import pymysql

#建立连接（客户端连接服务端）
conn = pymysql.connect(
    host='',
    port=3306,
    user='',
    password='',
    database='',
    charset='utf8'  #不带-
)

#游标
cursor = conn.cursor()
sql = "select * from digital_image;"

ret = cursor.execute(sql)

print(ret)

print(cursor.fetchall())

def print_hi(name):
    # Use a breakpoint in the code line below to debug your script.
    print(f'Hi, {name}')  # Press Ctrl+F8 to toggle the breakpoint.


# Press the green button in the gutter to run the script.
if __name__ == '__main__':
    print_hi('PyCharm')

# See PyCharm help at https://www.jetbrains.com/help/pycharm/

但是结果，你不知道对应的哪个字段。

游标字典

# This is a sample Python script.

# Press Shift+F10 to execute it or replace it with your code.
# Press Double Shift to search everywhere for classes, files, tool windows, actions, and settings.
import pymysql

#建立连接（客户端连接服务端）
conn = pymysql.connect(
    host='',
    port=3306,
    user='',
    password='',
    database='',
    charset='utf8'  #不带-
)

#游标
cursor = conn.cursor(pymysql.cursors.DictCursor)
sql = "select * from digital_image;"

ret = cursor.execute(sql)

print(ret)

print(cursor.fetchall())

def print_hi(name):
    # Use a breakpoint in the code line below to debug your script.
    print(f'Hi, {name}')  # Press Ctrl+F8 to toggle the breakpoint.


# Press the green button in the gutter to run the script.
if __name__ == '__main__':
    print_hi('PyCharm')

# See PyCharm help at https://www.jetbrains.com/help/pycharm/

cursor.fetchmany()、cursor.fetchone()、cursor.fetchall()

# This is a sample Python script.

# Press Shift+F10 to execute it or replace it with your code.
# Press Double Shift to search everywhere for classes, files, tool windows, actions, and settings.
import pymysql

#建立连接（客户端连接服务端）
conn = pymysql.connect(
    host='',
    port=3306,
    user='',
    password='',
    database='',
    charset='utf8'  #不带-
)

#游标
cursor = conn.cursor(pymysql.cursors.DictCursor)
#默认游标取出来的数据是元组((),)
#DictCursor对应的数据结构{[]，]，如果用的是fetcheone，那么结果是{}
sql = "select * from digital_image;"

ret = cursor.execute(sql)

print(ret)
print(cursor.fetchmany())#默认一条，不写参数
print(cursor.fetchone())#取出一条
print(cursor.fetchone())#再取出一条
print(cursor.fetchall())


def print_hi(name):
    # Use a breakpoint in the code line below to debug your script.
    print(f'Hi, {name}')  # Press Ctrl+F8 to toggle the breakpoint.


# Press the green button in the gutter to run the script.
if __name__ == '__main__':
    print_hi('PyCharm')

# See PyCharm help at https://www.jetbrains.com/help/pycharm/

先cursor.fetchall()，再cursor.fetchone()

# This is a sample Python script.

# Press Shift+F10 to execute it or replace it with your code.
# Press Double Shift to search everywhere for classes, files, tool windows, actions, and settings.
import pymysql

#建立连接（客户端连接服务端）
conn = pymysql.connect(
    host='',
    port=3306,
    user='',
    password='',
    database='',
    charset='utf8'  #不带-
)

#游标
cursor = conn.cursor(pymysql.cursors.DictCursor)
#默认游标取出来的数据是元组((),)
#DictCursor对应的数据结构{[]，]，如果用的是fetcheone，那么结果是{}
sql = "select * from digital_image;"

ret = cursor.execute(sql)

print(ret)
# print(cursor.fetchmany())#默认一条，不写参数
# print(cursor.fetchone())#取出一条
# print(cursor.fetchone())#再取出一条
print(cursor.fetchall())
print("-----------------")
print(cursor.fetchone())


def print_hi(name):
    # Use a breakpoint in the code line below to debug your script.
    print(f'Hi, {name}')  # Press Ctrl+F8 to toggle the breakpoint.


# Press the green button in the gutter to run the script.
if __name__ == '__main__':
    print_hi('PyCharm')

# See PyCharm help at https://www.jetbrains.com/help/pycharm/

光标定位移动光标绝对移动

# This is a sample Python script.

# Press Shift+F10 to execute it or replace it with your code.
# Press Double Shift to search everywhere for classes, files, tool windows, actions, and settings.
import pymysql

#建立连接（客户端连接服务端）
conn = pymysql.connect(
    host='',
    port=3306,
    user='root',
    password='',
    database='',
    charset='utf8'  #不带-
)

#游标
cursor = conn.cursor(pymysql.cursors.DictCursor)
#默认游标取出来的数据是元组((),)
#DictCursor对应的数据结构{[]，]，如果用的是fetcheone，那么结果是{}
sql = "select * from digital_image;"

ret = cursor.execute(sql)

print(ret)
# print(cursor.fetchmany())#默认一条，不写参数
# print(cursor.fetchone())#取出一条
# print(cursor.fetchone())#再取出一条
print(cursor.fetchall())
print("-----------------")

cursor.scroll(2,'absolute')#光标  absolute 绝对移动  2:移动多少条记录

print(cursor.fetchone())


def print_hi(name):
    # Use a breakpoint in the code line below to debug your script.
    print(f'Hi, {name}')  # Press Ctrl+F8 to toggle the breakpoint.


# Press the green button in the gutter to run the script.
if __name__ == '__main__':
    print_hi('PyCharm')

# See PyCharm help at https://www.jetbrains.com/help/pycharm/

光标相对移动

# This is a sample Python script.

# Press Shift+F10 to execute it or replace it with your code.
# Press Double Shift to search everywhere for classes, files, tool windows, actions, and settings.
import pymysql

#建立连接（客户端连接服务端）
conn = pymysql.connect(
    host='',
    port=3306,
    user='root',
    password='',
    database='',
    charset='utf8'  #不带-
)

#游标
cursor = conn.cursor(pymysql.cursors.DictCursor)
#默认游标取出来的数据是元组((),)
#DictCursor对应的数据结构{[]，]，如果用的是fetcheone，那么结果是{}
sql = "select * from digital_image;"

ret = cursor.execute(sql)

print(ret)
# print(cursor.fetchmany())#默认一条，不写参数
# print(cursor.fetchone())#取出一条
# print(cursor.fetchone())#再取出一条
# print(cursor.fetchall())
print("-----------------")

# cursor.scroll(2,'absolute')#光标  absolute 绝对移动  2:移动多少条记录  从数据最开始向下移动
cursor.scroll(2,'relative')#光标  relative 相对移动  2:移动多少条记录 按照当前光标位置向下移动

print(cursor.fetchone())


def print_hi(name):
    # Use a breakpoint in the code line below to debug your script.
    print(f'Hi, {name}')  # Press Ctrl+F8 to toggle the breakpoint.


# Press the green button in the gutter to run the script.
if __name__ == '__main__':
    print_hi('PyCharm')

# See PyCharm help at https://www.jetbrains.com/help/pycharm/

先cursor.fetchall()，再cursor.fetchone()

# This is a sample Python script.

# Press Shift+F10 to execute it or replace it with your code.
# Press Double Shift to search everywhere for classes, files, tool windows, actions, and settings.
import pymysql

#建立连接（客户端连接服务端）
conn = pymysql.connect(
    host='',
    port=3306,
    user='',
    password='',
    database='',
    charset='utf8'  #不带-
)

#游标
cursor = conn.cursor(pymysql.cursors.DictCursor)
#默认游标取出来的数据是元组((),)
#DictCursor对应的数据结构{[]，]，如果用的是fetcheone，那么结果是{}
sql = "select * from digital_image;"

ret = cursor.execute(sql)

print(ret)
# print(cursor.fetchmany())#默认一条，不写参数
# print(cursor.fetchone())#取出一条
# print(cursor.fetchone())#再取出一条
# print(cursor.fetchall())
print("-----------------")
print(cursor.fetchall())
# cursor.scroll(2,'absolute')#光标  absolute 绝对移动  2:移动多少条记录  从数据最开始向下移动
cursor.scroll(2,'relative')#光标  relative 相对移动  2:移动多少条记录 按照当前光标位置向下移动
print(cursor.fetchone())


def print_hi(name):
    # Use a breakpoint in the code line below to debug your script.
    print(f'Hi, {name}')  # Press Ctrl+F8 to toggle the breakpoint.


# Press the green button in the gutter to run the script.
if __name__ == '__main__':
    print_hi('PyCharm')

# See PyCharm help at https://www.jetbrains.com/help/pycharm/

fetchall（）返回的数据类型是一个元组，其中元组里面的每条数据还是元组

# This is a sample Python script.

# Press Shift+F10 to execute it or replace it with your code.
# Press Double Shift to search everywhere for classes, files, tool windows, actions, and settings.
import pymysql

#建立连接（客户端连接服务端）
conn = pymysql.connect(
    host='',
    port=3306,
    user='root',
    password='',
    database='',
    charset='utf8'  #不带-
)

#游标
cursor = conn.cursor(pymysql.cursors.DictCursor)
#默认游标取出来的数据是元组((),)
#DictCursor对应的数据结构{[]，]，如果用的是fetcheone，那么结果是{}
sql = "select * from digital_image;"

ret = cursor.execute(sql)

print(ret)
# print(cursor.fetchmany())#默认一条，不写参数
# print(cursor.fetchone())#取出一条
# print(cursor.fetchone())#再取出一条
# print(cursor.fetchall())
print("-----------------")
result = cursor.fetchall()
for row in result:
    print(row)
# cursor.scroll(2,'absolute')#光标  absolute 绝对移动  2:移动多少条记录  从数据最开始向下移动
# cursor.scroll(2,'relative')#光标  relative 相对移动  2:移动多少条记录 按照当前光标位置向下移动


def print_hi(name):
    # Use a breakpoint in the code line below to debug your script.
    print(f'Hi, {name}')  # Press Ctrl+F8 to toggle the breakpoint.


# Press the green button in the gutter to run the script.
if __name__ == '__main__':
    print_hi('PyCharm')

# See PyCharm help at https://www.jetbrains.com/help/pycharm/

增删改

# This is a sample Python script.

# Press Shift+F10 to execute it or replace it with your code.
# Press Double Shift to search everywhere for classes, files, tool windows, actions, and settings.
import pymysql

#建立连接（客户端连接服务端）
conn = pymysql.connect(
    host='',
    port=3306,
    user='',
    password='',
    database='',
    charset='utf8'  #不带-
)

#游标
cursor = conn.cursor(pymysql.cursors.DictCursor)
#默认游标取出来的数据是元组((),)
#DictCursor对应的数据结构{[]，]，如果用的是fetcheone，那么结果是{}


sql = "insert into digital_image values(13,"pumysql001");"

ret = cursor.execute(sql)
print(ret)
print(cursor.fetchall())

#增删改都必须进行提交操作(commit)
conn.commit()

def print_hi(name):
    # Use a breakpoint in the code line below to debug your script.
    print(f'Hi, {name}')  # Press Ctrl+F8 to toggle the breakpoint.


# Press the green button in the gutter to run the script.
if __name__ == '__main__':
    print_hi('PyCharm')

# See PyCharm help at https://www.jetbrains.com/help/pycharm/

增删改都必须进行提交操作(commit)

sql = “insert into digital_image values(13,“pumysql001”);”

准备sql，之前再mysql客户端如何编写失去了，再python里面就怎么写

ret = cursor.execute(sql)

执行sql语句

cursor.fetchone()

执行查询的结果，返回元组

关闭游标cursor.close() 关闭连接conn.close() 总结步骤

1.导包
2.创建连接对象
3.获取游标，目的是要执行sql语句
4.执行sql语句
5.关闭游标
6.关闭连接

python与mysql的交互，pymysql的使用、建立连接、pymysql的查询

Python相关栏目本月热门文章