LoginSignup
1

More than 3 years have passed since last update.

備忘碌 mysql-connector-python を利用したレコード取得

Posted at

概要

機械学習の為、SQLを利用して在る期間のレコードを取得する ソースを作成したが、pandasで代用出来るとの事で不要となった
備忘録も兼ねてソースを記載する。

import

import mysql.connector

palam

toDB = '【database name】'
toTable_1H = '【table name】'
sql_usr = '【mysql user】'
sql_pass = r"【mysql user password】"
sql_host = '【database server host name】'
sql_port = '【port】'
sql_char = '【charset】'

class

def get_vol(tables,start_time,end_time):
    sql_query = "select * from " + toTable_1H + " where date(date) >= '" + start_time +"' and date(date) <= '" + end_time + "'"
    cur.execute(sql_query)
    res = cur.fetchall()
    return res

connection

conn = mysql.connector.connect(
    host=sql_host,
    port=sql_port,
    user=sql_usr,
    password=sql_pass,
    database=toDB
)

# connection DB from ping check
conn.ping(reconnect=True)

# DB pointer maked
cur = conn.cursor(buffered=True)

get volume

res = get_vol(toTable_1H,'2018-3-22','2018-3-24')

disconnection database

cur.close()
conn.close()

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
1