Update magento customer infor using SQL query

gistfile1.txt

# -*- coding: utf-8 -*-
import random
from random import randint, choice
from mysql.connector import connect
list_city = []




# with open('/home/ubuntu/venv/world-cities_csv.csv', 'r') as f:
# 	lst = f.read().split('\n')
# 	for i,country in enumerate(lst):
# 		cl = country.split(',')
# 		if len(cl) > 0:
# 			list_city.append(cl[0])



with open('/home/ubuntu/venv/list_cus.txt', 'r') as f:
	LIST_CUS = f.read().split('\n')

# with open('/home/mars/Downloads/list_cus.txt', 'r') as f:
# 	LIST_CUS = f.read().split('\n')


mail_1 = ['elvina', 'celia', 'isreal', 'stephanie', 'jung', 'lani', 'graciela', 'amberly', 'kaitlin', 'jannet', 'bridget', 'consuelo', 'collette', 'kelli', 'jamal', 'albertina', 'alberto', 'marco', 'jc', 'micki', 'merna', 'jolene', 'maxwell', 'birdie', 'gaynelle', 'jonnie', 'eboni', 'irina', 'thomasine', 'verna', 'laureen', 'maryam', 'chrissy', 'gabriele', 'danyel', 'ulrike', 'oliver', 'benito', 'joesph', 'salina', 'vincenzo', 'christinia', 'edmundo', 'nannette', 'antione', 'margurite', 'tomasa', 'will', 'lance', 'nyla', 'debra', 'terrence', 'larita', 'gisele', 'garland', 'christene', 'arica', 'horace', 'vasiliki', 'leonora', 'elizabeth', 'gabriele', 'julietta', 'edwin', 'latasha', 'lorene', 'susy', 'laurinda', 'vernie', 'francine', 'oren', 'sylvie', 'genevie', 'guy', 'miss', 'carolann', 'maribeth', 'lynne', 'antionette', 'aurea', 'jackqueline', 'juliana', 'tyler', 'margarite', 'shanelle', 'anabel', 'carina', 'bryon', 'asha', 'charise', 'brittaney', 'noelle', 'wenona', 'kelsie', 'normand', 'rubi', 'willena', 'ehtel', 'fidela', 'von']
mail_2 = ['lippert', 'rotenberry', 'wigger', 'roher', 'stahlman', 'limberg', 'cahoon', 'bold', 'strine', 'eddington', 'gillum', 'szabo', 'mojarro', 'cusson', 'weiskopf', 'liechty', 'garett', 'tennant', 'amy', 'rasnake', 'guptill', 'schartz', 'shockley', 'choe', 'meng', 'harman', 'redmon', 'prum', 'garn', 'brecht', 'ang', 'christiano', 'benz', 'salisbury', 'terhune', 'bickham', 'philippi', 'spangler', 'loso', 'dipasquale', 'caroll', 'ridley', 'harber', 'litwin', 'franceschini', 'moxley', 'wright', 'pouliot', 'dickenson', 'ciulla', 'hesler', 'duane', 'horne', 'hollow', 'brownlee', 'treese', 'dano', 'gotcher', 'walley', 'conigliaro', 'pena', 'brunner', 'montaluo', 'bebout', 'casterline', 'barsh', 'rappa', 'braga', 'frantz', 'cocco', 'alcorn', 'marini', 'waiters', 'keever', 'mohammed', 'bellantoni', 'mendivil', 'kist', 'kirkham', 'johnson', 'dymond', 'mansfield', 'henriksen', 'saleh', 'keaney', 'crites', 'justice', 'willson', 'shumway', 'moudy', 'puma', 'lebeau', 'gabel', 'quijas', 'marrin', 'krahn', 'fedor', 'number', 'lubbers', 'mcquaig']
domain = ['gmail.com', 'hotmail.com', 'yahoo.com', 'nasa.com', 'tesla.com']



unique_mail = []
MAILS = []
cmail = 0
while cmail < 10000:
	new_mail = '%s%s@%s' % (random.choice(LIST_CUS).lower(), random.choice(LIST_CUS).lower(), random.choice(domain))
	if new_mail not in MAILS:
		MAILS.append(new_mail)
		cmail += 1

list_country = ['Andorra', 'United Arab Emirates', 'Afghanistan', 'Antigua and Barbuda', 'Anguilla', 'Albania', 'Armenia', 'Angola', 'Antarctica', 'Argentina', 'American Samoa', 'Austria', 'Australia', 'Aruba', 'Aland Islands', 'Azerbaijan', 'Bosnia and Herzegovina', 'Barbados', 'Bangladesh', 'Belgium', 'Burkina Faso', 'Bulgaria', 'Bahrain', 'Burundi', 'Benin', 'Saint Barthelemy', 'Bermuda', 'Brunei', 'Bolivia', 'Bonaire, Saint Eustatius and Saba ', 'Brazil', 'Bahamas', 'Bhutan', 'Bouvet Island', 'Botswana', 'Belarus', 'Belize', 'Canada', 'Cocos Islands', 'Democratic Republic of the Congo', 'Central African Republic', 'Republic of the Congo', 'Switzerland', 'Ivory Coast', 'Cook Islands', 'Chile', 'Cameroon', 'China', 'Colombia', 'Costa Rica', 'Cuba', 'Cape Verde', 'Curacao', 'Christmas Island', 'Cyprus', 'Czechia', 'Germany', 'Djibouti', 'Denmark', 'Dominica', 'Dominican Republic', 'Algeria', 'Ecuador', 'Estonia', 'Egypt', 'Western Sahara', 'Eritrea', 'Spain', 'Ethiopia', 'Finland', 'Fiji', 'Falkland Islands', 'Micronesia', 'Faroe Islands', 'France', 'Gabon', 'United Kingdom', 'Grenada', 'Georgia', 'French Guiana', 'Guernsey', 'Ghana', 'Gibraltar', 'Greenland', 'Gambia', 'Guinea', 'Guadeloupe', 'Equatorial Guinea', 'Greece', 'South Georgia and the South Sandwich Islands', 'Guatemala', 'Guam', 'Guinea-Bissau', 'Guyana', 'Hong Kong', 'Heard Island and McDonald Islands', 'Honduras', 'Croatia', 'Haiti', 'Hungary', 'Indonesia', 'Ireland', 'Israel', 'Isle of Man', 'India', 'British Indian Ocean Territory', 'Iraq', 'Iran', 'Iceland', 'Italy', 'Jersey', 'Jamaica', 'Jordan', 'Japan', 'Kenya', 'Kyrgyzstan', 'Cambodia', 'Kiribati', 'Comoros', 'Saint Kitts and Nevis', 'North Korea', 'South Korea', 'Kosovo', 'Kuwait', 'Cayman Islands', 'Kazakhstan', 'Laos', 'Lebanon', 'Saint Lucia', 'Liechtenstein', 'Sri Lanka', 'Liberia', 'Lesotho', 'Lithuania', 'Luxembourg', 'Latvia', 'Libya', 'Morocco', 'Monaco', 'Moldova', 'Montenegro', 'Saint Martin', 'Madagascar', 'Marshall Islands', 'Macedonia', 'Mali', 'Myanmar', 'Mongolia', 'Macao', 'Northern Mariana Islands', 'Martinique', 'Mauritania', 'Montserrat', 'Malta', 'Mauritius', 'Maldives', 'Malawi', 'Mexico', 'Malaysia', 'Mozambique', 'Namibia', 'New Caledonia', 'Niger', 'Norfolk Island', 'Nigeria', 'Nicaragua', 'Netherlands', 'Norway', 'Nepal', 'Nauru', 'Niue', 'New Zealand', 'Oman', 'Panama', 'Peru', 'French Polynesia', 'Papua New Guinea', 'Philippines', 'Pakistan', 'Poland', 'Saint Pierre and Miquelon', 'Pitcairn', 'Puerto Rico', 'Palestinian Territory', 'Portugal', 'Palau', 'Paraguay', 'Qatar', 'Reunion', 'Romania', 'Serbia', 'Russia', 'Rwanda', 'Saudi Arabia', 'Solomon Islands', 'Seychelles', 'Sudan', 'South Sudan', 'Sweden', 'Singapore', 'Saint Helena', 'Slovenia', 'Svalbard and Jan Mayen', 'Slovakia', 'Sierra Leone', 'San Marino', 'Senegal', 'Somalia', 'Suriname', 'Sao Tome and Principe', 'El Salvador', 'Sint Maarten', 'Syria', 'Swaziland', 'Turks and Caicos Islands', 'Chad', 'French Southern Territories', 'Togo', 'Thailand', 'Tajikistan', 'Tokelau', 'Timor Leste', 'Turkmenistan', 'Tunisia', 'Tonga', 'Turkey', 'Trinidad and Tobago', 'Tuvalu', 'Taiwan', 'Tanzania', 'Ukraine', 'Uganda', 'United States Minor Outlying Islands', 'United States', 'Uruguay', 'Uzbekistan', 'Vatican', 'Saint Vincent and the Grenadines', 'Venezuela', 'British Virgin Islands', 'U.S. Virgin Islands', 'Vietnam', 'Vanuatu', 'Wallis and Futuna', 'Samoa', 'Yemen', 'Mayotte', 'South Africa', 'Zambia', 'Zimbabwe', 'Serbia and Montenegro', 'Netherlands Antilles']
list_capital = ['Andorra la Vella', 'Abu Dhabi', 'Kabul', "St. John's", 'The Valley', 'Tirana', 'Yerevan', 'Luanda', '', 'Buenos Aires', 'Pago Pago', 'Vienna', 'Canberra', 'Oranjestad', 'Mariehamn', 'Baku', 'Sarajevo', 'Bridgetown', 'Dhaka', 'Brussels', 'Ouagadougou', 'Sofia', 'Manama', 'Bujumbura', 'Porto-Novo', 'Gustavia', 'Hamilton', 'Bandar Seri Begawan', 'Sucre', '', 'Brasilia', 'Nassau', 'Thimphu', '', 'Gaborone', 'Minsk', 'Belmopan', 'Ottawa', 'West Island', 'Kinshasa', 'Bangui', 'Brazzaville', 'Bern', 'Yamoussoukro', 'Avarua', 'Santiago', 'Yaounde', 'Beijing', 'Bogota', 'San Jose', 'Havana', 'Praia', ' Willemstad', 'Flying Fish Cove', 'Nicosia', 'Prague', 'Berlin', 'Djibouti', 'Copenhagen', 'Roseau', 'Santo Domingo', 'Algiers', 'Quito', 'Tallinn', 'Cairo', 'El-Aaiun', 'Asmara', 'Madrid', 'Addis Ababa', 'Helsinki', 'Suva', 'Stanley', 'Palikir', 'Torshavn', 'Paris', 'Libreville', 'London', "St. George's", 'Tbilisi', 'Cayenne', 'St Peter Port', 'Accra', 'Gibraltar', 'Nuuk', 'Banjul', 'Conakry', 'Basse-Terre', 'Malabo', 'Athens', 'Grytviken', 'Guatemala City', 'Hagatna', 'Bissau', 'Georgetown', 'Hong Kong', '', 'Tegucigalpa', 'Zagreb', 'Port-au-Prince', 'Budapest', 'Jakarta', 'Dublin', 'Jerusalem', 'Douglas', 'New Delhi', 'Diego Garcia', 'Baghdad', 'Tehran', 'Reykjavik', 'Rome', 'Saint Helier', 'Kingston', 'Amman', 'Tokyo', 'Nairobi', 'Bishkek', 'Phnom Penh', 'Tarawa', 'Moroni', 'Basseterre', 'Pyongyang', 'Seoul', 'Pristina', 'Kuwait City', 'George Town', 'Astana', 'Vientiane', 'Beirut', 'Castries', 'Vaduz', 'Colombo', 'Monrovia', 'Maseru', 'Vilnius', 'Luxembourg', 'Riga', 'Tripoli', 'Rabat', 'Monaco', 'Chisinau', 'Podgorica', 'Marigot', 'Antananarivo', 'Majuro', 'Skopje', 'Bamako', 'Nay Pyi Taw', 'Ulan Bator', 'Macao', 'Saipan', 'Fort-de-France', 'Nouakchott', 'Plymouth', 'Valletta', 'Port Louis', 'Male', 'Lilongwe', 'Mexico City', 'Kuala Lumpur', 'Maputo', 'Windhoek', 'Noumea', 'Niamey', 'Kingston', 'Abuja', 'Managua', 'Amsterdam', 'Oslo', 'Kathmandu', 'Yaren', 'Alofi', 'Wellington', 'Muscat', 'Panama City', 'Lima', 'Papeete', 'Port Moresby', 'Manila', 'Islamabad', 'Warsaw', 'Saint-Pierre', 'Adamstown', 'San Juan', 'East Jerusalem', 'Lisbon', 'Melekeok', 'Asuncion', 'Doha', 'Saint-Denis', 'Bucharest', 'Belgrade', 'Moscow', 'Kigali', 'Riyadh', 'Honiara', 'Victoria', 'Khartoum', 'Juba', 'Stockholm', 'Singapore', 'Jamestown', 'Ljubljana', 'Longyearbyen', 'Bratislava', 'Freetown', 'San Marino', 'Dakar', 'Mogadishu', 'Paramaribo', 'Sao Tome', 'San Salvador', 'Philipsburg', 'Damascus', 'Mbabane', 'Cockburn Town', "N'Djamena", 'Port-aux-Francais', 'Lome', 'Bangkok', 'Dushanbe', '', 'Dili', 'Ashgabat', 'Tunis', "Nuku'alofa", 'Ankara', 'Port of Spain', 'Funafuti', 'Taipei', 'Dodoma', 'Kyiv', 'Kampala', '', 'Washington', 'Montevideo', 'Tashkent', 'Vatican City', 'Kingstown', 'Caracas', 'Road Town', 'Charlotte Amalie', 'Hanoi', 'Port Vila', 'Mata Utu', 'Apia', 'Sanaa', 'Mamoudzou', 'Pretoria', 'Lusaka', 'Harare', 'Belgrade', 'Willemstad']


LIST_PHONE = []
for i in range(70000):
	phone = '%s%s%s%s%s%s%s%s%s%s' % (
		randint(1,9),
		randint(1,9),
		randint(1,9),
		randint(1,9),
		randint(1,9),
		randint(1,9),
		randint(1,9),
		randint(1,9),
		randint(1,9),
		randint(1,9),
		)
	LIST_PHONE.append(phone)

LIST_ADDRESS = list_country + list_capital + list_city



con = connect(user='root', password='root', host='127.0.0.1',database='magento_backup_1')





def create_table_new_value(update_table,entity_type_id, field, attribute_id, list_val):
	# get list value_id
	get_value_ids = 'SELECT value_id FROM %s WHERE entity_type_id=%s AND attribute_id=%s;' % (update_table, entity_type_id, attribute_id)
	value_ids = []
	cursor = con.cursor()
	cursor.execute(get_value_ids)
	for (value_id) in cursor:
		value_ids.append(value_id[0])

	# create table query to saved data
	new_table_name = '{field}_{entity_type_id}_new_value'.format(field=field, entity_type_id=entity_type_id)
	drop_table_query = 'DROP TABLE {table_name};'.format(table_name=new_table_name)
	create_table_query = 'CREATE TABLE {table_name} (value_id int(11), value varchar(255));'.format(table_name=new_table_name)
	insert_query = 'INSERT INTO {table_name} (value_id, value) VALUES '.format(table_name=new_table_name)

	# insert data to new table
	new_values = []
	for value_id in value_ids:
		new_values.append("({value_id}, '{value}')".format(value_id=value_id,value=random.choice(list_val)))

	insert_query += ','.join(new_values)


	# update appropiate field with value_id
	update_query = 'UPDATE {update_table} t1, {new_table} t2 SET t1.value = t2.value WHERE t1.value_id = t2.value_id'.format(update_table=update_table, new_table=new_table_name)



	return {'drop_table_query': drop_table_query,'create_table_query': create_table_query, 'insert_query': insert_query, 'update_query': update_query}




def create_table_flowpassword_new_value(update_table, field, list_val):
	# get list value_id
	get_emails = 'SELECT flowpassword_id FROM %s;' % (update_table)
	emails = []
	cursor = con.cursor()
	cursor.execute(get_emails)
	for (email) in cursor:
		emails.append(email[0])

	# create table query to saved data
	new_table_name = '{field}_30_new_value'.format(field=field)
	drop_table_query = 'DROP TABLE {table_name};'.format(table_name=new_table_name)
	create_table_query = 'CREATE TABLE {table_name} (flowpassword_id int(11), email varchar(255));'.format(table_name=new_table_name)
	insert_query = 'INSERT INTO {table_name} (flowpassword_id, email) VALUES '.format(table_name=new_table_name)

	# insert data to new table
	new_values = []
	for index,entity_id in enumerate(emails):
		new_values.append("({flowpassword_id}, '{email}')".format(flowpassword_id=entity_id, email=list_val[index]))

	insert_query += ','.join(new_values)


	# update appropiate field with value_id
	update_query = 'UPDATE {update_table} t1, {new_table} t2 SET t1.email = t2.email WHERE t1.flowpassword_id = t2.flowpassword_id'.format(update_table=update_table, new_table=new_table_name)



	return {'drop_table_query': drop_table_query,'create_table_query': create_table_query, 'insert_query': insert_query, 'update_query': update_query}




def show_command(file_data_name, dict_val):
	with open(file_data_name, 'w') as f:
		for key in dict_val:
			f.write(dict_val[key])
			f.write('\n' * 5)


# show_command('good_data_1',create_table_new_value('customer_entity_varchar', 1, 'bidder_name', 633, LIST_CUS))
# show_command('good_data_2',create_table_new_value('customer_entity_varchar', 1, 'firstname', 5, LIST_CUS))
# show_command('good_data_3',create_table_new_value('customer_entity_varchar', 1, 'lastname', 7, LIST_CUS))
# show_command('good_data_4',create_table_new_value('customer_entity_varchar', 1, 'middlename', 6, LIST_CUS))
# show_command('good_data_5',create_table_new_value('customer_entity_varchar', 1, 'nickname', 511, LIST_CUS))
# show_command('good_data_6',create_table_new_value('customer_address_entity_varchar', 2, 'city', 24, LIST_ADDRESS))
# show_command('good_data_7',create_table_new_value('customer_address_entity_varchar', 2, 'company', 22, LIST_CUS))
# show_command('good_data_8',create_table_new_value('customer_address_entity_varchar', 2, 'firstname', 18, LIST_CUS))
# show_command('good_data_9',create_table_new_value('customer_address_entity_varchar', 2, 'lastname', 20, LIST_CUS))
# show_command('good_data_10',create_table_new_value('customer_address_entity_varchar', 2, 'middlename', 19, LIST_CUS))
# show_command('good_data_11',create_table_new_value('customer_address_entity_varchar', 2, 'region', 26, LIST_CUS))
# show_command('good_data_12',create_table_new_value('customer_address_entity_varchar', 2, 'street', 23, LIST_CUS))
# show_command('good_data_13',create_table_new_value('customer_address_entity_varchar', 2, 'telephone', 29, LIST_PHONE))
# show_command('good_data_14',create_table_email_new_value('customer_entity', 'email', MAILS))
show_command('good_data_14',create_table_flowpassword_new_value('customer_flowpassword', 'email', MAILS))


# print(len(LIST_PHONE))
# print(len(MAILS))
# print(len(LIST_ADDRESS))
# print(len(LIST_CUS))

# connect to mysql: https://stackoverflow.com/questions/25865270/how-to-install-python-mysqldb-module-using-pip