Mysql 与 Python socket

py1.py

# -*- coding: utf-8 -*-
import sqlalchemy
import tushare
import pandas
import socket
import struct


class Mysql0:

    def __init__(self, user0='test', password0='123456', dsn0='odbcdsn', host0='192.168.1.3', port0=12345):
        self.user0 = user0
        self.password0 = password0
        self.dsn0 = dsn0
        self.engine0 = sqlalchemy.create_engine('mysql+pyodbc://'+self.user0+':'+self.password0+'@'+self.dsn0)
        self.host0 = host0
        self.port0 = port0

    def get_url_data0(self):
        codelist0 = pandas.DataFrame((tushare.get_today_all())['code'])
        codelist0 = codelist0.sort(columns='code', ascending=True)
        codelist0['index'] = pandas.DataFrame([i for i in range(0, len(codelist0))], codelist0.index)
        codelist0.reindex(index=codelist0['code'])
        codelist0.to_sql('codelist', self.engine0, if_exists='replace', index=True, index_label='index')
        self.engine0.connect().execute('alter table testdb.codelist add primary key(`index`)')
        for i in range(0, len(codelist0)):
            stockdata0 = tushare.get_h_data(codelist0['code'][i])
            stockdata0['date'] = pandas.Series(stockdata0.index, stockdata0.index)
            stockdata0 = stockdata0.sort_values(by='date', ascending=True)
            stockdata0['index'] = pandas.DataFrame([i for i in range(0, len(stockdata0))], stockdata0.index)
            stockdata0.to_sql(codelist0['code'][i], self.engine0, if_exists='replace', index=True, index_label='index')
            self.engine0.connect().execute('alter table testdb.'+codelist0['code'][i]+' add primary key(`index`)')

    def get_sql_data0(self, code0='000410'):
        stockdata1 = pandas.read_sql_table(code0, self.engine0)
        return code0, stockdata1

    def run_socket_server0(self):
        socket0 = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
        socket0.bind((self.host0, self.port0))
        socket0.listen(5)
        code1, stockdata2 = self.get_sql_data0()
        i0 = stockdata2['index']
        o0 = stockdata2['open']
        h0 = stockdata2['high']
        l0 = stockdata2['low']
        c0 = stockdata2['close']
        v0 = stockdata2['volume']
        i = 0
        while True:
            connection0, address0 = socket0.accept()
            if connection0.recv(1024) == b'connect' and i < len(i0):
                connection0.send(struct.pack('6si5f', code1.encode('utf-8'), i0[i], o0[i], h0[i], l0[i], c0[i], v0[i]))
                i += 1
            else:
                connection0.send(struct.pack('6si5f', '888888'.encode('utf-8'), 8, 8, 8, 8, 8, 8))
                connection0.close()
                socket0.close()

    def run_socket_client0(self, message0=b'connect'):
        socket1 = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
        socket1.connect((self.host0, self.port0))
        socket1.send(message0)
        code2, i1, o1, h1, l1, c1, v1 = struct.unpack('6si5f', socket1.recv(1024)[:32])
        socket1.close()
        return code2.decode('utf-8'), i1, o1, h1, l1, c1, v1

py2.py

# coding=utf-8
import sys
import py1
sys.path.append('D:\\360data\\MyWorkSpace1')
Py1 = py1.Mysql0()
Py1.run_socket_server0()

py3.py

# coding=utf-8
import sys
import time
import py1
import pandas
sys.path.append('D:\\360data\\MyWorkSpace1')
Py1 = py1.Mysql0()
f0 = open('c:\\1.txt', 'a')
f0.write('code'+','+'index'+','+'open'+','+'high'+','+'low'+','+'close'+','+'volume'+'\n')
while True:
    code1, i1, o1, h1, l1, c1, v1 = Py1.run_socket_client0()
    if code1 == '888888':
        break
    f0.write(code1+','+str(i1)+','+str(o1)+','+str(h1)+','+str(l1)+','+str(c1)+','+str(v1)+'\n')
    time.sleep(0.01)
f0.close()
f1 = pandas.read_csv('c:\\1.txt')
print(f1)

转载于:https://www.cnblogs.com/blog-3123958139/p/5572480.html