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

MySQLdb 和 pymysql

PyMySQL 是在 Python3.x 版本中用于连接 MySQL 服务器的一个库，Python2中则使用mysqldb。

PyMySQL 遵循 Python 数据库 API v2.0 规范，并包含了 pure-Python MySQL 客户端库

import MySQLdb
import sys
reload(sys)
sys.setdefaultencoding('utf-8')

conn = MySQLdb.connect(
    host='m6103i.apollo.grid.sina.com.cn',
    port=6103,
    user='dipadmin',
    passwd='dipqwe123',
    db='auto_bip',
    connect_timeout=3,
    charset="utf8")
cur = conn.cursor()

sql = """
        INSERT INTO es_domain_2021(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 es_domain_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()

clickhouse_driver

from clickhouse_driver import Client，connect

/方式一
client = Client(host='ck9x.mars.grid.sina.com.cn', port=9000, 
user='ck_rw', password='GuovHJWa',database='bip')
sql = "select value from kafka_consumer_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_str = "insert into bip.kafka_consumer_lag(date, datetime, cluster, topic, consumer_group, partition, value) values"
conn = connect(host='ck9x.mars.grid.sina.com.cn', port=9000, user='ck_rw', password='GuovHJWa',
               database='bip')
cs = conn.cursor()  # 获取光标
cs.executemany(sql_str,data)	/批量插入
conn.commit()
cs.close()
conn.close()