Pymysql增删改查

1、mysql
DDL（数据定义语言）、DML（数据操作语言）和DCL（数据控制语言）

查看服务器版本
select version();

查看所有数据库。
show databases;

切换到指定数据库。
use mysql;

查看数据库下所有表。
show tables;

安装PyMySQL。
pip install pymysql

import pymysql

#添加数据
def main():
no = int(input('编号: '))
name = input('名字: ')
loc = input('所在地: ')
# 1. 创建数据库连接对象
con = pymysql.connect(host='localhost', port=3306,
      database='hrs', charset='utf8',
      user='yourname', password='yourpass')
try:
# 2. 通过连接对象获取游标
    with con.cursor() as cursor:
# 3. 通过游标执行SQL并获得执行结果
         result = cursor.execute(
        'insert into tb_dept values (%s, %s, %s)',
         (no, name, loc)
         )
        if result == 1:
          print('添加成功!')
# 4. 操作成功提交事务
     con.commit()
finally:
# 5. 关闭连接释放资源
     con.close()


if __name__ == '__main__':
main()


#删除数据
import pymysql
def main():
no = int(input('编号: '))
con = pymysql.connect(host='localhost', port=3306,
        database='hrs', charset='utf8',
        user='yourname', password='yourpass',
        autocommit=True)
try:
    with con.cursor() as cursor:
        result = cursor.execute(
        'delete from tb_dept where dno=%s',
        (no, )
        )
        if result == 1:
            print('删除成功!')
finally:
        con.close()


if __name__ == '__main__':
    main()


#更新数据
import pymysql
def main():
no = int(input('编号: '))
name = input('名字: ')
loc = input('所在地: ')
con = pymysql.connect(host='localhost', port=3306,
        database='hrs', charset='utf8',
        user='yourname', password='yourpass',
        autocommit=True)
try:
    with con.cursor() as cursor:
        result = cursor.execute(
        'update tb_dept set dname=%s, dloc=%s where dno=%s',
        (name, loc, no)
        )
        if result == 1:
            print('更新成功!')
finally:
    con.close()


if __name__ == '__main__':
    main()



#查询数据
import pymysql
from pymysql.cursors import DictCursor

def main():
con = pymysql.connect(host='localhost', port=3306,
        database='hrs', charset='utf8',
        user='yourname', password='yourpass')
try:
    with con.cursor(cursor=DictCursor) as cursor:
        cursor.execute('select dno as no, dname as name, dloc as loc from tb_dept')
        results = cursor.fetchall()
        print(results)
        print('编号\t名称\t\t所在地')
        for dept in results:
            print(dept['no'], end='\t')
            print(dept['name'], end='\t')
            print(dept['loc'])
finally:
    con.close()


if __name__ == '__main__':
    main()