python 调mysql读写

MYSQL_URL='jdbc:mysql://ip:port/库名?useUnicode=true&autoReconnect=true&rewriteBatchedStatements=TRUE'
MYSQL_USER='admin'
MYSQL_PASSWORD='password'


class SearchMonitor(object):

    def __init__(self):
        """
        初始化数据库连接
        """
        # self.end_date = datetime.datetime.strptime(time.strftime('%Y%m%d', time.localtime(time.time())), "%Y%m%d"
        spark = SparkSession \
            .builder \
            .appName("readandwrite") \
            .enableHiveSupport() \
            .config("spark.sql.crossJoin.enabled", "true")\
            .getOrCreate()
        self.spark = spark

        mysql_prop = {'user': MYSQL_USER,
            'password': MYSQL_PASSWORD,
            'driver': 'com.mysql.jdbc.Driver',
            'rewriteBatchedStatements': 'true'}
        self.mysql_prop = mysql_prop

    def test(self,datestr):
 
        sql = ''' select 字段1 from table where date = "{}"'''.format(start_date)
        print num_query_sql
        data = self.spark.sql(sql)
        if data.count():
            data = num_query_data.withColumn("start_date", F.lit(start_date))
            data = num_query_data.withColumn("end_date", F.lit(end_date))
            data = num_query_data.withColumn("update_date", F.lit(today))
            data.show()
            data.write.jdbc(url=MYSQL_URL, table='表名', mode='append', properties=self.mysql_prop)