Python + Mysql 数据操作
持久化存储策略与SQL
在任何应用中,都需要持久化存储,目前一般的存储机制包括:稳健、数据库系统以及一些混合类型。一些复杂且要求高响应的项目离不开结构化数据库,而结构化数据库离不开SQL查询语言。
在数据库存储策略中,一般有这样的结构
![数据库存储策略结构](https://img-blog.csdnimg.cn/20201119024640557.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JhaWR1XzM4NTQ2MTky,size_16,color_FFFFFF,t_70#pic_center)
SQL语言整体结构
- 操作数据库
CREATE DATABASE name_of_new_database #创建一个新的数据库
SHOW DATABASES #展示所有已创建的数据库
USE name_of_new_database #使用新建立的数据库
DROP name_of_new_database #删除新建立的数据库
- 操作表
CREATE TABLE <table name> (id INT(1000) PRIMARY KEY AUTO_INCREMENT);
#build a new table
ALTER TABLE <table name> ADD name VARCHAR(20);
#add new attributes
INSERT INTO <table name> (name) VALUES ('Sheeps Lue');
#insert new records
- 操作数据
SELECT * FROM <table name>
Python与数据库的连接
Python与数据库的协同已经成为了应用的核心组件,所以Python的BD-API是强需求驱动的。从数据库出发,目前一般的策略分为:数据库 > RDBMS客户端 > Python数据库适配器 > Python ORM > Python应用。其中Python ORM是通过处理数据库细节来简化应用的策略。
- 着重介绍一下DB-API
DB-API是介于数据库和Python适配器之间的标准化接口。由于不同的应用可能会定制开发不同的适配器,同时不同的底层数据库在查询需求上也不同,为了统一应用查询的标准,专门为Python适配器开发出访问底层数据库的标准化接口。换句话讲,显示器需要的信号标准不同,输出设备输出标准不同,需要标准化转换器来当皮条客。
DS-API要求具有基本的功能和属性。
apilevel | 接口的版本,需要适配器来兼容这个版本 |
---|---|
-threadsafety- | -本模块的线程安全级别- |
-paramstyle- | -参数输入风格- |
-connect- | -connect()通过connection对象访问数据库- |
在connect()中需要输入属性
以MySQLdb为例,整体策略为:
import MySQLdb #加载DB-API
new_connection=MySQLdb.connect(host='localhost',user='root',password='xxx',db='test',port=3306) #建立数据库连接对象,注意不是database而是db,port 参数需要输入一个integer对象而不是一个varchar对象
new_connection.query('CREATE DATABASE test') #创建连接对象后可以直接进行数据库级别的查询和操作
new_connection.commit() #有些数据库适配器可以自动commit,但是有些不行
new_cursor=MySQLdb.cursor() #如果要针对数据库进行操作和查询,需要建立数据库指针对象
new_cursor.execute("SQL")
result=new_cursor.fetchall() #new_cursor.fetchall会返回一个包含数据库查询数据的元组
new_connection.commit() #先提交本次连接请求
new_cursor.close()
new_connection.close()
或者按照OOP
的原则来构建(以上传Titanic.csv为例):
class Data_base(connection_key):
"""the init connection object that require connection key object"""
def __init__(self,content=None):
super(Data_base,self).__init__()
self.content=content
def link2database(self,tem_SQL=None):
with mysql.connect(self.host, self.user, self.password, self.db, self.port) as temp_connection:
tem_cursor=temp_connection.cursor()
tem_cursor.execute(tem_SQL)
temp_connection.commit()#!!!!!!
def upload_attribute(self,table_name=None):
attribute=self.content
self.link2database(tem_SQL='CREATE TABLE %s(id INT PRIMARY KEY AUTO_INCREMENT);'%(table_name))
for atr in attribute:
self.link2database(tem_SQL='ALTER TABLE %s ADD %s VARCHAR(20);'%(table_name,atr))
print("Table %s has been created"%(table_name))
def upload_titanic_value(self,table_name=None):
self.link2database(tem_SQL='INSERT INTO %s (Survived,PC,Sex,Age,SS) VALUES (%s);'%(table_name,self.content))
MySQLdb安装可能会遇到的问题
1. 由于MySQL-Python使用的是API 2.0, 对Python 3.x没有良好的支持,所以只能下载其更新后版本mysqlclient. 注意这里是mysqlclient而不是mysql-client.
2. 在安装好之后如果出现modelErro报错声称找不到mydsql模块,此时需要如下操作
export DYLD_LIBRARY_PATH=/usr/local/mysql/lib/
source ~/.bash_profile
3.SQL格式正确但是没有增加记录:缺失connect.commit()方法,这是非常重要的。
python 适配器的构建
为了能够顺利使用数据分析数据,在使用Python构建模型之前需要构建特定的适配器,适配器可以帮助应用完成数据的读取与存储,批量处理,再编辑等操作。
- I/O操作类构建
- 读和写的具体位置是由指针来决定的,一般从开头开始,逐步完成读写
- 如果逐行读取,在读完数据之后再进行
readline()
操作会出现所读内容为空的情况 - 根据官方文档的建议,逐行读取不如直接使用文件对象的可迭代属性来读取
for line in new_file:
print(line)
- 无论如何不要使用
w+
模式,因为会覆盖掉原数据文件导致数据丢失 - 由于读写操作都需要发出请求以及关闭请求,所以尽量使用
with
语段可以高效率完成任务
#通过构建读写对象来实现功能
with open(file_dir,mode='r',encoding=None, newline=None) as new_file:
#mode可以有多种模式
#r 只读,比较常用
#r+ 可以读写
#a 添加,原先内容会被保留,会添加新的内容
#为了不影响数据原先结构和内容,最好使用只读模式
#newline用来设置换行标识
#使用该对象的read()方法可以读取读写对象的内容
new_file.read(n) #读取最多n个字符
new_file.readline(n)#读取单行最多n个字符
new_file.write(s)#写入所有
new_file.writeline(s)#写入整行