import pymysql 

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

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

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())  # 取出剩下的数据

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() 

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()

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()

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()

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()