python操作mysql-------CSV文件数据存入数据库（四）

CSV文件读取后插入MySQL数据库

mysql+pymysql://用户名:密码@IP地址/数据库名?charset=UTF8MB4

一行一行插入数据库

csv原始数据，没有行列标题：

插入结果
Id数据是主键自动生成，不用赋值

源代码

import time
import csv
import os
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import  create_engine,Column,String,Integer,Float

#mysql+pymysql://用户名:密码@IP地址/数据库名?charset=UTF8MB4
engine = create_engine('mysql+pymysql://root:root@localhost/finddata?charset=UTF8MB4', echo=True)

#建表
BaseModel=declarative_base()
class BasicInfo(BaseModel):
    # 创建一个表名为basic_info的表
    __tablename__='basic_info'
    id=Column(Integer,primary_key=True)
    ts_code=Column(String(20),default=None,nullable=True,comment='股票代码')
    trade_date=Column(Float,default=None,nullable=True,comment='截至时间')
    open=Column(Float,default=None,nullable=True,comment='开盘价')
    high=Column(Float,default=None,nullable=True,comment='最高价')
    low=Column(Float,default=None,nullable=True,comment='最低价')
    close=Column(Float,default=None,nullable=True,comment='收盘价')
    pre_close=Column(Float,default=None,nullable=True,comment='昨天收盘价')
    change=Column(Float,default=None,nullable=True,comment='')
    pct_chg=Column(Float,default=None,nullable=True,comment='')
    vol=Column(Float,default=None,nullable=True,comment='')
    amount=Column(Float,default=None,nullable=True,comment='成交量')

def __repr__(self):
    ts_code=self.ts_code
    trade_date=self.trade_date
    open=self.open
    high=self.high
    low=self.low
    close=self.close
    pre_close=self.pre_close
    change=self.change
    pct_chg=self.pct_chg
    vol=self.vol
    amount=self.amount
    return f"Course:(ts_code={ts_code},trade_date={trade_date},open={open},high={high},low={low}," \
           f"close={close},pre_close={pre_close},change={change},pct_chg={pct_chg}," \
           f"vol={vol},amount={amount})"

#BaseModel.metadata.create_all(engine)#创建一次就好,

def db_conn():
    #mysql+pymysql://用户名:密码@IP地址/数据库名?charset=UTF8MB4
    Session = sessionmaker()
    conn_info='mysql+pymysql://root:root@localhost/finddata?charset=UTF8MB4'
    engine = create_engine(conn_info, echo=True)
    Session.configure(bind=engine)
    session=Session()
    return session

#插入数据
csv_file_path=os.path.join(os.getcwd(),'8_002624.SZ_day.csv')
open_file=open(csv_file_path,'r')

#读取csv
def read_csv_file():
    start_time=time.time()
    #打开文件并读取
    with open_file as r_read:
        #读取所有内容
        file_read = csv.reader(r_read)
        # 按行遍历读取内容
        row_count = 0
        for row in file_read:
            if row_count == 0:
                row_count += 1
                print(row)
                continue
            row_count+=1
            ts_code = row[0]
            trade_date = row[1]
            open = row[2]
            high = row[3]
            low = row[4]
            close = row[5]
            pre_close = row[6]
            change = row[7]
            pct_chg = row[8]
            vol = row[9]
            amount = row[10]
            try:
                session=db_conn()
                basic_info_obj = BasicInfo(ts_code=ts_code, trade_date=trade_date, open=open,
                                       high=high, low=low, close=close, pre_close=pre_close, change=change,
                                       pct_chg=pct_chg, vol=vol, amount=amount)
                 #数据按行插入数据库
                session.add(basic_info_obj)
                session.commit()
                session.close()
            except Exception as ex:
                print('insert error:{}'.format(ex))
        print('插入({0})条数据，花费:{1}s'.format(row_count-1,time.time()-start_time))


if __name__=="__main__":
    read_csv_file()

column常用列选项

primary_key是否为主键，是为true；
unique是否唯一，true表示这列不允许出现重复的值；
index是否为这列创建索引；
nullable是否允许为空；
default是否为这列定义默认值；
name该属性再数据库中的字段映射；
autoincrement是否自动增加；
onupdate更新是执行的函数；
comment字段描述

批量读取数据插入到mysql

80行一插入

#使用SQLAlchemy操作数据库
#Integer整形映射到数据库int;Float;Double;String;Boolean;等等
from sqlalchemy import  create_engine,Column,String,Integer,Float
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
import time
import csv
import os

#mysql+pymysql://用户名:密码@IP地址/数据库名?charset=UTF8MB4
engine = create_engine('mysql+pymysql://root:root@localhost/finddata?charset=UTF8MB4', echo=True)

#建表
BaseModel=declarative_base()
class BasicInfo(BaseModel):
    # 创建一个表名为basic_info的表
    __tablename__='basic_info'
    id=Column(Integer,primary_key=True)
    ts_code=Column(String(20),default=None,nullable=True,comment='股票代码')
    trade_date=Column(Float,default=None,nullable=True,comment='截至时间')
    open=Column(Float,default=None,nullable=True,comment='开盘价')
    high=Column(Float,default=None,nullable=True,comment='最高价')
    low=Column(Float,default=None,nullable=True,comment='最低价')
    close=Column(Float,default=None,nullable=True,comment='收盘价')
    pre_close=Column(Float,default=None,nullable=True,comment='')
    change=Column(Float,default=None,nullable=True,comment='')
    pct_chg=Column(Float,default=None,nullable=True,comment='')
    vol=Column(Float,default=None,nullable=True,comment='')
    amount=Column(Float,default=None,nullable=True,comment='')

def __repr__(self):
    ts_code = self.ts_code
    trade_date = self.trade_date
    open = self.open
    high = self.high
    low = self.low
    close = self.close
    pre_close = self.pre_close
    change = self.change
    pct_chg = self.pct_chg
    vol = self.vol
    amount = self.amount
    return f"Course:(ts_code={ts_code},trade_date={trade_date},open={open},high={high},low={low}," \
           f"close={close},pre_close={pre_close},change={change},pct_chg={pct_chg}," \
           f"vol={vol},amount={amount})"

#BaseModel.metadata.create_all(engine)#创建一次就好

#连接数据库
def db_conn():
    #mysql+pymysql://用户名:密码@IP地址/数据库名?charset=UTF8MB4
    Session = sessionmaker()
    conn_info = 'mysql+pymysql://root:root@localhost/finddata?charset=UTF8MB4'
    engine = create_engine(conn_info, echo=True)
    Session.configure(bind=engine)
    session = Session()
    return session

#os.path.join() 函数用于路径拼接文件路径，可以传入多个参数。
csv_file_path=os.path.join(os.getcwd(),'8_002558.SZ_day.csv')#os.getcwd(返回当前工作目录。我输入命令让脚本运行的目录
open_file=open(csv_file_path,'r')
#统计csv文件的总行数，利用统计换行符数量的方法
def lines_count():
    f_read=open_file
    cline=0
    while True:
        buffer=f_read.read(8*1024*1024)
        if not buffer:
            break
        cline+=buffer.count('\n')
    f_read.seek(0)
    return cline

#数据批量插入数据库
def batch_insert_into_mysql(basic_info_obj_list):
    try:
        session=db_conn()
        session.add_all(basic_info_obj_list)
        session.commit()
        session.close()
    except Exception as ex:
        print('batch insert error :{}'.format(ex))

#读取csv文件
def read_csv_file():
    start_time=time.time()
    #csv文件总行数统计
    total_line=lines_count()#自定义函数
    with open_file as r_read:
        #读取csv文件全部内容
        file_read=csv.reader(r_read)
        #按行遍历读取内容
        row_count=0
        basic_info_obj_list=list()
        for row in file_read:
            if row_count==0:
                row_count+=1
                print(row)
                continue

            ts_code=row[0]
            trade_date=row[1]
            open=row[2]
            high=row[3]
            low=row[4]
            close=row[5]
            pre_close=row[6]
            change=row[7]
            pct_chg=row[8]
            vol=row[9]
            amount=row[10]

            #构造插入数据库的语句
            basic_info_obj=BasicInfo(ts_code=ts_code,trade_date=trade_date,open=open,
            high=high ,low=low,close=close,pre_close=pre_close,change=change,pct_chg=pct_chg,vol=vol,amount=amount )
            basic_info_obj_list.append(basic_info_obj)

            row_count += 1

            #每80条记录做一次插入
            if row_count%80==0:
                batch_insert_into_mysql(basic_info_obj_list)
                basic_info_obj_list.clear()
                # session = db_conn()
                # session.add_all(basic_info_obj_list)
                # session.commit()
                # session.close()
                # basic_info_obj_list.clear()
                continue

            #剩下数据插入数据库
            if row_count==total_line:
                #batch_insert_into_mysql(basic_info_obj_list)
                batch_insert_into_mysql(basic_info_obj_list)
                basic_info_obj_list.clear()
                # session = db_conn()
                # session.add_all(basic_info_obj_list)
                # session.commit()
                # session.close()
                # basic_info_obj_list.clear()

        print('插入({0})条数据，花费:{1}s'.format(row_count-1,time.time()-start_time))


if __name__=="__main__":
    read_csv_file()

mysql没有安装显示软件，只能在cmd管理员身份打开去查看数据库，相关操作python操作mysql（一）