[大数据]pymysql Python 操作 MySQL

import pymysql

# 判断数据库中是否有指定用户名和密码
username = 'xaw'
pwd = '123456'

# 创建连接
conn = pymysql.connect(
    host='127.0.0.1',
    user='root',
    password='666660',
    database='database_2',
    port=3306,
    #charset=utf8,
)

# 创建游标
cur = conn.cursor()
sql = "select * from user where name = '{}' and pwd = '{}' ".format(username, pwd)
# 检测数据是否存在

rescount = cur.execute(sql)  # 成功返回1失败返回0
print(rescount)

# 关闭
cur.close
conn.close

import pymysql

# 判断数据库中是否有指定用户名和密码
username = 'xaw'
pwd = '123456'

# 创建连接
conn = pymysql.connect(
    host='127.0.0.1',
    user='root',
    password='666660',
    database='database_2',
    port=3306,
    #charset=,
)

# 创建游标
cur = conn.cursor()
sql = "select * from user where name = %s and pwd = %s "  # 检测数据是否存在

# 防止mql注入
rescount = cur.execute(sql,(username,pwd))  # 成功返回1失败返回0
print(rescount)

# 关闭
cur.close
conn.close

import pymysql

id = 5
username = 'zwh0'
pwd = '1234560'


# 创建连接
conn = pymysql.connect(
    host='127.0.0.1',
    user='root',
    password='666660',
    database='database_2',
    port=3306,
    #charset=,
)

# 创建游标
cur = conn.cursor()
sql = "insert into user values(%s,%s,%s,001)"

rescount = cur.execute(sql,(id,username,pwd))  # 成功返回1失败返回0
print(rescount)

# 插入 删除 更新数据 要使用commit()
conn.commit()

# 关闭
cur.close
conn.close

import pymysql

# 创建连接
conn = pymysql.connect(
    host='127.0.0.1',
    user='root',
    password='666660',
    database='database_2',
    port=3306,
    #charset='utf8',
)

# 创建游标
cur = conn.cursor()
sql = "select * from user"

rescount = cur.execute(sql)  # 成功返回1失败返回0
print(rescount)

 # 一行数据 一行数据 读取
row = cur.fetchone()
print(row)
row = cur.fetchone()
print(row)
row = cur.fetchone()
print(row)


# 关闭
cur.close
conn.close

import pymysql

# 创建连接
conn = pymysql.connect(
    host='127.0.0.1',
    user='root',
    password='666660',
    database='database_2',
    port=3306,
    #charset='utf8',
)

# 创建游标
cur = conn.cursor()
sql = "select * from user"

rescount = cur.execute(sql)  # 成功返回1失败返回0
print(rescount)


rows = cur.fetchmany(1)  # 读一个
print(rows)
rows = cur.fetchmany(2)  # 读两个
print(rows)


# 关闭
cur.close
conn.close

import pymysql

# 创建连接
conn = pymysql.connect(
    host='127.0.0.1',
    user='root',
    password='666660',
    database='database_2',
    port=3306,
    #charset='utf8',
)

# 创建游标
cur = conn.cursor(cursor=pymysql.cursors.DictCursor) # 返回字典
sql = "select * from user"

rescount = cur.execute(sql)  # 成功返回1失败返回0
print(rescount)


rows = cur.fetchmany(1)  # 读一个
print(rows)
rows = cur.fetchmany(2)  # 读两个
print(rows)


# 关闭
cur.close
conn.close