from sqlalchemy import create_engine
import pandas as pd
import vk
import numpy as np
import time
from datetime import datetime

class ConfigAPI(object):
    __token = None
    api = None

    #Стартовый обработчик. Подключает TOKEN VK API к приложению
    def init(self):
        print('Вставьте ссылку в строку браузера для получения токена VK API. ')
        print('https://oauth.vk.com/authorize?client_id=7629255&display=page&scope=status,video,photos,friends,wall,notes,groups,docs&response_type=token&v=5.103&state=123456')
        print('После скопируйте ссылку из браузера и вставьте сюда')
        val = str(input())
        self.__get_token_by_link(val) #токен обрабатывается на ошибки в этом методе
        self.__vk_api_on() # подключение VK API к системе


    def __get_token_by_link(self, str):
        arr = str.split('=')
        if(len(arr) >= 2):
            arr2 = arr[1].split('&')
            token = arr2[0]
            print(token)
            self.__token = token
        else:
            print('ссылка повреждена, попробуйте еще раз!')
            print(' ')
            self.init()

    def __vk_api_on(self):
        token = self.__token
        try:
            if(token != None):
                session = vk.Session(token)
                self.api = vk.API(session, v=5.103)

                user = self.api.users.get(user_ids=1) #не работает...

                print('Токен подключен!')
                print(' ')
            else:
                print('Токен отсутствует, попробуйте еще раз!')
                print(' ')
                self.init()
        except Exception:
            print('Токен недействителен или поврежден, попробуйте еще раз')
            print(' ')
            self.init()

c = ConfigAPI()
c.init()

dataset = pd.read_excel('vk_database.xlsx')

df_ids = pd.read_excel('vk_ids.xlsx')
df_ids = df_ids[df_ids.columns.drop(list(df_ids.filter(regex='^Unnamed')))]

# сhange i to last index in database
i = 54890
while i < len(df_ids):
    #     print('i: {}'.format(i))
    print('')
    people = c.api.users.get(user_ids=df_ids[i:i + 998]['id'],
                             fields=['sex', 'bdate', 'city', 'home_town', 'education', 'universities', 'schools',
                                     'status', 'followers_count', 'common_count', 'occupation', 'nickname', 'relatives',
                                     'relation', 'personal', 'connections', 'exports', 'activities', 'interests',
                                     'music', 'movies', 'tv', 'books', 'games', 'about', 'quotes', 'can_see_audio'])
    for j in range(len(people)):
        #         print('j: {}'.format(j))
        if (df_ids['id'][i + j] == people[j]['id']):
            dataset.loc[i + j, 'id'] = people[j]['id']
            dataset.loc[i + j, 'name'] = people[j]['first_name']
            dataset.loc[i + j, 'last_name'] = people[j]['last_name']
            if (people[j].get('deactivated') == None):
                if (people[j]['is_closed'] == False):
                    if (people[j].get('sex') != None):
                        dataset.loc[i + j, 'sex'] = people[j]['sex']
                    if (people[j].get('bdate') != None):
                        dataset.loc[i + j, 'bdate'] = people[j]['bdate']
                    if (people[j].get('city') != None):
                        dataset.loc[i + j, 'city'] = people[j]['city']['title']
                    if (people[j].get('home_town') != None):
                        dataset.loc[i + j, 'home_town'] = people[j]['home_town']
                    if (people[j].get('education') != None):
                        dataset.loc[i + j, 'education'] = people[j]['education']
                    if (people[j].get('schools') != None):
                        dataset.loc[i + j, 'schools'] = len(people[j]['schools'])
                    if (people[j].get('status') != None):
                        dataset.loc[i + j, 'status'] = people[j]['status']
                    if (people[j].get('followers_count') != None):
                        dataset.loc[i + j, 'followers_count'] = people[j]['followers_count']
                    if (people[j].get('common_count') != None):
                        dataset.loc[i + j, 'common_count'] = people[j]['common_count']
                    if (people[j].get('occupation') != None):
                        dataset.loc[i + j, 'occupation'] = people[j]['occupation']['type']
                    if (people[j].get('nickname') != None):
                        dataset.loc[i + j, 'nickname'] = people[j]['nickname']
                    if (people[j].get('relatives') != None):
                        if (len(people[j]['relatives'])) > 0:
                            dataset.loc[i + j, 'relatives'] = len(people[j]['relatives'])
                    if (people[j].get('relation') != None):
                        dataset.loc[i + j, 'relation'] = people[j]['relation']
                    if (people[j].get('personal') != None):
                        if (people[j]['personal'].get('langs') != None):
                            dataset.loc[i + j, 'langs'] = len(people[j]['personal']['langs'])
                        if (people[j]['personal'].get('people_main') != None):
                            dataset.loc[i + j, 'people_main'] = people[j]['personal']['people_main']
                        if (people[j]['personal'].get('life_main') != None):
                            dataset.loc[i + j, 'life_main'] = people[j]['personal']['life_main']
                        if (people[j]['personal'].get('smoking') != None):
                            dataset.loc[i + j, 'smoking'] = people[j]['personal']['smoking']
                        if (people[j]['personal'].get('alcohol') != None):
                            dataset.loc[i + j, 'alcohol'] = people[j]['personal']['alcohol']
                        if (people[j]['personal'].get('religion_id') != None):
                            dataset.loc[i + j, 'religion_id'] = people[j]['personal']['religion_id']

                    if (people[j].get('connections') != None):
                        dataset.iloc[i + j, 'connections'] = people[j]['connections']
                    if (people[j].get('exports') != None):
                        dataset.loc[i + j, 'exports'] = people[j]['exports']
                    if (people[j].get('activities') != None):
                        dataset.loc[i + j, 'activities'] = people[j]['activities']
                    if (people[j].get('interests') != None):
                        dataset.loc[i + j, 'interests'] = people[j]['interests']
                    if (people[j].get('music') != None):
                        dataset.loc[i + j, 'music'] = people[j]['music']
                    if (people[j].get('movies') != None):
                        dataset.loc[i + j, 'movies'] = people[j]['movies']
                    if (people[j].get('tv') != None):
                        dataset.loc[i + j, 'tv'] = people[j]['tv']
                    if (people[j].get('books') != None):
                        dataset.loc[i + j, 'books'] = people[j]['books']
                    if (people[j].get('games') != None):
                        dataset.loc[i + j, 'games'] = people[j]['games']
                    if (people[j].get('about') != None):
                        dataset.loc[i + j, 'about'] = people[j]['about']
                    if (people[j].get('quotes') != None):
                        dataset.loc[i + j, 'quotes'] = people[j]['quotes']
                    if (people[j].get('can_see_audio') != None):
                        dataset.loc[i + j, 'can_see_audio'] = people[j]['can_see_audio']
    print('saving vk db time: {}'.format(datetime.now()))
    # dataset.to_excel('vk_database.xlsx')
    print('saved at: {}'.format(datetime.now()))
    i += 998
    if (i > len(dataset)):
        break



engine = create_engine('')
dataset.to_sql('table_name', engine)
from sqlalchemy import create_engine
import pandas as pd
import vk
import numpy as np
import time
from datetime import datetime

class ConfigAPI(object):
    __token = None
    api = None

    #Стартовый обработчик. Подключает TOKEN VK API к приложению
    def init(self):
        print('Вставьте ссылку в строку браузера для получения токена VK API. ')
        print('https://oauth.vk.com/authorize?client_id=7629255&display=page&scope=status,video,photos,friends,wall,notes,groups,docs&response_type=token&v=5.103&state=123456')
        print('После скопируйте ссылку из браузера и вставьте сюда')
        val = str(input())
        self.__get_token_by_link(val) #токен обрабатывается на ошибки в этом методе
        self.__vk_api_on() # подключение VK API к системе


    def __get_token_by_link(self, str):
        arr = str.split('=')
        if(len(arr) >= 2):
            arr2 = arr[1].split('&')
            token = arr2[0]
            print(token)
            self.__token = token
        else:
            print('ссылка повреждена, попробуйте еще раз!')
            print(' ')
            self.init()

    def __vk_api_on(self):
        token = self.__token
        try:
            if(token != None):
                session = vk.Session(token)
                self.api = vk.API(session, v=5.103)

                user = self.api.users.get(user_ids=1) #не работает...

                print('Токен подключен!')
                print(' ')
            else:
                print('Токен отсутствует, попробуйте еще раз!')
                print(' ')
                self.init()
        except Exception:
            print('Токен недействителен или поврежден, попробуйте еще раз')
            print(' ')
            self.init()

c = ConfigAPI()
c.init()

dataset = pd.read_excel('vk_database.xlsx')

df_ids = pd.read_excel('vk_ids.xlsx')
df_ids = df_ids[df_ids.columns.drop(list(df_ids.filter(regex='^Unnamed')))]

# сhange i to last index in database
i = 54890
while i < len(df_ids):
    #     print('i: {}'.format(i))
    print('')
    people = c.api.users.get(user_ids=df_ids[i:i + 998]['id'],
                             fields=['sex', 'bdate', 'city', 'home_town', 'education', 'universities', 'schools',
                                     'status', 'followers_count', 'common_count', 'occupation', 'nickname', 'relatives',
                                     'relation', 'personal', 'connections', 'exports', 'activities', 'interests',
                                     'music', 'movies', 'tv', 'books', 'games', 'about', 'quotes', 'can_see_audio'])
    for j in range(len(people)):
        #         print('j: {}'.format(j))
        if (df_ids['id'][i + j] == people[j]['id']):
            dataset.loc[i + j, 'id'] = people[j]['id']
            dataset.loc[i + j, 'name'] = people[j]['first_name']
            dataset.loc[i + j, 'last_name'] = people[j]['last_name']
            if (people[j].get('deactivated') == None):
                if (people[j]['is_closed'] == False):
                    if (people[j].get('sex') != None):
                        dataset.loc[i + j, 'sex'] = people[j]['sex']
                    if (people[j].get('bdate') != None):
                        dataset.loc[i + j, 'bdate'] = people[j]['bdate']
                    if (people[j].get('city') != None):
                        dataset.loc[i + j, 'city'] = people[j]['city']['title']
                    if (people[j].get('home_town') != None):
                        dataset.loc[i + j, 'home_town'] = people[j]['home_town']
                    if (people[j].get('education') != None):
                        dataset.loc[i + j, 'education'] = people[j]['education']
                    if (people[j].get('schools') != None):
                        dataset.loc[i + j, 'schools'] = len(people[j]['schools'])
                    if (people[j].get('status') != None):
                        dataset.loc[i + j, 'status'] = people[j]['status']
                    if (people[j].get('followers_count') != None):
                        dataset.loc[i + j, 'followers_count'] = people[j]['followers_count']
                    if (people[j].get('common_count') != None):
                        dataset.loc[i + j, 'common_count'] = people[j]['common_count']
                    if (people[j].get('occupation') != None):
                        dataset.loc[i + j, 'occupation'] = people[j]['occupation']['type']
                    if (people[j].get('nickname') != None):
                        dataset.loc[i + j, 'nickname'] = people[j]['nickname']
                    if (people[j].get('relatives') != None):
                        if (len(people[j]['relatives'])) > 0:
                            dataset.loc[i + j, 'relatives'] = len(people[j]['relatives'])
                    if (people[j].get('relation') != None):
                        dataset.loc[i + j, 'relation'] = people[j]['relation']
                    if (people[j].get('personal') != None):
                        if (people[j]['personal'].get('langs') != None):
                            dataset.loc[i + j, 'langs'] = len(people[j]['personal']['langs'])
                        if (people[j]['personal'].get('people_main') != None):
                            dataset.loc[i + j, 'people_main'] = people[j]['personal']['people_main']
                        if (people[j]['personal'].get('life_main') != None):
                            dataset.loc[i + j, 'life_main'] = people[j]['personal']['life_main']
                        if (people[j]['personal'].get('smoking') != None):
                            dataset.loc[i + j, 'smoking'] = people[j]['personal']['smoking']
                        if (people[j]['personal'].get('alcohol') != None):
                            dataset.loc[i + j, 'alcohol'] = people[j]['personal']['alcohol']
                        if (people[j]['personal'].get('religion_id') != None):
                            dataset.loc[i + j, 'religion_id'] = people[j]['personal']['religion_id']

                    if (people[j].get('connections') != None):
                        dataset.iloc[i + j, 'connections'] = people[j]['connections']
                    if (people[j].get('exports') != None):
                        dataset.loc[i + j, 'exports'] = people[j]['exports']
                    if (people[j].get('activities') != None):
                        dataset.loc[i + j, 'activities'] = people[j]['activities']
                    if (people[j].get('interests') != None):
                        dataset.loc[i + j, 'interests'] = people[j]['interests']
                    if (people[j].get('music') != None):
                        dataset.loc[i + j, 'music'] = people[j]['music']
                    if (people[j].get('movies') != None):
                        dataset.loc[i + j, 'movies'] = people[j]['movies']
                    if (people[j].get('tv') != None):
                        dataset.loc[i + j, 'tv'] = people[j]['tv']
                    if (people[j].get('books') != None):
                        dataset.loc[i + j, 'books'] = people[j]['books']
                    if (people[j].get('games') != None):
                        dataset.loc[i + j, 'games'] = people[j]['games']
                    if (people[j].get('about') != None):
                        dataset.loc[i + j, 'about'] = people[j]['about']
                    if (people[j].get('quotes') != None):
                        dataset.loc[i + j, 'quotes'] = people[j]['quotes']
                    if (people[j].get('can_see_audio') != None):
                        dataset.loc[i + j, 'can_see_audio'] = people[j]['can_see_audio']
    print('saving vk db time: {}'.format(datetime.now()))
    # dataset.to_excel('vk_database.xlsx')
    print('saved at: {}'.format(datetime.now()))
    i += 998
    if (i > len(dataset)):
        break



engine = create_engine('')
dataset.to_sql('table_name', engine)
#Стартовый обработчик. Подключает TOKEN VK API к приложению #токен обрабатывается на ошибки в этом методе # подключение VK API к системе #не работает... # сhange i to last index in database #     print('i: {}'.format(i)) #         print('j: {}'.format(j)) # dataset.to_excel('vk_database.xlsx')