python 连接sql数据库

1. 数据库操作

1.1 数据库连接

import pymysql
conn=pymysql.connect(host='127.0.0.1',port=3306,user='root',password='',charset='utf8')
cursor=conn.cursor()

1.2 查看数据库

#1.查看数据库
cursor.execute('show databases')
#获取所有结果
result=cursor.fetchall()
print(result)
#(('data',), ('db110',))

1.3 创建数据库

cursor.execute('create database db111 default charset=utf8 collate utf8_general_ci')
conn.commit()

1.4 删除数据库

cursor.execute('drop database db111')
conn.commit()

1.5 进入数据库

cursor.execute('use db110')
cursor.execute('show tables')
result=cursor.fetchall() 
print(result)

2. 表操作

2.1 创建表

sql="""
create table tb1(
id int not null primary key auto_increment,
title varchar(128)
)default charset=utf8;
"""
cursor.execute(sql)
conn.commit()

2.2 查看表

cursor.execute('show tables')
result1=cursor.fetchall()
print(result1)

2.3 删除表

sql="""
drop table tb1;
"""
cursor.execute(sql)
conn.commit()

3. 表数据操作

3.1 新增数据

sql="""
insert into tb2(name,password)
values('user1','1231'),('user2','1232'),('user3','1233'),('user4','1234'),('user5','1235')"""
cursor.execute(sql)
conn.commit()

3.2 删除数据

sql='delete from tb2 where id=1'
cursor.execute(sql)
conn.commit()

3.3 修改数据

# 修改数据，查不到，不修改
sql='update tb2 set name="user" where id=1'
cursor.execute(sql)
conn.commit()

3.4 查询数据

sql='select * from tb2 where id>3'
cursor.execute(sql)
result=cursor.fetchall()
print(result)

4. 事务回滚

import pymysql
conn=pymysql.connect(host='127.0.0.1',port=3306,user='root',password='',charset='utf8',db='day27db')
cursor=conn.cursor()
conn.begin()
try:
    cursor.execute('update users set amount=1 where id=100')
except Exception as e:
    print('回滚') # 提交失败，回滚
    conn.rollback()
else:
    print('提交')
    conn.commit()
cursor.close()
conn.close()