文章目录
pymysql使用
1.下载pymysql
2.导入pymysql模块
3.创建链接及小例
4.获取最新数据的id
5.try except使用
6.取数据语句：fetch
7.使用.format拼接数据库语句的坑
7.1 传统拼接，不会出错
7.2 format拼接

pymysql使用
1.下载pymysql
1.pip3 install pymysql

2.pycharm–>file–>settings–>Project Interpreter,将pymysql加进去
2.导入pymysql模块
import pymysql
3.创建链接及小例
#1.打开数据库连接
conn=pymysql.connect(host="127.0.0.1",port=3306,user=user,password=pwd,database="seu",charset="utf8")

host:数据库主机地址

post：mysql默认端口3306

user、password：mysql用户

database：打开的数据库名字

charset：以utf-8形式打开，注意写utf8

#创建游标，相当于cmd的光标

#2.使用cursor（）创建一个游标对象cursor
cursor=conn.cursor()

#3.使用execute（）执行一条mysql语句，并返回受影响的行数
effect_row=cursor.execute("select * from student")
print(effect_row)

effect_row打印出来是6，因为student此时一共有6条数据，都被查询；
# 使用 execute() 方法执行 SQL，如果表存在则删除
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")
 
# 使用预处理语句创建表
sql = """CREATE TABLE EMPLOYEE (
         FIRST_NAME  CHAR(20) NOT NULL,
         LAST_NAME  CHAR(20),
         AGE INT,  
         SEX CHAR(1),
         INCOME FLOAT )"""
 
cursor.execute(sql)

以上是execute（）。
还可以使用executemany（）执行多条语句并执行，并返回受影响行数
effect_row = cursor.executemany("insert into hosts(host,color_id)values(%s,%s)", [("1.1.1.11",1),("1.1.1.11",2)])

列表里数组的每个元素都必须是元组，并且python对MySQL进行数据的插入、更新和删除之后需要commit，数据库才会真的有数据操作，因此上方插入完数据后需要加
conn.commit()

#4.执行完mysql语句操作后先关闭游标，再关闭连接

关闭游标：
cursor.close()

关闭连接：
conn.close()

4.获取最新数据的id
import pymysql
conn=pymysql.connect(host=host,user=username.password=pwd,database=databaseName,charset="utf8")
cursor=conn.cursor()
cursor.execute("insert into teacher (name,age,gender) values (%s,%s,%s)" ,("Mr.Chong", 40, "m"))
conn.commit()
last_id=cursor.lastrowid
print("最后一条数据的ID是:", last_id)
cursor.close()
conn.close()

5.try except使用
import pymysql
 
# 打开数据库连接
conn = pymysql.connect("localhost","testuser","test123","TESTDB" )
 
# 使用cursor()方法获取操作游标 
cursor = conn.cursor()
 
# SQL 插入语句
sql = "INSERT INTO EMPLOYEE(FIRST_NAME, \
       LAST_NAME, AGE, SEX, INCOME) \
       VALUES ('%s', '%s',  %s,  '%s',  %s)" % \
       ('Mac', 'Mohan', 20, 'M', 2000)
try:
   # 执行sql语句
   cursor.execute(sql)
   # 执行sql语句
   conn.commit()
except:
   # 发生错误时回滚
   conn.rollback()
 
# 可以不关闭游标，直接关闭数据库连接
conn.close()

6.取数据语句：fetch
1.fetchone()、fetchmany()、fetchall()
import pymysql
  
conn = pymysql.connect(host='127.0.0.1', port=3306, user=user, passwd=pwd, db='teacher')
cursor = conn.cursor()
cursor.execute("select * from teacher")
  
# 获取第一行数据,返回元组：(1, 'Mr.Weng', 'woman', 38)
row_1 = cursor.fetchone()
  
# 获取前n行数据，返回元组：((2, 'Mr.Zhou', 'man', 38), (3, 'Mr.Ni', 'm', 37), (4, 'Mr.Chong', 'm', 40))
# row_2 = cursor.fetchmany(3)
# 获取所有数据，返回元组：((2, 'Mr.Zhou', 'man', 38), (3, 'Mr.Ni', 'm', 37), (4, 'Mr.Chong', 'm', 40))
# row_3 = cursor.fetchall()
  
conn.commit()
cursor.close()
conn.close()

2.try 和 fetchall
import pymysql
 
# 打开数据库连接
db = pymysql.connect("localhost","testuser","test123","TESTDB" )
 
# 使用cursor()方法获取操作游标 
cursor = db.cursor()
 
# SQL 查询语句
sql = "SELECT * FROM EMPLOYEE \   # \ 的作用是编译器里代码太长另起一行，但下一行还和此行是一句.
       WHERE INCOME > %s" % (1000)
try:
   # 执行SQL语句
   cursor.execute(sql)
   # 获取所有记录列表
   results = cursor.fetchall()
   for row in results:
      fname = row[0]
      lname = row[1]
      age = row[2]
      sex = row[3]
      income = row[4]
       # 打印结果
      print ("fname=%s,lname=%s,age=%s,sex=%s,income=%s" % (fname, lname, age, sex, income ))
except:
   print ("Error: unable to fetch data")
 
# 关闭数据库连接
db.close()

以上脚本执行结果如下：
fname=Mac, lname=Mohan, age=20, sex=M, income=2000
7.使用.format拼接数据库语句的坑
7.1 传统拼接，不会出错
cursor.execute("SELECT company_id FROM company_index_tables where company_name = %s",(name))

7.2 format拼接
使用format进行数据库语句拼接时，它会自动把我们值外面的引号去掉，因此导致插入失败。也就是说如果值是字符串，在拼接到数据库语句的过程中，因为引号被去掉，所以失败(当然也可以进行一定修改使其成功)；但如果值是数字，那么拼接不会有丝毫影响。
数字拼接(此处start是数字):
sql="SELECT * FROM company_index_tables limit {0},10".format(start)
cursor.execute(sql)

字符串拼接(name是字符串)，失败：
sql = "SELECT company_id FROM company_index_tables where company_name = {0}".format(name)
cursor.execute(sql)

想要成功，就自己手动加引号(成功)：
sql = "SELECT company_id FROM company_index_tables where company_name = '{0}'".format(name)
cursor.execute(sql)

准备工作
使用python程序操作MySQL数据库

安装pymysql第三方包: sudo pip3 install pymysql

安装第三方包pip3 install

卸载第三方包pip3 uninstall

pip3 list 查看使用pip命令安装的第三方包列表
①导入pymysql包: import pymysql
②创建连接对象:
调用pymysql模块中的connect()函数来创建连接对象,代码如下:

conn = connect(参数列表)

*参数host: 连接mysql主机,如果是本机是’localhost’

*参数port: 连接的mysql主机的端口,默认是3306

*参数user: 连接的用户名

*参数password: 连接的密码

*参数database: 数据库的名称

*参数charset: 通信采用的编码方式,推荐使用utf8
连接对象操作说明:

关闭连接:conn.close()

提交数据:conn.commit()

撤销数据:conn.rollback()
③获取游标对象
获取游标对象的目标就是要执行sql语句,完成对数据库的增删改查操作

调用连接对象cursor()方法获取游标对象

cur = conn.cursor()
游标操作说明:
使用游标执行SQL语句: execute(operation [parameters ]) 执行SQL语句，返回受影响的行数，主要用于执行insert、update、delete、select等语句

获取查询结果集中的一条数据:cur.fetchone()返回一个元组, 如 (1,‘张三’)

获取查询结果集中的所有数据: cur.fetchall()返回一个元组,如((1,‘张三’),(2,‘李四’))

关闭游标: cur.close(),表示和数据库操作完成
---------pymysql完成数据的查询操作--------------
import pymysql
# 创建连接对象
conn = pymysql.connect(host='localhost', port=3306, user='root', password='mysql',database='python', charset='utf8')

# 获取游标对象
cursor = conn.cursor()

# 查询 SQL 语句
sql = "select * from students;"
# 执行 SQL 语句 返回值就是 SQL 语句在执行过程中影响的行数
row_count = cursor.execute(sql)
print("SQL 语句执行影响的行数%d" % row_count)

# 取出结果集中一行数据,　例如:(1, '张三')
# print(cursor.fetchone())

# 取出结果集中的所有数据, 例如:((1, '张三'), (2, '李四'), (3, '王五'))
for line in cursor.fetchall():
    print(line)

# 关闭游标
cursor.close()

# 关闭连接
conn.close()

***************注意点:port=3306 不能用引号  否则报错

***************报错内容:TypeError: %d format: a number is required, not str
------------------pymysql增删改-----------------
import pymysql

# 创建实例对象
conn = pymysql.connect(host='localhost',port=3306,user='root',password='mysql',database='jingdong',charset='utf8')

# 创建游标对象
cursor = conn.cursor()

try:
    # 添加sql语句
    # sql = "insert into student(name) values ('刘璐'),('王美丽');"
    # 删除sql语句
    # sql = "delete from student where id = 5;"
    # 修改sql语句
    sql = "update student set name = '王八' where id = 6;"
    # 执行sql语句
    row_count = cursor.execute(sql)
    print("sql 语句执行影响的行数%d" % row_count)
    # 提交数据到数据
    conn.commit()
except Exception as e:
    conn.rollback()


# 关闭游标
cursor.close()

# 关闭连接
conn.close()

*********与查的区别是:成功需要commit()提交  失败需要rollback()撤销
-----------防止SQL注入--------
SQL注入: 用户提交带有恶意的数据与SQL语句进行字符串方式拼接,

从而影响了SQL语句的语言,最终产生数据泄露现象
如何防止SQL注入:

SQL语言中的参数使用%s来占位，此处不是python中的字符串格式化操作

将SQL语句中%s占位所需要的参数存在一个列表中，把参数列表传递给execute方法中第二个参数
防止SQL注入的示例代码:

from pymysql import connect

def main():

    find_name = input("请输入物品名称：")

    # 创建Connection连接
    conn = connect(host='localhost',port=3306,user='root',password='mysql',database='jing_dong',charset='utf8')
    # 获得Cursor对象
    cs1 = conn.cursor()

    # 非安全的方式
    # 输入 ' or 1 = 1 or '   (单引号也要输入)
    # sql = "select * from goods where name='%s'" % find_name
    # print("""sql===>%s<====""" % sql)
    # # 执行select语句，并返回受影响的行数：查询所有数据
    # count = cs1.execute(sql)

    # 安全的方式
    # 构造参数列表
    params = [find_name]
    # 执行select语句，并返回受影响的行数：查询所有数据
    count = cs1.execute("select * from goods where name=%s", params)
    # 注意：
    # 如果要是有多个参数，需要进行参数化
    # 那么params = [数值1, 数值2....]，此时sql语句中有多个%s即可
    # %s 不需要带引号

    # 打印受影响的行数
    print(count)
    # 获取查询的结果
    # result = cs1.fetchone()
    result = cs1.fetchall()
    # 打印查询的结果
    print(result)
    # 关闭Cursor对象
    cs1.close()
    # 关闭Connection对象
    conn.close()

if __name__ == '__main__':
    main()

说明:
execute方法中的 %s 占位不需要带引号
小结:
导包: import pymysql

创建连接对象: pymysql.connect(参数列表)

获取游标对象: cursor =conn.cursor()

执行SQL语句: row_count = cursor.execute(sql)

获取查询结果集: result = cursor.fetchall()

将修改操作提交到数据库: conn.commit()

回滚数据: conn.rollback()

关闭游标: cursor.close()

关闭连接: conn.close()

PyMySQL 安装：
在python目录下的Programs文件路径下执行下面的命令
 pip install PyMySQL
pymysql创建表:
import pymysql  #导入pymysql模块
def createtable():
    #1 建立数据库
    conn=pymysql.connect('127.0.0.1','root','123456','student',charset='utf8')
   #2 获得cursor对象
    mycursor=conn.cursor()
    #执行sql语句
    sqlstr='''
        create table test1(
        id int PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR(20) NOT NULL,
        sex CHAR(4)
        )
    '''
    mystr=mycursor.execute(sqlstr)
    #  获取执行结果
    result=mycursor.fetchone()
    #关闭
    conn.close()
createtable()
公共的连接方法：
def getconn():
    conn = pymysql.connect("127.0.0.1", "root", "123456", "student",charset='utf8')
    mycursor=conn.cursor()
    return [conn,mycursor]
插入数据：
def inserttable():
    conn=pymysql.connect("localhost","root","123456","house",charset='utf8')
    mycursor=conn.cursor()
    sqlstr='''insert into `hos_district`(did,dName) values('11','朝阳')'''.encode("utf-8")
    try:
        result=mycursor.execute(sqlstr)
        print(result)
        conn.commit()#提交到数据库执行
    except:
        conn.rollback()#如果发生错误则回滚
inserttable()
查询数据：
def querytable():
    conn=getconn()
    sqlstr="select studentNo,studentName,sex,phone from student"
    conn[1].execute(sqlstr)
    rs=conn[1].fetchall()
    for row in rs:
        print("studentNo:{0},studentName:{1},sex:{2},phone:{3}".format(row[0],row[1],row[2],row[3]))
    conn[0].close()
querytable()
删除数据：
def deltable():
    conn=getconn()
    sqlstr='''delete from test1 where id=1'''
    try:
        print(conn[1].execute(sqlstr))
        conn[0].commit()
    except:
        conn[0].rollback()
    conn[0].close()
deltable()
简洁的方法
def getconn():
    conn = pymysql.connect("localhost", "root", "123456", "school")
    mycursor=conn.cursor()
    return [conn,mycursor]
def operationtable(sqlstr):
    conn = getconn()
    try:
        print(conn[1].execute(sqlstr))
        conn[0].commit()
    except:
        conn[0].rollback()
    conn[0].close()
删除
sqlstr = '''delete from test1 where id=1'''
更新
sqlstr='''update test1 set name='李四' where id=2'''
增
sqlstr='''insert into test1(name,sex) VALUES ('张三','男')'''
operationtable(sqlstr)

安装PyMySQL
PyMySQL是一个Python编写的MySQL驱动程序，让我们可以用Python语言操作MySQL数据库。
首先，使用pip安装PyMySQL。
pip install PyMySQL
1
使用PyMySQL
简单使用
如果有JDBC等其他语言的数据库学习经验的话，使用PyMySQL非常简单。下面是一个完整的MySQL增删查（没有改）的例子。
import pymysql
import datetime

host = 'localhost'
username = 'root'
password = '12345678'
db_name = 'test'

create_table_sql = """\
CREATE TABLE fuck(
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(255) UNIQUE ,
nickname VARCHAR(255) NOT NULL ,
birthday DATE
)
"""

insert_table_sql = """\
INSERT INTO fuck(username,nickname,birthday)
 VALUES('{username}','{nickname}','{birthday}')
"""

query_table_sql = """\
SELECT id,username,nickname,birthday
FROM fuck 
"""

delete_table_sql = """\
DELETE FROM fuck 
"""

drop_table_sql = """\
DROP TABLE fuck
"""

connection = pymysql.connect(host=host,
                             user=username,
                             password=password,
                             charset='utf8mb4',
                             db=db_name)

try:
    with connection.cursor() as cursor:
        print('--------------新建表--------------')
        cursor.execute(create_table_sql)
        connection.commit()

        print('--------------插入数据--------------')
        cursor.execute(
            insert_table_sql.format(username='yitian', nickname='易天', birthday=datetime.date.today()))
        cursor.execute(
            insert_table_sql.format(username='zhang3', nickname='张三', birthday=datetime.date.today()))
        cursor.execute(
            insert_table_sql.format(username='li4', nickname='李四', birthday=datetime.date.today()))
        cursor.execute(
            insert_table_sql.format(username='wang5', nickname='王五', birthday=datetime.date.today()))
        connection.commit()

        print('--------------查询数据--------------')
        cursor.execute(query_table_sql)
        results = cursor.fetchall()
        print(f'id\tname\tnickname\tbirthday')
        for row in results:
            print(row[0], row[1], row[2], row[3], sep='\t')

        print('--------------清除数据--------------')
        cursor.execute(delete_table_sql)
        connection.commit()

        print('--------------删除表--------------')
        cursor.execute(drop_table_sql)
        connection.commit()

finally:
    connection.close()
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
如果需要更详细的资料，请查阅pymysql文档或者其他资料。
防止SQL注入
在上面的例子中直接拼接字符串，这不是好办法，因为可能存在SQL注入攻击，更好的解决办法是使用类库提供的函数来传参。所以上面的代码也需要稍作修改。
首先，将带参数的SQL语句改写。
insert_table_sql = """\
INSERT INTO fuck(username,nickname,birthday)
 VALUES(%s,%s,%s)
"""
1
2
3
4
然后将相应的执行代码也进行修改，execute函数接受一个元组作为SQL参数。所以代码改写为这样。
print('--------------插入数据--------------')
cursor.execute(insert_table_sql, ('yitian', '易天', datetime.date.today()))
cursor.execute(insert_table_sql, ('zhang3', '张三', datetime.date.today()))
cursor.execute(insert_table_sql, ('li4', '李四', datetime.date.today()))
cursor.execute(insert_table_sql, ('wang5', '王五', datetime.date.today()))
connection.commit()
1
2
3
4
5
6
这样，SQL操作就更安全了。如果需要更详细的文档参考PyMySQL文档吧。不过好像这些SQL数据库的实现还不太一样，PyMySQL的参数占位符使用%s这样的C格式化符，而Python自带的sqlite3模块的占位符好像是?。因此在使用其他数据库的时候还是仔细阅读文档吧。