python 数据库的相关操作

1 安装pymysql

---

1.使用命令窗口

     pip install pymysql

2.使用anaconda3的conda命令安装：

     Conda install pymysql

3.在py文件中引入pymysql          

4.检查是否安装成功： import pymysql

---

2 常用操作

---

---

3 连接数据库

---

import pymysql

# 获取连接，关键字+值

db=pymysql.connect('localhost','root','root','test1')

db=pymysql.connect(host='localhost',user='root',passwd='root',db='test1',charset="utf8")

# 获取游标

cursor=db.cursor()

# 查讯版本

sql='select version()'

try:

           cursor.execute(sql)

# 从结果集中得到所有记录

           data = cursor.fetchall()

           print(data)

except:

# 如果发生错误则回滚

        db.rollback()

        print("错误")

# 关闭数据库

db.close()

---

4 创建表

---

import pymysql

# 获取连接，关键字+值

db=pymysql.connect('localhost','root','root','test1')

db=pymysql.connect(host='localhost',user='root',passwd='root',db='test1',charset="utf8")

# 获取游标

cursor=db.cursor()

sql="""create table `employee`(

    `first_name` CHAR(20) not null,

    `last_name` CHAR(20),

    `age` int,

    `sex` CHAR(2),

    `income` FLOAT )"""

try:

           cursor.execute(sql)

except:

# 如果发生错误则回滚

        db.rollback()

        print("错误")

# 关闭数据库

db.close()

---

5 插入数据

---

import pymysql

# 获取连接，关键字+值

db=pymysql.connect('localhost','root','root','test1')

db=pymysql.connect(host='localhost',user='root',passwd='root',db='test1',charset="utf8")

# 获取游标

cursor=db.cursor()

# 插入一条数据

sql="""insert into employee (first_name,last_name,age,sex,income)VALUES (

'Mac','Mohan',20,'M',200000)"""

try:

           cursor.execute(sql)

except:

# 如果发生错误则回滚

        db.rollback()

        print("错误")

# 关闭数据库

db.close()

---

import pymysql

# 获取连接，关键字+值

db=pymysql.connect('localhost','root','root','test1')

db=pymysql.connect(host='localhost',user='root',passwd='root',db='test1',charset="utf8")

# 获取游标

cursor=db.cursor()

# 插入多条数据

sql="""insert into employee (first_name,last_name,age,sex,income)VALUES (%s,%s,%s,%s,%s)"""

try:

        param=(

           ('Dava','Bekha',20,'M',20000),

           ('Mckean','Grace',20,'W',30000),

          )

         cursor.executemany(sql,param)

except:

# 如果发生错误则回滚

        db.rollback()

        print("错误")

# 关闭数据库

db.close()

---

6 更新数据

---

import pymysql

# 获取连接，关键字+值

db=pymysql.connect('localhost','root','root','test1')

db=pymysql.connect(host='localhost',user='root',passwd='root',db='test1',charset="utf8")

# 获取游标

cursor=db.cursor()

# 更改数据

sql="""update employee set age=age+1 where first_name='Davad'"""

# sql="""update employee set age=age+1 where sex='%c'"""%('M')

# sql="""update employee set age=age+1 where sex='M'"""

try:

           cursor.execute(sql)

except:

# 如果发生错误则回滚

        db.rollback()

        print("错误")

# 关闭数据库

db.close()

---

7 查看数据

---

import pymysql

# 获取连接，关键字+值

db=pymysql.connect('localhost','root','root','test1')

db=pymysql.connect(host='localhost',user='root',passwd='root',db='test1',charset="utf8")

# 获取游标

cursor=db.cursor()

sql="""select * from employee where income>%d"""%(1000)

try:

           cursor.execute(sql)

except:

# 如果发生错误则回滚

        db.rollback()

        print("错误")

# 关闭数据库

db.close()

---

8 删除数据

---

import pymysql

# 获取连接，关键字+值

db=pymysql.connect('localhost','root','root','test1')

db=pymysql.connect(host='localhost',user='root',passwd='root',db='test1',charset="utf8")

# 获取游标

cursor=db.cursor()

sql="""delete from employee where age<21"""

# sql="""delete from employee where age<21"""

# sql="""delete from employee where age<%d """%(20)

# sql="""delete from employee where age>%d"""%(20)

try:

           cursor.execute(sql)

except:

# 如果发生错误则回滚

        db.rollback()

        print("错误")

# 关闭数据库

db.close()