PyMySQL使用指南

0. 引用P有MySQL
import mysql
1. 创建connection和cursor对象
connection = pymysql.connect(host='localhost',
                             user='username',
                             password='passwd',
                             db='dbname')

cursor = connection.cursor()
2. 创建数据库表
create_sql = """
create table if not exists blogs(
    id int not null primary key auto_increment,
    blog_name varchar(50) not null,
    blog_content varchar(1000) not null,
    blog_date date not null,
    key blog_name(blog_name),
    key blog_date(blog_date)
)
"""

print(create_sql)
cursor.execute(create_sql)
connection.commit()
3. 插入数据
insert_template_sql = "insert into blogs(blog_name, blog_content, blog_date) values(%s, %s, %s)"

data_list = [
    ('Blog One', 'this is content 1', '2018-10-19'),
    ('Blog Two', 'this is content 2', '2017-10-19'),
]

for data in data_list:
    cursor.execute(insert_template_sql, data)

connection.commit()
4. 查询数据
select_sql = "select blog_name, blog_content, blog_date from blogs where blog_name=%s"

cursor.execute(select_sql, ('Blog One'))

rows = cursor.fetchall()
print(rows)

select_sql = 'select * from blogs where blog_date < %s order by blog_date desc '
cursor.execute(select_sql, ('2018-01-01'))
rows = cursor.fetchall()
print(rows)
5. 更改数据
update_sql = "update blogs set blog_name=%s where blog_name=%s"
res = cursor.execute(update_sql, ('Blog Title One', 'Blog One'))
print(res)
connection.commit()
6. 删除数据
delete_sql = "delete from blogs where blog_name=%s"
res = cursor.execute(delete_sql, ('Blog Two'))
print(res)
connection.commit()

res返回1表示修改成功,0修改失败。因为pymysql默认不是自动提交,需要在修改数据后commit以下。

7.关闭连接
cursor.close()
connection.close()
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值