使用Python Pandas读取CSV并保存至MySQL数据库

数据读取与预处理

在将数据存入数据库之前，首先需要利用 pandas 库读取本地的 .csv 文件。读取后，通常需要检查并处理数据的类型，特别是日期字段。例如，可以通过 pd.to_datetime() 将特定列转换为日期时间类型，并使用 data.dtypes 查看各列的数据类型，确保数据格式符合预期。

data=pd.read_csv('G:\data_operation\python_book\chapter5\\sales.csv')

data.iloc[:,1]=pd.to_datetime(data.iloc[:,1])

方法一：利用MySQLdb库封装类保存

第一种方法是使用 MySQLdb 库手动封装一个数据库操作类。该类负责建立连接、创建表以及插入数据。虽然这种方法灵活，但处理大量数据时耗时较长。

1. 建立连接与创建表

在初始化类时，需要指定数据库的用户名、主机、密码和数据库名。创建表时，需动态生成SQL语句，确保字段类型（如 varchar(20)）与数据长度匹配。

class Jess_mysql():
 """
 设置mysql类，实现创建数据框，表，及添加数据
 """
 def __init__(self):
  self.mysql=MySQLdb.connect(user=mysql_name,host=mysql_host,password=mysql_password,database=mysql_database)
  self.conn=self.mysql.cursor()
 def create_table(self,table_names,col_names):
  """
  创建表
  :param table_names: 表名
  :param col_names: 列名，列表格式
  :return:
  """
  tables=' varchar(20),'.join(['%s'] *len(col_names))
  sql_yuju='create table if not exists `{t}` ({v} varchar(20))'.format(t=table_names,v=tables)#字段需要标注格式
  ss=sql_yuju %(tuple(col_names))
  print(ss)
  self.conn.execute(ss)
  self.mysql.commit()

2. 插入数据

在插入数据时，同样需要动态构建 INSERT 语句。注意处理列名和数据值的拼接，确保SQL语法正确。

def add_data(self,table_name,col_names,col_data):
  """
  :param table_name: 表名
  :param col_names: 列名，字段名
  :param col_data: 字段值
  :return:
  """
  colname=','.join(['%s']*len(col_names))
  data=','.join(['%s']*len(col_data))
  sql_yuju='INSERT INTO `{t}` ({name}) VALUES ({data});'.format(t=table_name,name=colname,data=data)
  ss=sql_yuju%(*col_names,*col_data)
  #print(ss)
  self.conn.execute(ss)
  self.mysql.commit()

方法二：利用SQLAlchemy与to_sql保存

第二种方法是使用 SQLAlchemy 创建数据库引擎，并结合 pandas 的 to_sql 方法保存数据。这种方法代码更简洁，且性能通常优于手动封装类。

1. 创建数据库引擎

使用 sqlalchemy.create_engine() 建立与MySQL的连接。连接字符串格式为 mysql+mysqldb://user:password@host:port/database。

import sqlalchemy
#engine=sqlalchemy.create_engine('mysql + mysqldb://root:123456@118.24.26.227:3306/python_yuny')
engine=sqlalchemy.create_engine('mysql+mysqldb://{user}:{password}@{host}:3306/{database}'.format
        (user=mysql_name,password=mysql_password,host=mysql_host,database=mysql_database))

2. 保存数据至MySQL

利用 pd.io.sql.to_sql() 将DataFrame直接写入数据库。可以指定表名、连接引擎、是否包含索引以及当表已存在时的处理策略（如 'append'）。

pd.io.sql.to_sql(frame=data,name='yunying',con=engine,index=False,if_exists='append')

日期格式处理与注意事项

在保存数据时，日期格式的处理至关重要。如果MySQL字段设置为 varchar(20)，而原始日期格式不匹配，可能导致数据写入不全。解决方法是使用 pandas.to_datetime() 设置 format 参数，确保格式与原始字符一致。例如，使用 datetime 库将日期转换为 '%Y%m%d' 格式，即可顺利写入 varchar(20) 字段。

x=data.shape[0]
for i in range(x):
 col_data=list(df.iloc[i,:])
 col_data[1]=datetime.date.strftime(col_data[1],'%Y%d%m')

读取MySQL数据

保存完成后，可以使用 pd.read_sql() 从MySQL读取数据。默认情况下，读取时不设置索引列，但可以自行指定索引列名。

df=pd.read_sql('select * from %s'%table_name,con=engine,index_col=None)

以上就是使用Python的Pandas库读取CSV文件并保存至MySQL数据库的详细内容，更多关于Python数据处理和数据库操作的技术资料请关注本站其它相关文章！