pymysql操作mysql

目录

• pymysql操作mysql
  • 安装,启动及链接
    • MySQL
    • pymysql
  • 链接数据库
  • 创建表
  • 事务性ACID
  • 插入数据
  • 更新数据
  • 删除数据
  • 查询操作

pymysql操作mysql

安装,启动及链接

返回目标

MySQL

• 官方网站：https://www.mysql.com/cn
• 下载地址：https://www.mysql.com/cn/downloads

• 中文教程：http://www.runoob.com/mysql/mysql-tutorial.html

• 安装mysql:
  sudo apt-get install -y mysql-server mysql-client

• 启动、关闭和重启MySQL服务:
  sudo service mysql start
  sudo service mysql stop
  sudo service mysql restart
  sudo service mysql status:查看状态
  netstat -an|grep 3306:查看mysql是否启动成功,监听3306端口

msqylclient

安装:sudo apt-get install libmysqlclient-dev       pip install mysqlclient

参考:https://www.cnblogs.com/zyever/p/9449344.html

pymysql

• GitHub：https://github.com/PyMySQL/PyMySQL
• 官方文档：http://pymysql.readthedocs.io/
• PyPI：https://pypi.python.org/pypi/PyMySQL

安装pymysql:
pip3 install pymysql

链接数据库

返回目标

链接之前,要启动mysql

import pymysql  # 1.导入pymysql包

db = pymysql.connect(host='localhost', user='root',
    password='123456',port=3306)  # 2.声明一个MySQL连接对象db,在远程host传入其公网ip
cursor =db.cursor()  # 3.获得操作游标
cursor.execute('SELECT VERSION()')  # 4.通过游标进行操作,execute()执行sql语句
data = cursor.fetchone()  # 获得第一条数据
print('Database version:', data)
cursor.execute("CREATE DATABASE spiders DEFAULT CHARACTER SET utf8") # 创建数据库,默认utf-8编码
db.close()  # 5.关闭连接

创建表

返回目标

创建数据库后，在连接时需要额外指定一个参数db

import pymysql

db = connect(host='localhost', user='root',
    password='123456', port=3306, db='spiders')  #创建数据库后，在连接时需要额外指定一个参数db
cursor = db.cursor()
cursor.execute('CREAT TABLE IF NOT EXISTS students (id VARCHAR(255) NOT NULL, name VARCHAR(255) NOT NULL, age INT NOT NULL, PRIMARY KEY (id))')
db.close()

事务性ACID

属性	解释
原子性（atomicity）	事务是一个不可分割的工作单位，事务中包括的诸操作要么都做，要么都不做
一致性（consistency）	事务必须使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的
隔离性（isolation）	一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰
持久性（durability）	持续性也称永久性（permanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响

• 更改操作(增删改)事务性标准写法：

try:
    cursor.execute(sql)
    cursor.commit()  # 提交,数据才被真正写到了数据库中
except:
    db.rollback()  # 回滚操作,相当与没有进行操作

插入数据

返回目标

• sql语法:

INSERT INTO table_name ( field1, field2,...fieldN )
                       VALUES
                       ( value1, value2,...valueN );

• python操作

# 封装了sql语句和插入操作

data = {
    'id': '2012001',
    'name': 'Bob',
    'age':20
    }
table = 'students'
keys = ','.join(data.keys())
values = ', '.join(['%s'] * len(data))
sql = 'INSERT INTO {table}({keys}) VALUES ({values})'.format(table=table, keys=keys, values=values)
try:
   if cursor.execute(sql, tuple(data.values())):
       print('Successful')
       db.commit()
except:
    print('Failed')
    db.rollback()
db.close()

更新数据

返回目标

• sql语法

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]

• python操作

# 一种去重的方法，如果数据存在，则更新数据；如果数据不存在，则插入数据

data = {
    'id': '20120001',
    'name': 'Bob',
    'age': 21
}
 
table = 'students'
keys = ', '.join(data.keys())
values = ', '.join(['%s'] * len(data))
 
sql = 'INSERT INTO {table}({keys}) VALUES ({values}) ON DUPLICATE KEY UPDATE'.format(table=table, keys=keys, values=values)
update = ','.join([" {key} = %s".format(key=key) for key in data])
sql += update
try:
    if cursor.execute(sql, tuple(data.values())*2):
        print('Successful')
        db.commit()
except:
    print('Failed')
    db.rollback()
db.close()

ON DUPLICATE KEY UPDATE:意思是如果主键已经存在，就执行更新操作

完整的SQL构造出来是:INSERT INTO students(id, name, age) VALUES (%s, %s, %s) ON DUPLICATE KEY UPDATE id = %s, name = %s, age = %s所以execute()方法的第二个参数元组就需要乘以2变成原来的2倍

删除数据

返回目录

• sql语法

DELETE FROM table_name [WHERE Clause]

• python操作

# 将条件当作字符串来传递，以实现删除操作

table = 'students'
condition = 'age > 20'

sql = 'DELETE FROM  {table} WHERE {condition}'.format(table=table, condition=condition)
try:
    cursor.execute(sql)
    db.commit()
except:
    db.rollback()

db.close()

查询操作

返回目录

• sql语法

SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[LIMIT N][ OFFSET M]

• python操作

cursor.execute(sql)
print('Count:', cursor.rowcount)  # rowcount属性获取查询结果的条数
one = cursor.fetchone()  # fetchone()方法，这个方法可以获取结果的第一条数据，返回结果是元组形式
print('One:', one)
results = cursor.fetchall()  # fetchall()方法返回的是偏移指针指向的数据一直到结束的所有数据