2020.10.22 SQLite、MySQL

SQLite

SQLite是一种嵌入式数据库，它的数据库就是一个文件。由于SQLite是用C写成的，而且体积很小，所以，经常被集成到各种应用程序中，甚至在iOS和Android的App中都可以继承。

Python内置了SQLite3，所以，在Python中使用SQLite，不需要安装任何东西，可以直接使用。

在使用SQLite前，需要先搞清楚几个概念：

①要操作关系数据库，首先需要连接到数据库，一个数据库的连接称为Connection；

②连接到数据库后，需要打开游标，称之为Cursor，通过Cursor执行SQL语句，然后获得执行结果。

Python定义了一套操作数据库的API接口，任何数据库要连接到Python，只要提供符合Python标准的数据库驱动即可。

由于SQLite的驱动内置在Python标准库中，所以我们可以直接来操作SQLite数据库。

下面是实践：

#导入SQLite驱动：
import sqlite3
#连接，sqlite3.connect('xxx.db')，可以用with语句块
#如果xxx.db文件不存在，就会自动在当前目录创建
with sqlite3.connect('test.db') as conn:
        #创建游标Cursor：cursor=conn.cursor()
        cursor=conn.cursor()
        #通过Cursor执行SQL语句：cursor.execute(SQL语句)
        #创建表
        cursor.execute('create table user (id varchar(20) primary key,name varchar(20))')
        #插入一条记录
        cursor.execute('insert into user (id , user) values(\'1\' , \'Michael\')')
        #查询记录
        cursor.execute('select * from user where id = ?',('1',))
        #得到查询结果：cursor.fetchall()
        values=cursor.fetchall()
        print(values)

        #关闭游标
         cursor.close()
        #提交事务：
         conn.commit()
        #由于我们用了with语句块，所以会自动进行连接的关闭
        #关闭连接
        #conn.close()

[('1', 'Michael')]

使用Python的DB-API时，只要搞清楚Connection和Cursor对象，打开后记得分别用close关闭。

使用Cursor对象执行insert、update、delete语句时，可以在执行完后通过属性rowcount查询影响的行数。

执行select时，通过fetchall()方法得到结果集。结果集是一个List，每个元素都是一个Tuple，对应一行记录。

如果SQL语句有参数，需要在cursor.execute()的第二个参数处写明参数值，有几个?占位符就必须对应几个参数，例如：

cursor.execute('select * from user where id=? and pwd=?',('abc','password'))

上面的语句是，查询id是abc、pwd是password的行。

SQLite支持常见的标准SQL语句以及几种常见的数据类型。

 

注意：

fetchall()返回的是List，找到的每个关系对应List中的一个Tuple

 

MySQL

#导入MySQL驱动
import mysql.connector

#连接数据库
conn= mysql.connector.connect(
        user='root',
        password='123456',
        database='test',
        auth_plugin='mysql_native_password'
)
#创建游标Cursor
cursor=conn.cursor()
#创建user表
cursor.execute('create table user (id varchar(20) primary key ,name varchar(20))')
#插入一行记录，注意MySQL的占位符与SQLite不同，是%s而不是？
cursor.execute('insert into user (id,name) values(%s,%s)',['1','Michael'])
#处理的行数
print(cursor.rowcount)
#查询
cursor.execute('select * from user where id =%s',('1',))
#查询结果
values=cursor.fetchall()
print(values)
#提交事务、关闭游标Cursor
cursor.close()
conn.commit()
conn.close()


1
[('1', 'Michael')]

 

在此过程中，会遇到

Authentication method 'caching_sha2_password' is not supported

错误，解决方法mysql 报错Authentication method 'caching_sha2_password' is not supported

 

由于Python的DB-API定义都是通用的，所以操作MySQL的代码和SQLite类似。