python pymysql 连接 mysql数据库进行操作

1.数据库的连接操作

import pymysql 

conn = pymysql.connect(host='localhost', user='root', passwd='123456', db='oldboydb')  

# host表示ip地址，user表示用户名，passwd表示密码，db表示数据库名称

2. 进行数据库的查询，执行select * from student 

import pymysql

conn = pymysql.connect(host='localhost', user='root', passwd='lishentao22', db='oldboydb')

# 创建游标
cursor = conn.cursor()

effect_row = cursor.execute('select * from student')
print(effect_row)  # 打印信息的条数

print(cursor.fetchone())  # 取出一条数据
print(cursor.fetchall())  # 取出剩下的数据

3. 数据的增加操作 insert into student(name, register_data, sex) values('N4', '2015-02-03', 'M') 

import pymysql

conn = pymysql.connect(host='localhost', user='root', passwd='lishentao22', db='oldboydb')

# 创建游标
cursor = conn.cursor()

# 单条数据的插入
cursor.excute('insert into student (name, register_data, sex) values("N4", "2015-02-03", "M")') 

conn.commit()

# 批量数据的插入 
data = [
    ('N1', '2015-05-22', 'M'),
    ('N2', '2015-02-22', 'F'),
    ('N3', '2012-02-22', 'F'),
]
# 进行批量插入操作
cursor.executemany('insert into student (name, register_data, sex) values(%s, %s, %s)', data)

print(cursor.lastrowid) # 获取最新的一条数据的索引值

conn.commit() 

4. 进行表User_2的创建

import pymysql

conn = pymysql.connect(host='localhost', user='root', passwd='lishentao22', db='oldboydb')

# 创建游标
cursor = conn.cursor()

sql = """
create table User_2(
id int auto_increment primary key,
name char(10) not null unique,
age tinyint not null) engine = innodb default charset='utf8';
"""

cursor.execute(sql)

conn.commit() # 进行数据的提交
cursor.close() # 关闭光标对象

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

5. 进行数据的删除操作 drop from student where name = '%s'  

import pymysql

conn = pymysql.connect(host='localhost', user='root', passwd='lishentao22', db='oldboydb')

# 创建游标
cursor = conn.cursor()
# 进行单条数据的删除操作
sq1 = 'drop from student where name = %s'
name = 'N1'
cursor.excute_many(sql, name)
conn.commit() 

# 批量删除数据
sql = 'drop from student where name = %s' 
name = ['N3', 'N4']
cursor.excute_many(sql, name) 
conn.commit()

6. 进行数据的属性内容更改  update student set sex = ‘M’ where name = ’Rain‘ and id = 16

 

import pymysql

conn = pymysql.connect(host='localhost', user='root', passwd='lishentao22', db='oldboydb')

# 创建游标
cursor = conn.cursor()

sql = 'update student set sex = "F" where name="N1" and id=16'
cursor.execute(sql)
conn.commit()

7. 数据的回滚操作

import pymysql

conn = pymysql.connect(host='localhost', user='root', passwd='lishentao22', db='oldboydb')

# 创建游标
cursor = conn.cursor()

try:
    cursor.execute('insert into hobby (id, name, hobby) values("错误的id", "xxx", "iii")')
    conn.commit()
except Exception as e:
    print(e)
    conn.rollback()