Python数据库操作,针对pymysql 和 MYSQL数据库

2018-09-05 本文已影响0人 FlyingLittlePG

此文将以MYSQL数据库做为例子,pymysql库作为驱动进行学习

安装MYSQL数据库与pymysql第三方库

安装MYSQL数据库,请点击https://dev.mysql.com/downloads/installer/安装MYSQL的社区版本
安装pymysql库不多做叙述
安装navicat for mysql,此程序用来管理MYSQL数据库
- 注意: 连接过程中可能会出现1251错误
- 解决办法,在cmd命令下登录mysql后输入:
  - ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'mysql的密码'; 注意:引号中的为你自己设置的sql数据库的密码
  - FLUSH PRIVILEGES;

pymysql 连接mysql数据库的驱动库

Connection（host = None，user = None，password =''，database = None，port = 0，unix_socket = None，charset =''，sql_mode = None，read_default_file = None，conv = None，use_unicode = None，client_flag = 0，cursorclass = <class'pymysql.cursors.Cursor'>，init_command = None，connect_timeout = 10，ssl = None，read_default_group = None，compress = None，named_pipe = None，自动提交=假，分贝=无，passwd的=无，local_infile =假，max_allowed_packet个= 16777216，defer_connect =假，auth_plugin_map =无，read_timeout =无，write_timeout =无，bind_address =无，binary_prefix =假，程序名=无，server_public_key =无）类
- read_timeout - 以秒为单位读取连接的超时（默认值：无 - 无超时）
- write_timeout - 以秒为单位写入连接的超时（默认值：无 - 无超时）
- charset - 你要使用的Charset。
- sql_mode - 要使用的默认SQL_MODE。
- read_default_file - 指定my.cnf文件以从[client]部分下读取这些参数。
- conv - 使用转换字典而不是默认字典。这用于提供类型的自定义编组和解组。见转换器。
- use_unicode - 是否默认为unicode字符串。对于Py3k，此选项默认为true。
- client_flag - 要发送给MySQL的自定义标志。在constants.CLIENT中查找潜在值。
- cursorclass - 要使用的自定义游标类。
- init_command - 建立连接时要运行的初始SQL语句。
- connect_timeout - 连接时抛出异常之前的超时。（默认值：10，最小值：1，最大值：31536000）
- ssl - 类似于mysql_ssl_set（）参数的参数的dict。目前，不支持capath和cipher参数。
- read_default_group - 要在配置文件中读取的组。
- compress - 不支持
- named_pipe - 不支持
- autocommit - 自动提交模式。无表示使用服务器默认值。（默认值：False）
- local_infile - 允许使用LOAD DATA LOCAL命令的布尔值。（默认值：False）
- max_allowed_packet - 发送到服务器的最大数据包大小（以字节为单位）。（默认值：16MB）仅用于限制小于默认值（16KB）的“LOAD LOCAL INFILE”数据包的大小。
- defer_connect - 不要明确连接contruction - 等待连接调用。（默认值：False）
- auth_plugin_map - 插件名称的一个字典，用于处理该插件的类。该类将Connection对象作为构造函数的参数。该类需要一个认证方法，将认证包作为参数。对于对话框插件，可以使用提示（echo，prompt）方法（如果没有authenticate方法）从用户返回字符串。（实验）
- server_public_key - SHA256 authenticnticaiton插件公钥值。（默认：无）
- db - 数据库的别名。（与MySQLdb兼容）
- passwd - 密码的别名。（与MySQLdb兼容）
- binary_prefix - 在字节和bytearray上添加_binary前缀。（默认值：False）

常用方法
- select_db(db) 设置db为当前数据库
- cursor（） 创建一个游标对象,继而对数据进行操作
  - 游标对象方法:
  - execute（query，args = None ） 执行sql语句
  - executemany（query，args ） 运行多个sql语句
  - fetchall（） 获取所有行
  - fetchmany（size = None ） 获取指定行数
  - fetchone（） 获取下一行
  - close（） 关闭游标,将缓存的数据全部释放到数据库中
- commit（） 提交对数据库中数据的修改,使之持久化到数据库中
- close（） 关闭套接字连接退出

SQL语句

SQL 是一门 ANSI 的标准计算机语言，用来访问和操作数据库系统
MySQL 数据类型
- Text 类型
image
- Number 类型
image
- Date 类型
image

SQL的语句分为两大类,一类是DML,另一类是DDL

数据定义语言 (DDL) 用于创建或删除表格

CREATE DATABASE 创建新数据库

语句: CREATE DATABASE database_name

import pymysql

con = pymysql.connect('localhost','root','123456')

sql = '''CREATE DATABASE test
'''
with con.cursor()as cursor:
    cursor.execute(sql)

con.close()

CREATE TABLE 创建新表
- 语句为以下形式,其中约束可以没有
```
CREATE TABLE 表名称
(
列名称1 数据类型 约束,
列名称2 数据类型 约束,
列名称3 数据类型 约束,
....
) 
```
- 约束条件
  - NOT NULL 值不能为空
  - UNIQUE 设置索引,一个表中可以有多个索引
    - 在创建表时,末尾加上该语句
    UNIQUE(列名称)
    - 表创建以后添加索引
```
ALTER TABLE 表名称
ADD UNIQUE (列名称) 
```
    - 撤销索引
```
ALTER TABLE 表名称
DROP INDEX 列名称 
```
  - PRIMARY KEY 设置主键,一个表中只能有一个主键
    - 在创建表时,末尾加上该语句
    PRIMARY KEY (列名称)
    - 表创建后添加主键
```
ALTER TABLE表名称
ADD PRIMARY KEY (列名称) 
```
    - 撤销表中的主键
```
ALTER TABLE 表名称
DROP PRIMARY KEY 
```
  - FOREIGN KEY 一个表中的 FOREIGN KEY 指向另一个表中的 PRIMARY KEY
    - 在创建表时,末尾加上该语句
    FOREIGN KEY (列名称) REFERENCES 表名称(列名称)
    - 表创建后添加FOREIGN KEY
```
ALTER TABLE 表名称1
ADD FOREIGN KEY (列名称)
REFERENCES 表名称2(列名称)
```
    - 删除表中的PRIMARY KEY
```
ALTER TABLE 表名称
DROP FOREIGN KEY 约束名称
```
  - CHECK 限制列中的值的范围
    - 在创建表时,末尾加上该语句
    CHECK (列名称范围限制)
    - 表创建后添加限制范围
```
ALTER TABLE 表名称
ADD CHECK (列名称 范围限制)
```
    - 撤销范围限制
```
ALTER TABLE 表名称
DROP CONSTRAINT 约束名称
```
  - DEFAULT 向列中插入默认值
    - 在创建表时,向数据后添加默认值
    列名称数据类型 DEFALUT 默认值
  - 表创建后添加默认值
```
ALTER TABLE 表名称
ALTER 列名称 SET DEFAULT 默认值 
```
  - 删除默认值
```
ALTER TABLE 表名称
ALTER 列名称 DROP DEFAULT 
```
- AUTO_INCREMENT 新记录插入表中时生成一个唯一的数字
  - 在创建表时,在后面添加该字段即可
  列名称 int NOT NULL AUTO_INCREMENT
  - 默认以1开始递增,但也可以通过赋值来改变初始值
  ALTER TABLE 表名称 AUTO_INCREMENT=100
  - 创建新数据是不必向该数据进行赋值,但应加上NULL,代替该数据,会自动进行赋值

# 新建一个名为person的表,定义有id,name,age,address 其中id设置为主键,并采取自增形式

import pymysql

con = pymysql.connect('localhost','root','123456')

con.select_db('test')

sql = '''CREATE TABLE person
(
id INT(255) NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255),
age INT(100),
address VARCHAR(255)
)
'''
with con.cursor()as cursor:
    cursor.execute(sql)

con.close()

CREATE INDEX 创建表中的引索
- 用于加速搜索数据,用户无法看见,只需设置常用的列
- 使用以下语句
```
CREATE INDEX 引索名称
ON 表名称 (列名称)
```
DROP 删除索引、表和数据库
- 删除表
DROP TABLE 表名称
- 删除数据库
DROP DATABASE 数据库名称
- 删除表中数据,但保留表
TRUNCATE TABLE 表名称

ALTER TABLE 在已有的表中添加、修改或删除列

向表中添加列

ALTER TABLE 表名称
ADD 列名称 列类型

向表中删除列

ALTER TABLE 表名称 
DROP COLUMN 列名称

更改表中某一列的数据类型

ALTER TABLE 表名称
ALTER COLUMN 列名称 列类型

数据操作语言 (DML) 包含用于更新、插入和删除记录的语法

SELECT 选取数据

SELECT 列名称 FROM 表名称
SELECT DISTINCT 去掉重复数据

SELECT DISTINCT 列名称 FROM 表名称
WHERE 有条件的选取数据

SELECT 列名称 FROM 表名称 WHERE 列运算符值
- 运算符
```
=    等于
<>  不等于
>    大于
<    小于
>=  大于等于
<=  小于等于
```
- 操作符
  - IN 允许在 WHERE 子句中规定多个值,返回包含这些值的集合
```
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...)
```
  - BETWEEN 选取介于两个值之间的数据范围左包右闭
```
SELECT column_name(s)
FROM table_name
WHERE column_name
BETWEEN value1 AND value2
```
- AND 和 OR 对一个以上的条件对记录进行过滤
SELECT * FROM 表名称 WHERE 列名称运算符值 AND 列名称运算符值

总结: 可以将AND OR WHERE 组合起来使用,用于查找精确的数据
- LIKE 于在 WHERE 子句中搜索列中的指定模式,类似于正则表达式
```
SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern
```
  - 通配符
image
- ORDER BY 语句用于对结果集进行排序
  - 默认顺序排序,字母以ABC形式,数字从大到小
  SELECT 列名称 FROM 表名称 ORDER BY 列名称
  - 添加DESC可变为逆序排序
  SELECT 列名称 FROM 表名称 ORDER BY 列名称 DESC
- LIMIT 规定返回数据集合的最大值
```
"SELECT column_name(s)
FROM table_name
LIMIT number"
```
INTO 从一个表中选取输出并复制到另一个表中,用于备份数据库
- 备份到另一个数据库中
```
SELECT *
INTO new_table_name IN externaldatabase
FROM old_tablename  
```
- 备份到另一个表中
```
"SELECT *
INTO Persons_backup
FROM Persons   
```
INSERT INTO 项数据库添加新行
- 向一行中添加数据
INSERT INTO 表名称 VALUES (值1, 值2,....) 添加所有值
- 向一行中选取的列中添加数据
INSERT INTO 表名称 (列1, 列2,...) VALUES (值1, 值2,....)
UPDATE 修改表中的数据
- 修改某列数据为某值的数据为新值
UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
- 向某行中的多列修改数据
UPDATE 表名称 SET 列名称1 = 新值1,列名称2 = 新值2,…. WHERE 列名称 = 某值
DELETE 删除表中的行
- 删除列名称为某值的所有行
DELETE FROM 表名称 WHERE 列名称 = 值
- 删除所有行,即清空所有数据
DELETE * FROM table_name

向数据库的person表中写入一行数据

import pymysql

con = pymysql.connect('localhost','root','123456')

con.select_db('test')

sql = ''' INSERT INTO person VALUES(NULL,'jack',18,'Beijing')
'''
with con.cursor()as cursor:
    cursor.execute(sql)

con.commit()
con.close()

写入多行数据

import pymysql

con = pymysql.connect('localhost','root','123456')

con.select_db('test')

list = [(None,'jack',18,'beijing'),(None,'bob',20,'shanghai'),(None,'alice',10,'beijing'),(None,'luna',18,'shengzheng')]

with con.cursor()as cursor:
    cursor.executemany('INSERT INTO person VALUES(%s,%s,%s,%s)',list)

con.commit()

con.close()

查找所有数据

import pymysql

con = pymysql.connect('localhost','root','123456')

con.select_db('test')

sql = 'SELECT * FROM person'

with con.cursor()as cursor:
    cursor.execute(sql)
    data = cursor.fetchall()
    for i in data:
        print(i)

con.close()

查找所有地址为beijing的数据

import pymysql

con = pymysql.connect('localhost','root','123456')

con.select_db('test')

sql = "SELECT * FROM person where address = 'beijing'"

with con.cursor()as cursor:
    cursor.execute(sql)
    data = cursor.fetchall()
    for i in data:
        print(i)

con.close()

查找所有age大于10的数据

import pymysql

con = pymysql.connect('localhost','root','123456')

con.select_db('test')

sql = "SELECT * FROM person where age > 10"

with con.cursor()as cursor:
    cursor.execute(sql)
    data = cursor.fetchall()
    for i in data:
        print(i)

con.close()

查找age大于10并且address等于bejing的数据

import pymysql

con = pymysql.connect('localhost','root','123456')

con.select_db('test')

sql = "SELECT * FROM person where age > 10 AND address = 'beijing'"

with con.cursor()as cursor:
    cursor.execute(sql)
    data = cursor.fetchall()
    for i in data:
        print(i)

con.close()

将所有数据按年龄大小进行逆序排序

import pymysql

con = pymysql.connect('localhost','root','123456')

con.select_db('test')

sql = "SELECT * FROM person ORDER BY age DESC"

with con.cursor()as cursor:
    cursor.execute(sql)
    data = cursor.fetchall()
    for i in data:
        print(i)

con.close()

修改表中jack的age为16

import pymysql

con = pymysql.connect('localhost','root','123456')

con.select_db('test')

sql = "UPDATE person set age = 16 WHERE name = 'jack'"

with con.cursor()as cursor:
    cursor.execute(sql)

con.commit()

con.close()

删除数据库中jack的数据

import pymysql

con = pymysql.connect('localhost','root','123456')

con.select_db('test')

sql = "DELETE FROM person WHERE name = 'jack'"

with con.cursor()as cursor:
    cursor.execute(sql)

con.commit()

con.close()

删除表中所有数据

import pymysql

con = pymysql.connect('localhost','root','123456')

con.select_db('test')

sql = "DELETE FROM person"

with con.cursor()as cursor:
    cursor.execute(sql)

con.commit()

con.close()

向表中添加sex列,类型为varchar

import pymysql

con = pymysql.connect('localhost','root','123456')

con.select_db('test')

sql = '''ALTER TABLE person
ADD sex VARCHAR(255)
'''

with con.cursor()as cursor:
    cursor.execute(sql)

con.commit()

con.close()

删除表

import pymysql

con = pymysql.connect('localhost','root','123456')

con.select_db('test')

sql = '''DROP TABLE person
'''

with con.cursor()as cursor:
    cursor.execute(sql)

con.commit()

con.close()

删除数据库

import pymysql

con = pymysql.connect('localhost','root','123456')

sql = '''DROP DATABASE test
'''

with con.cursor()as cursor:
    cursor.execute(sql)

con.commit()

con.close()

Python数据库操作,针对pymysql 和 MYSQL数据库

安装MYSQL数据库与pymysql第三方库

pymysql 连接mysql数据库的驱动库

SQL语句

SQL的语句分为两大类,一类是DML,另一类是DDL

数据定义语言 (DDL) 用于创建或删除表格

数据操作语言 (DML) 包含用于更新、插入和删除记录的语法

向数据库的person表中写入一行数据

写入多行数据

查找所有数据

查找所有地址为beijing的数据

查找所有age大于10的数据

查找age大于10并且address等于bejing的数据

将所有数据按年龄大小进行逆序排序

修改表中jack的age为16

删除数据库中jack的数据

删除表中所有数据

向表中添加sex列,类型为varchar

删除表

删除数据库

猜你喜欢

热点阅读