current position:Home>Python quantitative data warehouse building series 2: Python operation database

Python quantitative data warehouse building series 2: Python operation database

2022-01-30 21:44:34 Hang Seng light cloud community

Python Quantitative data warehouse building series 2:Python Operating the database

 This series of tutorials is for quantitative developers , Provide a tutorial on the construction of local quantitative financial data warehouse and a full set of source code . We count with constancy (UDATA) The financial data community is the data source , Drop the basic financial data into the local database . The tutorial provides a full set of source code , Including historical data download and incremental data update , Data update task deployment and daily monitoring .
 Copy code 

In the previous section , We chose MySQL As the database for this series of tutorials , So this article focuses on Python operation MySQL Steps for , Parallel packaging method . At the end of the article, I briefly introduce Python operation MongoDB、SQLite、PostgreSQL database ;

One 、pymysql usage

1、 install pymysql modular

pip install pymysql
 Copy code 

2、 Connect to database

from pymysql import * 
#  Open database connection , Database parameters can be set in MySQL View in the interface or database configuration file 
conn = pymysql.connect(host = ' database IP',
                       port = ' port ',
                       user = ' user name ',
                       password = ' password ',
                       database=' Database name ')

#  Use  cursor()  Method to create a cursor object  cursor
cursor = conn.cursor()

#  After the database operation is completed , Close database connection 
# conn.close()
 Copy code 

3、 common SQL Code execution

from pymysql import * 
#  perform SQL Code : Build table 、 Delete table 、 insert data 
def Execute_Code(sql_str):
    #  Open database connection 
	conn = pymysql.connect(host = '127.0.0.1',port = 3306,user = 'root',
                           password = ' password ',database='udata')
    #  Use  cursor()  Method to create a cursor object  cursor
    cursor = conn.cursor()
    try:
        #  Use execute() Method execution SQL
        cursor.execute(sql)
        #  Commit to database execution 
        conn.commit()
	except:
        #  Rollback on error 
        conn.rollback()
    #  Close database connection 
    conn.close() 
 Copy code 

A、 Build table

sql_str = '''CREATE TABLE TB_Stock_List_Test ( secu_code CHAR(20), hs_code CHAR(20), secu_abbr CHAR(20), chi_name CHAR(40), secu_market CHAR(20), listed_state CHAR(20), listed_sector CHAR(20), updatetime CHAR(20));'''
Execute_Code(sql_str)
 Copy code 

B、 insert data

sql_str = ''' INSERT INTO TB_Stock_List_Test (`secu_code`,`hs_code`,`secu_abbr`,`chi_name`,`secu_market`,`listed_state` ,`listed_sector`,`updatetime`) VALUES ('000001','000001.SZ',' Ping An Bank ',' Ping An Bank Co., Ltd ',' Shenzhen Stock Exchange ',' list ', ' a main board ','2021-10-25 20:10:55'); '''
Execute_Code(sql_str)
 Copy code 

C、 Update data

sql_str = "UPDATE tb_stock_list SET updatetime = '2021-10-30 20:10:55' "
Execute_Code(sql_str)
 Copy code 

D、 Delete data

sql_str = 'DELETE FROM tb_stock_list'
Execute_Code(sql_str)
 Copy code 

E、 Delete table

sql_str = 'DROP TABLE IF EXISTS tb_stock_list'
Execute_Code(sql_str)
 Copy code 

4、 Query operation

def Select_Code(sql_str):
    #  Open database connection 
	conn = pymysql.connect(host = '127.0.0.1',port = 3306,user = 'root',
                           password = ' password ',database='udata')
    #  Use  cursor()  Method to create a cursor object  cursor
    cursor = conn.cursor()
    #  Use execute() Method execution SQL
    cursor.execute(sql_str)
    #  Get a list of all records 
    results = cursor.fetchall()
    #  Close database connection 
    conn.close()
    
    return results
 Copy code 
sql_str = 'select * from tb_stock_list'
results = Select_Code(sql_str)
results
 Copy code 

5、 Method encapsulation

Use the above usage , Encapsulated as a custom class , Save as MySQLOperation.py file , The code is as follows :

from pymysql import * 
# MySQL Operation function 
class MySQLOperation:
    def __init__(self, host, port, db, user, passwd, charset='utf8'):
        #  Parameter initialization 
        self.host = host
        self.port = port
        self.db = db
        self.user = user
        self.passwd = passwd
        self.charset = charset
    def open(self):
        #  Open database connection 
        self.conn = connect(host=self.host,port=self.port
                            ,user=self.user,passwd=self.passwd
                            ,db=self.db,charset=self.charset)
        #  Use  cursor()  Method to create a cursor object  cursor
        self.cursor = self.conn.cursor()
    def close(self):
        #  Disconnect the database 
        self.cursor.close()
        self.conn.close()
    def Execute_Code(self, sql):
        #  perform SQL Code : Build table 、 Delete table 、 insert data 
        try:
            self.open()               #  Open database connection 
            self.cursor.execute(sql)  #  Use execute() Method execution SQL
            self.conn.commit()        #  Commit to database execution  
            self.close()              #  Disconnect the database 
        except Exception as e:
            self.conn.rollback()      #  Rollback on error 
            self.close()              #  Disconnect the database 
            print(e)
    def Select_Code(self, sql):
        #  perform SQL Code , Query data 
        try:
            self.open()                        #  Open database connection 
            self.cursor.execute(sql)           #  Use execute() Method execution SQL
            result = self.cursor.fetchall()    #  Get a list of all records 
            self.close()                       #  Disconnect the database 
            return result                      #  Return query data 
        except Exception as e:
            self.conn.rollback()               #  Rollback on error 
            self.close()                       #  Disconnect the database 
            print(e)
 Copy code 

The usage of insert and query is as follows , Other uses are similar , No more details here ;

import pandas as pd
host='127.0.0.1'
port=3306
user='root'
passwd=" password "
db='udata'
#  Method instantiation 
MySQL = MySQLOperation(host, port, db, user, passwd)
#  Insert operation code 
sql_str = ''' INSERT INTO tb_stock_list (`secu_code`,`hs_code`,`secu_abbr`,`chi_name`,`secu_market`,`listed_state`,`listed_sector`,`updatetime`) VALUES ('000001','000001.SZ',' Ping An Bank ',' Ping An Bank Co., Ltd ',' Shenzhen Stock Exchange ',' list ', ' a main board ','2021-10-25 20:15:55'); '''
MySQL.Execute_Code(sql_str)
#  Query data 
sql_str = 'select * from tb_stock_list'
results = MySQL.Select_Code(sql_str)
results
 Copy code 

Two 、sqlalchemy usage

Because of the above pymysql Usage has been able to meet most use needs ,sqlalchemy The implementation function is similar . Here is a brief introduction based on sqlalchemy Linked database pandas.to_sql and pandas.read_sql operation .

1、 install pymysql modular

pip install sqlalchemy
 Copy code 

2、 Connect to database

from sqlalchemy import create_engine
host='127.0.0.1'
port = 3306
user='root'
password=' password '
database='udata'
engine = create_engine('mysql://{0}:{1}@{2}:{3}/{4}?charset=utf8'.format(user
                                                                         ,password
                                                                         ,host
                                                                         ,port
                                                                         ,database))
 Copy code 

3、pandas.to_sql

take DataFrame Data in , write in MySQL database , The code example is as follows :

import pandas as pd
#  Define the data to be written ,DataFrame Format 
data = pd.DataFrame([['000001','000001.SZ',' Ping An Bank ',' Ping An Bank Co., Ltd '
                      ,' Shenzhen Stock Exchange ',' list ',' a main board ','2021-10-25 20:12:55'],
                   ['000002','000002.SZ',' ten thousand   Families, A',' Vanke Enterprise Co., Ltd '
                    ,' Shenzhen Stock Exchange ',' list ',' a main board ','2021-10-25 20:12:55']])
#  Column name assignment 
data.columns = ['secu_code','hs_code', 'secu_abbr', 'chi_name'
                , 'secu_market', 'listed_state','listed_sector','updatetime']
#  Write to database 
data.to_sql(name='tb_stock_list', con=engine, index=False, if_exists='append')
 Copy code 

if_exists Parameter is used to process when the target table already exists , The default is fail, That is, if the target table exists, it will fail . The other two options are replace Represents an alternative to the original table , Delete and then create ,append Option to add data only .

4、pandas.read_sql

From the database , Read data as DataFrame, The code example is as follows :

#  take sql Query results , The assignment is result
result = pd.read_sql('''SELECT * FROM tb_stock_list ''', con=engine)
result
 Copy code 

3、 ... and 、Python Operate other common databases

1、MongoDB

(1) install pymongo:pip install pymongo

(2) Operation introduction

import pymongo
#  Connect MongoDB
conn = pymongo.MongoClient(host='localhost',port=27017
                           ,username='username', password='password')
#  Specify database 
db = conn['udata']  # db = client.udata
#  Specify the collection 
collection = db['tb_stock_list']  # collection = db.tb_stock_list
#  insert data  insert_one()、insert_many()
data1 = {}  #  aggregate , Key value pair ,1 Data 
data2 = {}  #  aggregate , Key value pair ,1 Data 
result = collection.insert_many([data1, data2])
# result = collection.insert_one(data1)
#  Query data  find_one()、find()
result = collection.find_one({'secu_code': '000001'})
#  Update data  update_one()、update()
result = collection.update_one({'secu_code': '000001'}, {'$set': {'hs_code': '000001'}})
#  Delete data  remove()、delete_one() and delete_many()
result = collection.remove({'secu_code': '000001'})
 Copy code 

2、SQLite

(1) install sqlite3:pip install sqlite3

(2) Operation introduction

import sqlite3
#  Connect to database 
conn = sqlite3.connect('udata.db')
#  Create cursors 
cursor = conn.cursor()
#  perform SQL
sql = " Increase, decrease, deletion, etc SQL Code "
cursor.execute(sql)
#  Query data 
sql = " Inquire about sql Code "
values = cursor.execute(sql)
#  Submit things 
conn.commit()
#  Close cursor 
cursor.close()
#  Close the connection 
conn.close()
 Copy code 

3、PostgreSQL

(1) install psycopg2:pip install psycopg2

(2) Operation introduction

import psycopg2
#  Connect to database 
conn = psycopg2.connect(database="udata", user="postgres"
                        , password=" password ", host="127.0.0.1", port="5432")
#  Create cursors 
cursor = conn.cursor()
#  perform SQL
sql = " Increase, decrease, deletion, etc SQL Code "
cursor.execute(sql)
#  Query all data 
sql = " Inquire about sql Code "
cursor.execute(sql)
rows = cursor.fetchall()
#  Submit sth 
conn.commit()
#  Close database connection 
conn.close()
 Copy code 

Sum up ,Python The brief introduction of operating the database is over ; There are many types of databases ,Python The process of operating them is similar , In the future, I will continue to sort out relevant materials .

Next section 《Python Quantitative investment data warehouse construction 3: Data drop library code encapsulation 》

copyright notice
author[Hang Seng light cloud community],Please bring the original link to reprint, thank you.
https://en.pythonmana.com/2022/01/202201302144324364.html

Random recommended