Python连接并操作数据库的各种方法

MySQLdb 和 pymysql

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是一个元祖列表

clickhouse_driver

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()

http方式连接CK – POST

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美化输出

http方式连接CK – GET

    # 使用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

dataset

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)