code/sqlinsert.py at master · hawksr/code · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123


#!/usr/bin/python
# -*- coding: utf-8 -*-

import datetime
import MySQLdb as mdb


# Obtain a database connection to the MySQL instance
db_host = 'localhost'
db_user = 'name'
db_pass = 'pass'
db_name = 'db-name'
con = mdb.connect(db_host, db_user, db_pass, db_name)

def obtain_parse_wiki_snp500():


  with open('/home/your-name/Downloads/your-file.csv') as yf_data:

    for y in yf_data:
       import unicodedata
       )


       prices = []

       p = y.rstrip('\n')
       p = y.strip().split(',')
       prices.append( (
                   ("KR"),p[0], p[1], p[2], p[3], p[4],p[5]) )


       print prices
  # Amend the data to include the vendor ID and symbol ID


#  print symbol
  # Create the insert strings
       column_str = """symbol, price_date,open_price, high_price, low_price,close_price, volume"""
       insert_str = ("%s, " * 7)[:-2]
       final_str = "INSERT INTO daily_price (%s) VALUES (%s)" % (column_str, insert_str)
      # data = [prices]
     #  print data
  # Using the MySQL connection, carry out an INSERT INTO for every symbol
       with con:
         cur = con.cursor()
         cur.executemany(final_str, prices)


if __name__ == "__main__":
  prices = obtain_parse_wiki_snp500()
  insert_snp500_symbols(prices)