赞
踩
PyMySQL 是在 Python3.x 版本中用于连接 MySQL 服务器的一个库,Python2中则使用mysqldb。
PyMySQL 遵循 Python 数据库 API v2.0 规范,并包含了 pure-Python MySQL 客户端库
def select_sql(db,sql): try: conn = MySQLdb.connect(host=host, user=user, port=port, passwd=password, db=db, charset='utf8') except Exception, e: print str(e) print "connection failed" return -1 cursor = conn.cursor() cursor.execute(sql) data = cursor.fetchall() cursor.close() conn.commit() conn.close() return data sql = "select htVal".format(yesterday) data = select_sql('aut_2022',sql)
import MySQLdb import sys reload(sys) sys.setdefaultencoding('utf-8') conn = MySQLdb.connect( host='m6id.om.cn', port=6103, user='dmin', passwd='d', db='auip', connect_timeout=3, charset="utf8") cur = conn.cursor() sql = """ INSERT INTO es_1(cluster, cluster_domin, version, port, notes, master_ip, master_ip_list, real_ip_list, record_time) VALUES ("%s","%s","%s","%s","%s","%s","%s","%s","%s") """ % (cluster, cluster_domin, version, port, notes, master_ip, master_ip_list, real_ip_list, record_time_info) cur.execute(sql) conn.commit() /插入语句需要提交 sql = "select * from ein_2021" data = cur.execute(sql) /data是返回的行数 # print data,type(data) # for row in cur.fetchall(): # print row cluster_list = [] for i in range(data): row_data = cur.fetchone()
def insert_sql_many(db,sql,data): try: conn = MySQLdb.connect(host=host, user=user, port=port, passwd=password, db=db, charset='utf8') except Exception, e: print str(e) print "connection failed" return -1 cursor = conn.cursor() cursor.executemany(sql,data) cursor.close() conn.commit() conn.close() row = (asset_number,ip,idc,buy_date,server_tag_info,service_type,L1,L2,L3,is_lend,note,today) rows.append(row) insert_sql = """ insert INTO test.bne_test(asset_nime) VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) """ insert_sql_many('test',insert_sql,rows)
rows是一个元祖列表
from clickhouse_driver import Client,connect ///方式一 /查询 client = Client(host='c.com.cn', port=9000, user='cw', password='vHJWa',database='p') sql = "select value from _lag_all where date = '%s' and datetime >= '%s' and cluster = '%s' and topic = '%s' and consumer_group = '%s';" % (last_time_date, last_time_zero, cluster, topic, group) ans = client.execute(sql) /插入 sql = 'INSERT INTO tb_test (t1,t2,t3) VALUES (1,2,3)' ans = client.execute(sql) print(ans) ///方式二 /查寻 conn = connect(host='a.com.cn', port=9000, user='_rw', password='GJWa', database='system') cur = conn.cursor() sql_str = "select database, name from tables where engine='Distributed' and database not in ('test', 'system', 'default')" ans = cur.execute(sql_str) /ans是返回数据的行数 print cur.fetchall() /插入 sql_str = "insert into bip.kafg(date, datetime, cluster, topic, consumer_group, partition, value) values" conn = connect(host='cm.cn', port=9000, user='cw', password='GuJWa', database='bip') cs = conn.cursor() # 获取光标 cs.executemany(sql_str,data) /批量插入,data是一个元祖的列表 conn.commit() cs.close() conn.close()
import urllib.parse
import pandas as pd
import requests
clickhouse_host = "http://ckcn:83/"
params = {
"user": "cw",
"password": "Gua",
"database": "p"
}
post_url = '{}?{}'.format(clickhouse_host, urllib.parse.urlencode(params))
sql = """select * from consll where date = today() and consumer_group = '{}'
and datetime = (select max(datetime) from c_all where date = today() and coup = '{}') FORMAT JSON""".format(csm_group,csm_group)
# 以Json方式获取结果,注意:如果以Json格式获取结果,必须要在sql的最后指定 FORMAT JSON
result = requests.post(post_url, data=sql).json()
text = json.dumps(result['data'][0],indent=3) //indent=3美化输出
# 使用get请求,可以直接把query添加到url参数中 clickhouse_host = "http://c.c.cn:83/" params = { "user": "scort", "password": "SkomDd", "database": "se", "query": "SELECT date, uniq(sn) as num FROM se.nvt_msg_all WHERE date >= (today() - 7) GROUP BY date " "ORDER BY date DESC FORMAT JSON " } get_url = '{}?{}'.format(clickhouse_host, urllib.parse.urlencode(params)) # 发送get请求,执行查询 result = requests.get(get_url) # 以Json方式获取结果,注意:如果以Json格式获取结果,必须要在sql的最后指定 FORMAT JSON r = result.json() # 将查询结果转换为DataFrame,方便使用 df = pd.DataFrame(r['data']) return df
def 1003_conn(db_name):
"""
connection to m6103
:param db_name:
:return:
"""
conn = dataset.connect(
url="mysql://din:d3@m6.s.com:1003/{}?"
"charset=utf8mb4&use_unicode=0".format(db_name)
)
return conn
m6103_conn = 1003_conn('naator')
sql = "SELECT distinct ck_cluster, table_info FROM table_metadata"
res_list = m6103_conn.query(sql)
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。