Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- #0.10612400000000001 sec
- from clickhouse_driver import Client
- import subprocess
- import time
- def temp(arr):
- res = 'CREATE TABLE IF NOT EXISTS ' + arr[0] + '\n(\n'
- for i in range(1, len(arr), 2):
- if i != len(arr) - 2:
- res += arr[i] + ' ' + arr[i + 1] + ',\n'
- else:
- res += arr[i] + ' ' + arr[i + 1] + '\n'
- res += ') ENGINE = MergeTree ORDER BY tuple()'
- return res
- tables = [
- ['actor',
- 'actor_id', 'Int32',
- 'first_name', 'String',
- 'last_name', 'String',
- 'last_update', 'DateTime64'
- ],
- ['actor_info',
- 'actor_id', 'Int32',
- 'first_name', 'String',
- 'last_name', 'String',
- 'film_info', 'String'
- ],
- ['customer_list',
- 'id', 'Int32',
- 'name', 'String',
- 'address', 'String',
- 'zipcode', 'String',
- 'phone', 'String',
- 'city', 'String',
- 'country', 'String',
- 'notes', 'String',
- 'sid', 'Int16'
- ],
- ['film_list',
- 'fid', 'Int32',
- 'title', 'String',
- 'description', 'String',
- 'category', 'String',
- 'price', 'Decimal(4, 2)',
- 'length', 'Int16',
- 'rating', 'String',
- 'actors', 'String'
- ],
- ['nicer_but_slower_film_list',
- 'fid', 'Int32',
- 'title', 'String',
- 'description', 'String',
- 'category', 'String',
- 'price', 'Decimal(4, 2)',
- 'length', 'Int16',
- 'rating', 'String',
- 'actors', 'String'
- ],
- ['sales_by_film_category',
- 'category', 'String',
- 'total_sales', 'Decimal(6, 2)'
- ],
- ['store',
- 'store_id', 'Int32',
- 'manager_staff_id', 'Int16',
- 'address_id', 'Int16',
- 'last_update', 'DateTime'
- ],
- ['sales_by_store',
- 'store', 'String',
- 'manager', 'String',
- 'total_sales', 'Decimal(7, 2)'
- ],
- ['staff_list',
- 'id', 'Int32',
- 'name', 'String',
- 'address', 'String',
- 'zipcode', 'String',
- 'phone', 'String',
- 'city', 'String',
- 'country', 'String',
- 'sid', 'Int16'
- ],
- ['address',
- 'address_id', 'Int32',
- 'address', 'String',
- 'address2', 'String',
- 'district', 'String',
- 'city_id', 'Int16',
- 'postal_code', 'String',
- 'phone', 'String',
- 'last_update', 'DateTime'
- ],
- ['category',
- 'category_id', 'Int32',
- 'name', 'String',
- 'last_update', 'DateTime'
- ],
- ['city',
- 'city_id', 'Int32',
- 'city', 'String',
- 'country_id', 'Int16',
- 'last_update', 'DateTime'
- ],
- ['country',
- 'country_id', 'Int32',
- 'country', 'String',
- 'last_update', 'DateTime'
- ],
- ['customer',
- 'customer_id', 'Int32',
- 'store_id', 'Int16',
- 'first_name', 'String',
- 'last_name', 'String',
- 'email', 'String',
- 'address_id', 'Int16',
- 'activebool', 'UInt8',
- 'create_date', 'Date',
- 'last_update', 'DateTime64',
- 'active', 'Int32'
- ],
- ['film_actor',
- 'actor_id', 'Int16',
- 'film_id', 'Int16',
- 'last_update', 'DateTime'
- ],
- ['film_category',
- 'film_id', 'Int16',
- 'category_id', 'Int16',
- 'last_update', 'DateTime'
- ],
- ['inventory',
- 'inventory_id', 'Int32',
- 'film_id', 'Int16',
- 'store_id', 'Int16',
- 'last_update', 'DateTime'
- ],
- ['language',
- 'language_id', 'Int32',
- 'name', 'String',
- 'last_update', 'DateTime'
- ],
- ['rental',
- 'rental_id', 'Int32',
- 'rental_date', 'DateTime',
- 'inventory_id', 'Int32',
- 'customer_id', 'Int16',
- 'return_date', 'DateTime',
- 'staff_id', 'Int16',
- 'last_update', 'DateTime'
- ],
- ['staff',
- 'staff_id', 'Int32',
- 'first_name', 'String',
- 'last_name', 'String',
- 'address_id', 'Int16',
- 'email', 'String',
- 'store_id', 'Int16',
- 'active', 'UInt8',
- 'username', 'String',
- 'password', 'String',
- 'last_update', 'DateTime64',
- 'picture', 'String'
- ],
- ['payment',
- 'payment_id', 'Int32',
- 'customer_id', 'Int16',
- 'staff_id', 'Int16',
- 'rental_id', 'Int32',
- 'amount', 'Decimal(5, 2)',
- 'payment_date', 'DateTime64'
- ],
- ['film',
- 'film_id', 'Int32',
- 'title', 'String',
- 'description', 'String',
- 'release_year', 'Int32',
- 'language_id', 'Int16',
- 'rental_duration', 'Int16',
- 'rental_rate', 'Decimal(4, 2)',
- 'length', 'Int16',
- 'replacement_cost', 'Decimal(5, 2)',
- 'rating', 'String',
- 'last_update', 'DateTime64',
- 'special_features', 'String',
- 'fulltext', 'String',
- ]
- ]
- client = Client('localhost')
- #client.execute('DROP DATABASE default')
- t = time.clock()
- for i in tables:
- client.execute(temp(i))
- for i in tables:
- if i[0] == 'customer' or i[0] == 'staff':
- f = open('/media/sf_im/' + i[0] + '.csv', 'r')
- temp = ''
- for now in f:
- temp += now.replace(',t,', ',1,').replace(',f,', ',0,').replace('\\', '')
- f.close()
- f = open('/media/sf_im/' + i[0] + '.csv', 'w')
- f.write(temp)
- f.close()
- elif i[0] == 'film':
- f = open('/media/sf_im/' + i[0] + '.csv', 'r')
- temp = ''
- for now in f:
- temp += now.replace('{', '').replace('}', '')
- f.close()
- f = open('/media/sf_im/' + i[0] + '.csv', 'w')
- temp = temp.replace('\'', ' ')
- f.write(temp)
- f.close()
- res = "clickhouse-client --query=\"INSERT INTO " + i[0] + " FORMAT CSV\" < /media/sf_im/" + i[0] + ".csv"
- # print(res)
- subprocess.run(res, shell=True)
- print(time.clock() - t)
- '''
- import psycopg2
- path = 'C:/im/'
- fields = open(path + 'field.txt', 'w')
- con = psycopg2.connect(
- database="dvd",
- user="postgres",
- password="postgres",
- host="127.0.0.1",
- port="5432"
- )
- cursor = con.cursor()
- cursor.execute('SELECT table_name FROM information_schema.tables \
- WHERE table_schema NOT IN (\'information_schema\',\'pg_catalog\');')
- tables = []
- for row in cursor:
- tables += [row[0]]
- for row in tables:
- new_file = open(path + row + '.csv', 'w')
- new_file.close()
- cursor.execute('SELECT column_name, data_type \
- FROM INFORMATION_SCHEMA.COLUMNS \
- WHERE table_name = \'' + row + '\';')
- temp = [row]
- for i in cursor:
- temp += [i[0].replace(' ', ''), i[1].replace(' ', '')]
- fields.write(' '.join(temp) + '\n')
- cursor.execute('COPY (SELECT * FROM "' + row + '") TO \'' + path + row + '.csv\' CSV;')
- fields.close()
- '''
Add Comment
Please, Sign In to add comment