GooglePoiCrawlerV1/utility/database_access.py

# -*- coding: utf-8 -*-
import dataset
import pandas as pd
import pymysql


def check_unique(table, value):
    #---if there is the same url, return 0---
    if len([i for i in table.find(url=value)]) > 0:
        return 0
    else:
        return 1
    
    
def DBconnect():
    db = dataset.connect('sqlite:///gs.db')
    print('Have {} tables: {}'.format(len(db.tables), str(db.tables)))
    
    return db


def Tabelconnect(db, table_name):
    table=db[table_name]
    print('Have {} columns: {}'.format(len(table.columns),str(table.columns)))
    return table


def DataToCsv(table, output_file_name):
    db = dataset.connect('sqlite:///gs.db')
    table=db[table]
    data = pd.DataFrame([dict(i) for i in table])
    print('output data size: {}'.format(len(data)))
    data.to_csv(output_file_name, index=False)
    
    
def mysql_connect(mysql_config, DB_name):
    # Open database connection
    db = pymysql.connect(
            host = mysql_config['MYSQL_HOST'],
            port = mysql_config['MYSQL_PORT'],
            user = mysql_config['MYSQL_USER'],
            password = mysql_config['MYSQL_PASSWORD'],
            db = DB_name,
    )
    return db


def create_table(db, table_name, schema):
    cursor = db.cursor()
    # Drop table if it already exist using execute() method.
    cursor.execute("DROP TABLE IF EXISTS {}".format(table_name))

    cursor.execute(schema)
    print("Created table {} Successfull.".format(table_name))
    
    cursor.close()
    
    
def mysql_data_version(db):
    # prepare a cursor object using cursor() method
    cursor = db.cursor()
    # execute SQL query using execute() method.
    cursor.execute("SELECT VERSION()")
    # Fetch a single row using fetchone() method.
    data = cursor.fetchone()
    print ("Database version : %s " % data)
    
    
def mysql_insert_data(db, insert_sql):
    cursor = db.cursor()
    try:
        # Execute the SQL command
        cursor.execute(insert_sql)
        # Commit your changes in the database
        db.commit()
    except Exception as e:
        print(e)
        # Rollback in case there is any error
        db.rollback()
    cursor.close()
    
    
def mysql_select_data(db, query_sql):
    cursor = db.cursor()
    # Prepare SQL query to select a record from the table.

    try:
        # Execute the SQL command
        cursor.execute(query_sql)
        # Fetch all the rows in a list of lists.
        results = cursor.fetchall()

    except:
        import traceback
        traceback.print_exc()

        print ("Error: unable to fetch data")
        
    return results