作业

用Python读取Excel 数据 存放到mysql中

import pymysqlimport openpyxlimport datetimefrom pymysql.cursors import DictCursordef main():conn = pymysql.Connect(host='localhost',port=3306,user='root',password='xxxxxx',database='ali', # 默认使用school 相当于use school;charset='utf8mb4',autocommit=True)# 建立链接游标print('开始建表......')with conn.cursor(DictCursor) as cursor:cursor.execute('''create table if not exists tb_stock(s_date date not null comment '日期',s_open decimal(10,6) unsigned not null comment '开盘价',s_high decimal(10,6) unsigned not null comment '最高价',s_low decimal(10,6) unsigned not null comment '最低价',s_close decimal(10,6) unsigned not null comment '收盘价',s_adjcolse decimal(10,6) unsigned not null comment '调整后的收盘价',s_volume int unsigned not null comment '成交量',primary key (s_date))engine=innodb;''')print('表建立完成!')# 数据准备，获取表对象,行数列数work_book = openpyxl.load_workbook('files/阿里巴巴股票数据.xlsx')name = work_book.sheetnames[0]sheet = work_book[name]rows = sheet.max_rowcolumns = sheet.max_column#数据写入datas=list(sheet.iter_rows())for x in datas[1:]:data=[item.value for item in x[1:]]data.insert(0,x[0].value.strftime("%Y-%m-%d %H:%M:%S"))cursor.execute(f"""insert into tb_stock values ('{data[0]}',{data[1]},{data[2]},{data[3]},{data[4]},{data[5]},{data[6]});""")if __name__ == '__main__':main()

数据集

DateOpenHighLowCloseAdj CloseVolume-01-03 0:00:00898988.08000288.59999888.5999988789400-01-04 0:00:0088.98500190.88999988.58000290.51000290.51000211490200-01-05 0:00:0091.91000494.80999891.63999994.37000394.37000316821500-01-06 0:00:0094.40000294.59393.88999993.8899997639800-01-09 0:00:0094.16000495.65000293.30999894.72000194.72000110792900-01-10 0:00:0096.40000297.90399995.55000396.7596.7514780600-01-11 0:00:0096.91999897.44999795.59999896.94000296.9400028735900-01-12 0:00:0096.84999896.84999894.80000395.90000295.9000028424700-01-13 0:00:0095.94999797.37999795.76000296.26999796.26999710701100-01-17 0:00:0096.3296.5899969596.12000396.1200036301600-01-18 0:00:0096.26999796.26999795.26000296966610700-01-19 0:00:0096.41999896.69999795.58000296.16999896.1699987090000-01-20 0:00:0096.44999797.15000295.67099896.05999896.0599989685800-01-23 0:00:0096.48000398.66999896.26000298.41000498.41000414090300-01-24 0:00:00103.150002103.19000299.940002101.43101.4337181500-01-25 0:00:00102.459999104.269997101.900002104.059998104.05999823322800-01-26 0:00:00103.919998104.57102.169998102.75102.7512504800-01-27 0:00:00102.25102.699997101.290001102.07102.078031000-01-30 0:00:00101.199997101.459999100.089996101.019997101.0199978619800-01-31 0:00:00100.239998102.089996100.059998101.309998101.3099987530000-02-01 0:00:00102.07102.379997100.870003101.57101.575736400-02-02 0:00:00101.279999101.470001100.559998100.839996100.8399965371600-02-03 0:00:00101.5101.559998100.300003100.389999100.3899996197600-02-06 0:00:00100.169998101.706001100.019997100.900002100.9000026866700-02-07 0:00:00101.040001101.599998100.5100.830002100.8300025655600-02-08 0:00:00101.550003103.989998101.160004103.57103.5710178500-02-09 0:00:00103.57104.18102.559998103.339996103.3399968435800-02-10 0:00:00103.879997103.940002102.309998102.360001102.3600017680300-02-13 0:00:00102.82104.440002102.620003103.099998103.0999989729000-02-14 0:00:00103.139999103.599998100.896004101.589996101.58999610192100-02-15 0:00:00102.059998102.129997100.110001101.550003101.55000312384400-02-16 0:00:00101.860001102.099998100.5100.82100.827464000-02-17 0:00:00100.690002101.730003100.300003100.519997100.5199978258200-02-21 0:00:00101.029999102.540001100.910004102.120003102.1200038189600-02-22 0:00:00102.480003105.199997102.419998104.199997104.19999715779400-02-23 0:00:00104.720001104.860001101.82102.459999102.45999910095600-02-24 0:00:00101.389999103101.300003102.949997102.9499977356600-02-27 0:00:00102.5103.824997102.220001103.599998103.5999986865900-02-28 0:00:00103.889999103.989998102.029999102.900002102.9000028017500-03-01 0:00:00103.68104.989998103.209999104.040001104.04000110331800-03-02 0:00:00103.800003104.580002102.769997103.190002103.1900027983000-03-03 0:00:00102.989998103.419998102.440002103.309998103.3099986320900-03-06 0:00:00102.760002103.25102.099998102.309998102.3099986290700-03-07 0:00:00102.300003103.307999102.110001102.629997102.6299974985200-03-08 0:00:00102.629997104.290001102.540001103.220001103.2200017066700-03-09 0:00:00102.970001103.75102.760002103.239998103.2399984523600-03-10 0:00:00103.43103.699997102.629997103.389999103.3899994120700-03-13 0:00:00103.519997106.25103.470001105.18105.1816118900-03-14 0:00:00104.610001106103.821999104.25104.257748400-03-15 0:00:00104.370003104.489998102.599998103.690002103.69000211695200-03-16 0:00:00105.25105.650002104.301003105.629997105.62999712445200-03-17 0:00:00105.169998106.5104.860001105.610001105.6100018778900-03-20 0:00:00106.230003108.199997105.940002107.25107.2512447900-03-21 0:00:00108.459999108.699997104.699997105.089996105.08999613877700-03-22 0:00:00104.639999106.120003103.980003106.089996106.0899967153000-03-23 0:00:00106.949997108.830002106.440002108.25108.2513410800-03-24 0:00:00108.400002109.129997107.230003108.040001108.0400017578400-03-27 0:00:00106.650002108.190002106.510002108.080002108.0800027709400-03-28 0:00:00108.230003108.949997107.459999107.800003107.8000036472000-03-29 0:00:00107.970001109.739998107.690002109.510002109.5100028788700-03-30 0:00:00109.728996110.449997108.660004108.849998108.84999810592500-03-31 0:00:00108.5109.119003107.779999107.830002107.8300029002800-04-03 0:00:00108.849998109.470001107.519997108.089996108.0899968391400-04-04 0:00:00107.870003108.260002106.879997107.519997107.5199976480100-04-05 0:00:00108.360001108.82107.32107.440002107.4400028205400-04-06 0:00:00107.589996108.419998107.150002108.040001108.0400015211100-04-07 0:00:00107.879997109.07106.760002108.989998108.9899988354900-04-10 0:00:00109111.879997108.839996111.699997111.69999714798600-04-11 0:00:00111.400002111.790001109.459999110.440002110.44000210012100-04-12 0:00:00110.5111.699997110.309998110.629997110.6299978099800-04-13 0:00:00110.720001111.449997109.82110.209999110.2099996857000-04-17 0:00:00110.910004111.870003110.300003111.760002111.7600027128100-04-18 0:00:00111.199997112.199997110.866997111.230003111.2300038305100-04-19 0:00:00111.989998112.199997110.57110.760002110.7600029603800-04-20 0:00:00111.529999113.599998111.480003112.959999112.95999913773500-04-21 0:00:00112.800003113.169998112.300003113.110001113.1100018968300-04-24 0:00:00114.040001115.160004114.019997114.860001114.86000113386600-04-25 0:00:00115.540001115.82114.800003115.480003115.48000311434600-04-26 0:00:00115.370003115.75114.622002114.970001114.9700016678800-04-27 0:00:00115115.959999114.830002115.43115.439441500-04-28 0:00:00115.900002115.989998115115.5115.56955700-05-01 0:00:00115.629997117.239998115.5116.68116.687332100-05-02 0:00:00117.07118.690002117118.089996118.08999610048200-05-03 0:00:00117.949997118115.120003116.57116.5710591100-05-04 0:00:00115.940002116.410004115.150002115.860001115.8600016977800-05-05 0:00:00116.050003116.150002114.800003116.040001116.0400017099400-05-08 0:00:00116.900002117.510002116.199997116.860001116.8600016461900-05-09 0:00:00118.62000318.071015219900-05-10 0:00:00120.010002120.330002119.099998119.980003119.9800039347300-05-11 0:00:00120.129997120.5118.25120.160004120.16000411375500-05-12 0:00:00120.160004120.468002119.510002120.339996120.3399969823900-05-15 0:00:00121.029999121.489998120.030998121.400002121.40000211125500-05-16 0:00:00121.980003124.339996121.739998124.019997124.01999718222100-05-17 0:00:00123.25123.889999120.5120.720001120.72000119834700-05-18 0:00:00116.309998122.629997114121.269997121.26999737121900-05-19 0:00:00123.959999126.400002123.07123.220001123.22000121101700-05-22 0:00:00124.980003125.589996123.555124.75124.7513493200-05-23 0:00:00124.669998125.675003122.849998122.93122.9314370100-05-24 0:00:00123.07123.683998121.949997122.809998122.8099988371500-05-25 0:00:00123.739998124.449997122.309998123.629997123.62999712486500-05-26 0:00:00123.93124.650002123.099998123.989998123.9899987558900-05-30 0:00:00124.080002124.879997123.529999123.910004123.9100048343400-05-31 0:00:00123.639999124.175003122122.459999122.45999911582200-06-01 0:00:00122.82124122.260002123.970001123.9700018901400-06-02 0:00:00123.720001124.529999123.239998124.129997124.1299977342100-06-05 0:00:00124.019997125.559998123.870003125.370003125.37000310030600-06-06 0:00:00125.370003126.199997124.269997124.620003124.6200039257100-06-07 0:00:00125.080002125.910004124.110001125.639999125.6399999431600-06-08 0:00:00142.509995143.699997135.210007142.339996142.33999680936900-06-09 0:00:00142.339996148.289993137.009995139.440002139.44000254367400-06-12 0:00:00139.169998139.389999136.050003139.080002139.08000232177600-06-13 0:00:00141.880005142.244003135.899994136.600006136.60000640879600-06-14 0:00:00137.800003139.25135.259995136.669998136.66999832862000-06-15 0:00:00135.639999135.669998133.100006135.080002135.08000230510400-06-16 0:00:00136.279999136.5133.550003134.869995134.86999536431300-06-19 0:00:00137.809998140.399994137.399994139.470001139.47000124951600-06-20 0:00:00140.080002140.630005137.929993138.380005138.38000520580500-06-21 0:00:00138.820007143.5138.020004143.289993143.28999330911600-06-22 0:00:00142.050003144.679993140.880005142.270004142.27000421897600-06-23 0:00:00142.100006143.339996141.600006143.009995143.00999511759000-06-26 0:00:00144.029999145.360001141.270004142.729996142.72999617828100-06-27 0:00:00143.899994144.380005141.100006141.529999141.52999917374800-06-28 0:00:00142.649994144.369995141.479996143.949997143.94999717759800-06-29 0:00:00143.160004143.559998137.520004140.809998140.80999824130300-06-30 0:00:00141.029999142.380005139.919998140.899994140.89999412276700-07-03 0:00:00141.75142.490005139.494995140.990005140.9900056975700-07-05 0:00:00141.160004145140.330002144.869995144.86999516995800-07-06 0:00:00143.009995143.619995141.830002142.199997142.19999711934200-07-07 0:00:00142.649994143.378998141.830994142.429993142.4299938046200-07-10 0:00:00143144.160004142.25143.809998143.80999810351500-07-11 0:00:00144.289993145.869995143.259995145.809998145.80999813519900-07-12 0:00:00147.380005149.300003146.85000614914914955800-07-13 0:00:00149.520004150148149.520004149.52000411683400-07-14 0:00:00150.119995152.25149.690002151.830002151.83000211469400-07-17 0:00:00153.529999153.699997150.419998151.229996151.22999615342700-07-18 0:00:00151.080002154.600006150.479996153.75153.7515313000-07-19 0:00:00155.139999156.410004152.639999153.149994153.14999416896100-07-20 0:00:00153.960007154.119995151.839996152.110001152.11000116739800-07-21 0:00:00151.789993152.149994150.309998151.889999151.88999911452700-07-24 0:00:00152.770004153.229996151.600006152.259995152.25999510649100-07-25 0:00:00152.360001152.75150.850006152.440002152.4400028460300-07-26 0:00:00152.529999156152.360001155.789993155.78999314466300-07-27 0:00:00157.850006160.389999151.330002154.149994154.14999426058500-07-28 0:00:00153.960007158.960007152.820007157.559998157.55999814449400-07-31 0:00:00158.899994159.389999153.5154.949997154.94999717782400-08-01 0:00:00156.25156.610001153.800003154.729996154.72999612035300-08-02 0:00:00155.470001155.75149.100006151.910004151.91000419529300-08-03 0:00:00152.100006153.800003149.600006153.320007153.32000712435400-08-04 0:00:00154.320007154.320007152.160004153.330002153.33000210035600-08-07 0:00:00154.830002158.990005154.369995158.839996158.83999615906400-08-08 0:00:00158.559998159.839996156.800003157.5157.515447100-08-09 0:00:00156.050003157.710007155.199997157.490005157.49000511022600-08-10 0:00:00156.199997156.5151.25151.770004151.77000419298900-08-11 0:00:00150.509995152.570007147.5151.699997151.69999722327400-08-14 0:00:00154.770004155.080002153.100006154.610001154.61000114601800-08-15 0:00:00155.740005158.320007154157.75157.7518635700-08-16 0:00:00160160.350006158.535004159.5159.521318300-08-17 0:00:00166.119995168163.509995163.919998163.91999856840300-08-18 0:00:00165.360001169.5164.100006167.5167.531492500-08-21 0:00:00168.289993170.600006166.979996169.25169.2522352300-08-22 0:00:00170.020004174.770004169.639999174.460007174.46000724481900-08-23 0:00:00172.899994176.229996172.75175.800003175.80000321939000-08-24 0:00:00177177174.30000317517520795200-08-25 0:00:00174.800003174.940002171.110001171.740005171.74000524184700-08-28 0:00:00171.149994171.339996167.059998168.300003168.30000324615900-08-29 0:00:00165.149994169.229996164.350006167.410004167.41000419587300-08-30 0:00:00169.149994172.389999169.149994171.25171.2517770000-08-31 0:00:00172.539993172.899994170.25171.740005171.74000516486800-09-01 0:00:00171.990005172.386002170.649994171.039993171.03999311166600-09-05 0:00:00169.470001171.929993168.738007169.919998169.91999814943600-09-06 0:00:00170.169998171.110001167168.139999168.13999919988300-09-07 0:00:00169.259995170.970001168170.479996170.47999615141900-09-08 0:00:00169.990005171.220001168.91999816916912345300-09-11 0:00:00171.5174.199997171.5174.059998174.05999818257900-09-12 0:00:00174.889999175.619995173.369995175.309998175.30999815629700-09-13 0:00:00175.75179.100006175.559998178.970001178.97000122060700-09-14 0:00:00177.539993179.929993176.020004177.100006177.10000622827700-09-15 0:00:00177.899994179.75176.699997176.699997176.69999726567600-09-18 0:00:00178.889999180.289993177.75179.979996179.97999622919800-09-19 0:00:00180.690002180.869995179.25180.070007180.07000714751900-09-20 0:00:00180.020004180.5176.151001177.949997177.94999715396700-09-21 0:00:00178.050003178.860992175.300003177.389999177.38999914578800-09-22 0:00:00176.199997178.419998175.679993178.139999178.13999912749700-09-25 0:00:00176.479996177167.5169.589996169.58999634033700-09-26 0:00:00170.009995171.899994166.789993167.020004167.02000423696200-09-27 0:00:00169.839996171.949997169.449997170.990005170.99000519444200-09-28 0:00:00170.910004171.880005169.567001170.240005170.24000513957000-09-29 0:00:00171.110001173.539993170.740005172.710007172.71000713401800-10-02 0:00:00174.570007175.130005172.570007173.610001173.61000112563400-10-03 0:00:00176.059998178.779999173.610001178.559998178.55999816714600-10-04 0:00:00178.059998180177.300003178.710007178.71000711420600-10-05 0:00:00179179.729996176.029999178.889999178.88999911895300-10-06 0:00:00177.850006179.869995177.610001179.199997179.1999978749300-10-09 0:00:00178.970001183.13178.75182.089996182.08999615011600-10-10 0:00:00181.649994184.460007181.360001183.119995183.1199951300-10-11 0:00:00183184.699997182.600998184.690002184.69000211974400-10-12 0:00:00184.039993184.389999180.348007180.529999180.52999915441200-10-13 0:00:00180.960007181.399994177.970001178.449997178.44999718107700-10-16 0:00:00180180.050003178.509995179.559998179.55999812855900-10-17 0:00:00179.580002180.119995175.300003175.320007175.3200072200-10-18 0:00:00177.089996180176.279999179.610001179.61000121736500-10-19 0:00:00177.001007179.610001175.449997177.929993177.92999313539000-10-20 0:00:00179.020004179.520004177.080002177.320007177.32000711953200-10-23 0:00:00177.800003178.009995173.050003173.130005173.13000517610900-10-24 0:00:00174175.979996173.259995173.699997173.69999711946000-10-25 0:00:00174.690002175.440002169.300003170.220001170.22000118109400-10-26 0:00:00170.619995171.449997168.580002170.320007170.32000713527400-10-27 0:00:00173.190002177171.110001176.149994176.14999419712600-10-30 0:00:00178.429993181.899994177.589996181.580002181.5800029700-10-31 0:00:00183.570007185.119995181.811005184.889999184.88999921256700-11-01 0:00:00187.880005188.880005183.580002186.080002186.08000228594700-11-02 0:00:00190.990005191.220001183.309998184.809998184.80999841239900-11-03 0:00:00186.509995186.929993182.059998183.210007183.21000719697300-11-06 0:00:00184.070007188.25184187.839996187.83999619873100-11-07 0:00:00189.580002189.860001187188.509995188.50999517781000-11-08 0:00:00187.979996188.149994185.100006185.899994185.89999413944600-11-09 0:00:00184.210007185.130005181.050003185.130005185.13000519755600-11-10 0:00:00187.029999187.169998185.399994186.410004186.41000415031000-11-13 0:00:00186.770004187.880005184184.539993184.53999317139100-11-14 0:00:00184.300003184.380005181.149994181.789993181.78999314475300-11-15 0:00:00179.369995181.880005176.735992181.479996181.47999616567000-11-16 0:00:00183.309998186.270004183.119995185.429993185.42999318389500-11-17 0:00:00185.919998186.5184.669998185.130005185.13000513422700-11-20 0:00:00187.600006190186.61099218818825992300-11-21 0:00:00190.350006191.559998188.800003190.899994190.89999419639500-11-22 0:00:00191.539993191.75189.369995189.839996189.83999614417600-11-24 0:00:00189.389999191.699997188.699997191.190002191.1900029887300-11-27 0:00:00190.490005191.449997187.550003188.029999188.02999914952800-11-28 0:00:00188.830002189.100006186.5186.690002186.69000213378100-11-29 0:00:00185.25185.410004173.619995179.910004179.91000440204100-11-30 0:00:00179.529999179.800003176.320007177.080002177.08000225851100-12-01 0:00:00175.270004179.800003173.800003174.610001174.61000126782300-12-04 0:00:00177.199997177.580002168.259995169.580002169.58000235895100-12-05 0:00:00165.279999172.960007164.25168.960007168.96000731609900-12-06 0:00:00172.630005173.229996167.865005172.630005172.63000523897800-12-07 0:00:00175.289993175.820007172.259995174.470001174.47000115697900-12-08 0:00:00177.570007178.960007176.369995177.619995177.61999523543400-12-11 0:00:00179.899994180.679993178.149994179.289993179.28999315626200-12-12 0:00:00174.639999178.160004173.770004174.639999174.63999922656500-12-13 0:00:00175.190002177.699997174.300003176.470001176.47000118653400-12-14 0:00:00173.110001173.389999169.610001171.75171.7532830000-12-15 0:00:00172.889999173.550003170173.550003173.55000341356700-12-18 0:00:00174.600006174.699997172.369995173.369995173.36999517023300-12-19 0:00:00171.970001173170.509995171.279999171.27999923936200-12-20 0:00:00171.789993172.899994170.800003172.639999172.63999917121400-12-21 0:00:00172.800003176.25172.651001175.320007175.32000714812400-12-22 0:00:00175.839996176.660004175.039993176.289993176.28999312524700-12-26 0:00:00174.550003175.149994171.729996172.330002172.33000212913800-12-27 0:00:00172.289993173.869995171.729996172.970001172.97000110152300-12-28 0:00:00173.039993173.529999171.669998172.300003172.3000039508100-12-29 0:00:00172.279999173.669998171.199997172.429993172.4299939704600