• python操作MySQL
• 一、pymysql模块
• 二、pymysql补充说明
  • 1.获取数据
  • 2.增删改查
• 三、注册登录
  • 1.注册
  • 2.登录

python操作MySQL

一、pymysql模块

import pymysql
 
# 连接mysql，输入root账户和密码
conn = pymysql.connect(
  host='127.0.0.1', 
  port=3306, user='root', 
  passwd='root123', 
  db='db4',
  charset='utf8'
)
# 产生游标对象
cursor = conn.cursor()   # 括号内不填写额外参数 数据是元组 指定性不强  [(),()]  
"""
指定参数为 列表内套字典 [{},{}]
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
"""

# 1 新增
# execute也有返回值 接收的是SQL语句影响的行数
cursor.execute("create database db4 default charset utf8 collate utf8_general_ci")  
conn.commit()
 
# 2 查看数据库
cursor.execute("show databases")
cursor.execute("use db4")
# 查看数据表
cursor.execute("show tables")
result = cursor.fetchall()
conn.commit()
 
# 3 删除数据库db4
cursor.execute("drop database db4")
conn.commit()
 
 
# 4 用mysql查看其中的数据表
cursor.execute("use mysql")
cursor.execute("show tables")
res = cursor.fetchall()
print(res)  # (('columns_priv',), ('db',), ('engine_cost',), ('event',), ('func',), ('general_log',), ('gtid_executed',), ('help_category',), ('help_keyword',), ('help_relation',), ...
 
# 5 关闭连接
cursor.close()
conn.close()

二、pymysql补充说明

1.获取数据

2.增删改查

三、注册登录

1.注册

import pymysql
def register():
    print('用户注册')
    # 1 获取用户输入
    username = input('请输入用户名：').strip()
    pwd = input('请输入密码：').strip()
    # 2 获取用户连接mysql数据库
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', charset='utf8', db='usersdb')
    cursor = conn.cursor()
    # 3 编写sql语句
    sql_statement = "insert into users(name,password) values('{}','{}') ".format(username, pwd)

    # 4 连接mysql执行sql语句
    cursor.execute(sql_statement)
    conn.commit()

    # 5 关闭数据库连接
    cursor.close()
    conn.close()

    print('注册成功')

2.登录

def login():
    print('用户登录')

    # 1 获取用户信息
    username = input('请输入用户名：')
    pwd = input('请输入密码：')

    # 2 连接mysql服务端
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', db='usersdb')
    cursor = conn.cursor()
    # 3 编写sql语句

    # sql_statement = "select * from users where name='{}' and password='{}'".format(username, pwd)
    sql_statement = "select * from users where name='%s' and password='123'" % (username)
    # sql_statement = "select * from users where name='' or 1=1 -- ' and password='123'"
    # 4 执行sql语句
    cursor.execute(sql_statement)

    # cursor.execute("select * from users where name=%s and password=%s", [username, pwd])
    # 5 获取结果
    res = cursor.fetchone()

    # 6 断开与mysql的连接
    cursor.close()
    conn.close()

    # 7 判断返回的信息
    if res:
        print('登录成功', res)
    else:
        print('登录失败', res)

注意：SQL注入的问题