新建数据库
# 连接
db = pymysql.connect(host='localhost', user='root', password='@ROOT_root_123', port=3306)
cursor = db.cursor()
try:
sql = 'create DATABASE ziMing DEFAULT CHARACTER SET utf8'
cursor.execute(sql)
except Exception as e:
print(e)
在数据库中新建数据表
此时,在链接数据库时要绑定数据库,db=‘ziMing’
db = db = pymysql.connect(host='localhost', user='root', password='@ROOT_root_123', port=3306, db='ziMing')
cursor = db.cursor()
sql = 'create table if not exists students(id VARCHAR (255) NOT NULL, name VARCHAR(255) NOT NULL, age INT NOT NULL, PRIMARY KEY (id))'
try:
cursor.execute(sql)
except Exception as e:
print(e)
增加数据1.0
db = db = pymysql.connect(host='localhost', user='root', password='@ROOT_root_123', port=3306, db='ziMing')
cursor = db.cursor()
id = '100'
name = 'ziMing'
age = '18'
sql = 'insert into students(id, name, age) VALUES(%s, %s, %s)'
try:
cursor.execute(sql, (id, name, age))
print('success')
except Exception as e:
print(e)
db.rollback()
增加数据2.0
利用字典实现动态增加数据
db = db = pymysql.connect(host='localhost', user='root', password='@ROOT_root_123', port=3306, db='ziMing')
cursor = db.cursor()
data = {
'id': '101',
'name': 'Michael',
'age': 16
}
table = 'students'
keys = ', '.join(data.keys())
print(keys)
values = ', '.join(['%s'] * len(data))
print(values)
sql = 'insert into {table}({keys}) VALUES ({values})'.format(table=table, keys=keys, values=values)
try:
cursor.execute(sql, tuple(data.values()))
db.commit()
print('Success')
except Exception as e:
print(e)
db.rollback()
finally:
db.close()
查看数据1.0
db = db = pymysql.connect(host='localhost', user='root', password='@ROOT_root_123', port=3306, db='ziMing')
cursor = db.cursor()
sql = 'select * from students WHERE age > 1'
try:
cursor.execute(sql)
print('count:', cursor.rowcount)
data = cursor.fetchone()
print('data:', data)
datas = cursor.fetchall()
print('datas:', datas)
for row in datas:
print(row)
print('success')
except Exception as e:
print(e)
db.rollback()
查看数据2.0
用while循环查询每一行的数据,fetchall()的内存开销
db = db = pymysql.connect(host='localhost', user='root', password='@ROOT_root_123', port=3306, db='ziMing')
# cursor = db.cursor()
# sql = 'select * from students WHERE age > 1'
# try:
# cursor.execute(sql)
# print('count:', cursor.rowcount)
# data = cursor.fetchone()
# while data:
# print(data)
# data = cursor.fetchone()
# except Exception as e:
# print(e)
# db.rollback()
修改数据
用where定位,再用update修改
db = db = pymysql.connect(host='localhost', user='root', password='@ROOT_root_123', port=3306, db='ziMing')
cursor = db.cursor()
sql = 'update students set age = %s WHERE name = %s'
try:
cursor.execute(sql, (36, 'Janel'))
db.commit()
print('Success')
except Exception as e:
print(e)
db.rollback()
finally:
db.close()