Pycharm 操作数据库 |
您所在的位置：网站首页 › 修改数据库名字代码怎么写 › Pycharm 操作数据库 |
Pycharm 操作数据库
|
view--->Tool Buttons，点击Pycharm右侧的Database 1、连接数据库
2、建立一个表，添加数据
通过以上操作把用户名和密码储存到了数据库中 3、连接登录数据库 连接数据库 # 1. 连接数据库 conn = pymysql.connect( host="localhost", port=3306, # 端口号是数字类型 database="userinfo", # 写自己本地的数据库名字 user="root", password="3822515", charset="utf8" # 千万记得没有- ) cursor = conn.cursor() # 获取输入SQL语句的光标对象 sql = "select * from info;" ret = cursor.execute(sql) print(ret) # 关闭连接 cursor.close() conn.close() 
import pymysql
username = input("输入用户名：")
pwd = input("请输入密码：")
# if username == "erge" and pwd == "dashabi":
# print("登陆成功！")
# else:
# print("滚~")
# 拿到用户输入的用户名密码
# 去数据库里面判断用户名和密码是否正确
# 1. 连接数据库
conn = pymysql.connect(
host="localhost",
port=3306, # 端口号是数字类型
database="userinfo", # 写自己本地的数据库名字
user="root",
password="3822515",
charset="utf8" # 千万记得没有-
)
cursor = conn.cursor() # 获取输入SQL语句的光标对象
sql = "select * from info;"
ret = cursor.execute(sql)
print(ret)
# 关闭连接
cursor.close()
conn.close()
# 2. 判断 --> 只需要把检索条件写到sql语句中，去数据库执行就可以了
# with open("userinfo.txt", "r", encoding="utf-8") as f:
# for line in f:
# # print(line.strip())
# u, p = line.strip().split("|")
# if u == username and p == pwd:
# print("登陆成功！")
# break
# else:
# print("go out~")
Python连接数据库登录
import pymysql
# 获取用户输入
username = input("输入用户名：")
pwd = input("请输入密码：")
# 连接数据库检索有没有该用户
conn = pymysql.connect(
host="localhost",
port=3306,
database="userinfo",
user="root",
password="3822515",
charset="utf8"
)
cursor = conn.cursor() # 获取光标
# 拼接要执行的SQL语句
sql = "select * from info where username='%s' and password='%s'" % (username, pwd)
print(sql)
print("=" * 120)
# 执行SQL语句
ret = cursor.execute(sql)
if ret:
print("登录成功")
else:
print("登录失败！")
# 关闭光标对像
cursor.close()
# 关闭连接
conn.close()
登录校验MySQL
登录校验MySQL--规避SQL注入 import pymysql # 获取用户输入 username = input("输入用户名：") pwd = input("请输入密码：") # 连接数据库检索有没有该用户 conn = pymysql.connect( host="localhost", port=3306, database="userinfo", user="root", password="3822515", charset="utf8" ) cursor = conn.cursor() # 获取光标 # 拼接要执行的SQL语句 sql = 'select * from info where username=%s and password=%s' print(sql) print("=" * 120) # 执行SQL语句 ret = cursor.execute(sql, [username, pwd]) # 让pymysql帮我们拼接SQL语句 if ret: print("登录成功") else: print("登录失败！") # 关闭光标对像 cursor.close() # 关闭连接 conn.close()4、操作数据库 增删改查参考
"""
pymysql增操作
"""
import pymysql
conn = pymysql.connect(
host="localhost",
port=3306,
database="userinfo",
user="root",
password="3822515",
charset="utf8"
)
cursor = conn.cursor()
# 拼接语句
sql = "insert into info (username, password)VALUES (%s, %s)"
# 执行
data=("Ale",1882)
try:
cursor.execute(sql, data)
conn.commit()
except Exception as e:
print("报错啦：",str(e))
conn.rollback() # 回滚
# 对数据库做写操作一定要记得提交assword
cursor.close()
conn.close()
pymysql增操作
import pymysql
conn = pymysql.connect(
host="localhost",
port=3306,
database="userinfo",
user="root",
password="3822515",
charset="utf8"
)
cursor = conn.cursor()
# 创建班级的sql语句
sql = "insert into info (username, password) VALUES (%s, %s)"
data = [("alex1", "dashabi"), ("污Sir1","sha"), ("xiaoyima1", "nvshen")]
try:
cursor.executemany(sql, data) # 内部实现for循环，批量执行插入语句
# for i in data:
# cursor.execute(sql, i)
conn.commit() # 提交一次
except Exception as e:
print("错啦！")
conn.rollback()
cursor.close()
conn.close()
批量插入数据
import pymysql
conn = pymysql.connect(
host="localhost",
port=3306,
database="userinfo",
user="root",
password="3822515",
charset="utf8"
)
cursor = conn.cursor()
# sql = "delete from info WHERE username=%s"
sql = "delete from info WHERE id=%s"
cursor.execute(sql,8)
conn.commit()
cursor.close()
conn.close()
删除数据操作
import pymysql
conn = pymysql.connect(
host="localhost",
port=3306,
database="userinfo",
user="root",
password="3822515",
charset="utf8"
)
cursor = conn.cursor()
sql = "update info set password=%s where username=%s"
cursor.execute(sql,["123456", "Ale"])
conn.commit()
cursor.close()
conn.close()
修改数据操作
import pymysql
conn = pymysql.connect(
host="localhost",
port=3306,
database="userinfo",
user="root",
password="3822515",
charset="utf8"
)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # 指定返回的数据格式为字典格式
sql = "select * from info"
cursor.execute(sql) # 返回的不是具体的数据而是受影响的行数
# ret = cursor.fetchall() # 返回所有的数据
# ret = cursor.fetchone() # 返回第一条的数据
# print(ret)
# ret = cursor.fetchone() # 接着上一条返回一条数据
# print(ret)
ret = cursor.fetchmany(3) # 查询具体多少条数据，这里查询前三条数据
print(ret)
# cursor.scroll(0, mode="absolute") # 绝对移动，写多少就是移到多少，这里从第1条数据开始查
cursor.scroll(-1, mode="relative") #相对移动，这里-1表示光标往前移动数据到第2条数据，从第3条数据开始查
ret = cursor.fetchall()
print(ret)
cursor.close()
conn.close()
查询数据操作
import pymysql
conn = pymysql.connect(
host="localhost",
port=3306,
database="userinfo",
user="root",
password="3822515",
charset="utf8"
)
cursor = conn.cursor()
# 创建班级的sql语句
sql1 = "insert into class (name) VALUES (%s)"
# 创建学生的sql语句
sql2 = "insert into student (name, cid) VALUES (%s, %s)"
cursor.execute(sql1, "全栈9期")
new_id = cursor.lastrowid # 获取刚插入数据的ID值
cursor.execute(sql2, ["小东北", new_id])
conn.commit()
cursor.close()
conn.close()
lastrowid作用
|
【本文地址】
今日新闻 |
推荐新闻 |