笔记-python操作mysql

笔记-python操作mysql

 

 

1.      开始

1.1.    环境准备-mysql

create database db_python;

use db_python;

 

create table `t2`(

`id` int unsigned auto_increment,

`name` varchar(30),

primary key(`id`));

 

#创建用户并授权

create user 'dev_python' identified by '123456';

grant all on db_python.t2 to dev_python@'%';

 

 

alter table t2 add password varchar(30);

alter table t2 add content varchar(500);

 

1.2.    环境-python

使用pymysql库，PyMySQL是在 Python3.x 版本中用于连接 MySQL 服务器的一个库，Python2中是使用mysqldb。

pip install pymysql

 

2.      API

 

2.1.    connect

连接类，有一些参数可以调整，列出最常用的部分：

# connect to database, parameter: address, user name, password, database name,charset
db = pymysql.connect('localhost','dev_python','123456','db_python',cursorclass=pymysql.cursors.DictCursor)

 

2.2.    cursor

执行操作的接口，有

execute(query, args=None)

Execute a query

Parameters:	query (str) – Query to execute. args (tuple, list or dict) – parameters used with query. (optional)
Returns:	Number of affected rows
Return type:	int

If args is a list or tuple, %s can be used as a placeholder in the query. If args is a dict, %(name)s can be used as a placeholder in the query.

 

fetchall()

Fetch all the rows

fetchmany(size=None)

Fetch several rows

fetchone()

Fetch the next row

 

classpymysql.cursors.DictCursor(connection)

A cursor which returns results as a dictionary

返回的是一个字典，使用时需要注意返回的字典的健值。

 

3.      使用

import pymysql

# connect to database, parameter: address, user name, password, database name,charset
db = pymysql.connect('localhost','dev_python','123456','db_python',cursorclass=pymysql.cursors.DictCursor)

'''
# 得到一个可以执行sql语句的cursor object
# 注意cursor支持上下文，
# with db.cursor() as cur:
cursor = db.cursor()


#定义要执行的sql语句
#  本处为查看表结构
cmd_text = 'desc t2;'
# 执行
print(cursor.execute(cmd_text))

# 关闭相关对象

cursor.close()
db.close()
'''


# execute statement
#create table
sql_create = r"""create table if not exists `t3` (`id` int)"""
with db.cursor() as cur:
    cur.execute(sql_create)
    print(cur.execute('show tables;'))
    print(cur.fetchall())


#CURD
# 插入
cmd_insert = """insert into t2 values('7','first_name','pass1', 'content1')"""
with db.cursor() as cur:
    pass
    #print(cur.execute(cmd_insert))
db.commit()

# update
sql_update = """update t2 set name=%s where id=%s"""
with db.cursor() as cur:
    cur.execute(sql_update, ['ppp','7'])
    cur.execute('select * from t2')
    #print(cur.fetchall())


# retrieve
sql_select = """select * from t3;"""
with db.cursor() as cur:
    if cur.execute(sql_select):
        print(cur.fetchall())
    else:
        print('get 0 rows.')


# delete
sql_delete = """delete from t2 where id=%s"""
with db.cursor() as cur:
    cur.execute(sql_delete, ['7'])
    cur.execute('select * from t2')
    print(cur.fetchall())

 

上面列出的仅包括最常用的CURD操作，更复杂的功能需要参考相关接口文档。

 

4.      参考文档

https://pypi.org/project/PyMySQL/

https://pymysql.readthedocs.io/en/latest/