python用pymysql模块操作数据库MySQL,实现查增删改

这里主要写四个函数，查增删改

下面是目标数据库的样式，数据库：testpy，数据表：movie1，（拼错了不改了,猪啊），有四个字段，id是主键。

 查，下面的例子加个函数名就可以运行了

#查select
def select():
    #创建connection连接
    conn = pymysql.connect(
        host = 'localhost',
        port = 3306,
        user = 'root',
        password = 'mysql_123456',
        database = 'testpy',
        charset = 'utf8'
    )
    #获得cursor对象
    cursor = conn.cursor()
    #SQL语句
    sql = "select daoyan,dizhi,mingzi from movie1 where daoyan='NA';"
    #执行语句
    cursor.execute(sql)
    #获取查询数据
    #ret = cursor.fetchone()
    #ret = cursor.fetchmany(3)
    ret = cursor.fetchall()
    #关闭对象，连接
    cursor.close()
    conn.close()
    #打印结果
    for i in ret:
        print(i)

增

#增：insert
def insert():
    #创建connection连接
    conn = pymysql.connect(
        host = 'localhost',
        port = 3306,
        user = 'root',
        password = 'mysql_123456',
        database = 'testpy',
        charset = 'utf8'
    )
    #获得cursor对象
    cursor = conn.cursor()
    #SQL语句
    sql = "insert into movie1 values ('0','luke','http://www.luke.com','你妹');"
    #执行语句
    cursor.execute(sql)
    #提交事务
    conn.commit()
    last_id = cursor.lastrowid
    #返回插入的id号码
    print(last_id)
    #关闭对象，连接
    cursor.close()
    conn.close()

删

#删：delete
def delete():
    #创建connection连接
    conn = pymysql.connect(
        host = 'localhost',
        port = 3306,
        user = 'root',
        password = 'mysql_123456',
        database = 'testpy',
        charset = 'utf8'
    )
    #获得cursor对象
    cursor = conn.cursor()
    #SQL语句
    sql = "delete from movie1 where id =149 ;"
    #执行语句
    cursor.execute(sql)
    #提交事务
    conn.commit()
    #关闭对象，连接
    cursor.close()
    conn.close()

改

#改：update
def update():
    #创建connection连接
    conn = pymysql.connect(
        host = 'localhost',
        port = 3306,
        user = 'root',
        password = 'mysql_123456',
        database = 'testpy',
        charset = 'utf8'
    )
    #获得cursor对象
    cursor = conn.cursor()
    #SQL语句
    sql = "update movie1 set daoyan='luke' where id=2;"
    #执行语句
    cursor.execute(sql)
    #提交事务
    conn.commit()
    #关闭对象，连接
    cursor.close()
    conn.close()

上面的四个函数写在一个py里，调用函数即可，自己备份一下用法。