python数据库命令_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

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

try:

cursor.execute(sql)

except:

# 如果发生错误则回滚

db.rollback()

print("错误")

# 关闭数据库

db.close()