今天我们来学习一下下数据库的使用。
#一.连接数据库
import pymysql
一.连接数据库
conn=pymysql.connect(host="localhost", user="root", password="123456", database="pymysql_demo",port=3306)
cursor=conn.cursor()
cursor.execute("select 1")
result=cursor.fetchone()
print(result)
conn.close()
#二.插入数据的两种方式
conn=pymysql.connect(host="localhost", user="root", password="123456", database="pymysql_demo",port=3306)
cursor=conn.cursor()
1.
sql='''
insert into user(id,username,age,password) values(2,'asqq',19,'111222')
'''
cursor.execute(sql)#执行sql
conn.commit()#连接到库
conn.close()
#2.
sql='''
insert into user(id,username,age,password) alues(null,%s,%s,%s)
'''
username='das'
age=24
password='1114455'
cursor.execute(sql,(username,age,password))
conn.commit()#连接到库
conn.close()
三.查找数据
#三.查找数据
conn=pymysql.connect(host="localhost", user="root", password="123456", database="pymysql_demo",port=3306)
cursor=conn.cursor()
sql='select * from user'
sql='select id,username from user where age=19'
#########fetchone###########
sql='select * from user '
cursor.execute(sql)
while True:
result=cursor.fetchone()
if result:
print(result)
else:
break
conn.close()
########fetchall###########
sql='select * from user'
cursor.execute(sql)
results=cursor.fetchall()
for result in results:
print(result)
########fetchmany#############
sql='select * from user'
cursor.execute(sql)
results=cursor.fetchmany(2)
for result in results:
print(result)
conn.close()
四.插入,更新数据
#四.删除和更新数据库
conn=pymysql.connect(host="localhost", user="root", password="123456", database="pymysql_demo",port=3306)
########删除#######
cursor=conn.cursor()
sql='delete from user where id=1'
cursor.execute(sql)
conn.commit()
#插入,删除,更新都需要commit
conn.close()
######更新#########
cursor=conn.cursor()
sql='update user set username="qqqqq" where id=13'
cursor.execute(sql)
conn.commit()
conn.close()