2020-11-30 python

Python Graph

from matplotlib import pyplot as plt
import numpy as np

x = np.arange(1,4)
y = x*3
z = x*5

print(x)
print(y)

plt.plot(x,y)
plt.plot(x,z)
plt.show()

サムスン、lgグラフィック

from matplotlib import pyplot as plt
import pymssql
from openpyxl import load_workbook

lg = []
ss = []

conn = pymssql.connect(server="127.0.0.1", user="sa", password="java", database="mypy")
cursor = conn.cursor()

cursor.execute("select top 10 s_price from stock where s_name='LG'")
row = cursor.fetchone()
while row:
    print(row[0])
    lg.append(row[0])
    row = cursor.fetchone()

cursor.execute("select top 10 s_price from stock where s_name='삼성전자'")
row = cursor.fetchone()
while row:
    print(row[0])
    ss.append(row[0])
    row = cursor.fetchone()

conn.close()

x = range(10)
print(ss)
print(lg)
plt.plot(x,lg, label="lg")
plt.plot(x,ss, label="ss")
plt.legend(loc='upper right')
plt.show()

EXcelファイルの作成

import pymssql
import pandas as pd

lg = []
ss = []

conn = pymssql.connect(server="127.0.0.1", user="sa", password="java", database="mypy")
cursor = conn.cursor()

cursor.execute("select top 10 s_price from stock where s_name='LG'")
row = cursor.fetchone()
while row:
    print(row[0])
    lg.append(row[0])
    row = cursor.fetchone()

cursor.execute("select top 10 s_price from stock where s_name='삼성전자'")
row = cursor.fetchone()
while row:
    print(row[0])
    ss.append(row[0])
    row = cursor.fetchone()

conn.close()


raw_data = {'lg' : lg,
             'ss' : ss}
raw_data = pd.DataFrame(raw_data) #데이터 프레임으로 전환
raw_data.to_excel(excel_writer='stock.xlsx', sheet_name='증권', index=False, header=True) #엑셀로 저장

今日のタスク1は、ExcelファイルをDBに入れる

カラム名の取得

import pandas as pd
import pymssql

df = pd.read_excel('stock.xlsx')

conn = pymssql.connect(server="127.0.0.1", user="sa", password="java", database="mypy")
cursor = conn.cursor()
print(df.dtypes)

for i in range(0,2):
    s_name = list(df)[i]
    for j in range(0,10):
        df_iloc = df.iloc[:,[i]]
        s_price  = df_iloc.loc[j][0]
        print(s_name)
        print(s_price)
        
        sql = ""
        sql +="insert into excel (s_name, s_price) values "
        sql += "(%s,%s)"
        cursor.execute(sql,(str(s_name),int(s_price)))
        conn.commit()

conn.close()

今日の第2期の宿題を保存します。