SQLite database and Python Query for Email subscriber database - SQLite3
Budget: $10 – $30 USD
I am looking for someone that can make a SQLite database the requirements are as follows:
I would like the script to be broken into 3 smaller scripts. All scripts, output database, CSV/TXT files will all be in the same path.
Script part 1 - Building the database
1, Database name 'Main_Database' (there will only be 1 db)
2, Table name 'user_data' (there will only be 1 table in the database)
3, The table will have 1 column 'Email_address'.
4, Must Create Unique index on 'user_data' (Email_address) [The DB will hold a lot of data so want searching to be as fast as possible]
Script part 2 - Input data to the database
I want to be able to add data periodically to the database from .txt and .csv files. The data will always be in this format. EmailAddress:SubscriberType. Only insert unique values to the table and only insert text in to the column before the first instance of ":". The unique value is based upon the EmailAddress.
5, Insert values of this sheet into the database built in 'Script 1' ensuring that there are no duplicates added based on the EmailAddress.
6, The files will be either CSV or TXT, Email address and Subscriber type will be separated by a :
7, I only need to upload 1 file at a time. I want it to update the database and not create a new one each time. The files are large and it should insert 100,000 at a time. if(count % 10000 == 0):
Script part 3 -
Script to query the database. I would like to be able to query the database in 3 ways. I only wish to query EmailAddress. There is no need to query SubscriberType.
1st method - Exact match of email address. Only display the results that are the exact match of the email address
2nd method - like. Partial match based on 1 word. For example if there are 4 email addresses in the database as below and I query 'ho' I want it to return 2 results. 'hot_chocolate@gmai' and 'hot_toffee@outl'
hot_chocolate@gmai
green_wren_spotter@hotm
Hamptonlife@yaho
hot_toffee_chocolate@outl
3rd method - basic boolean search using AND. Partial match based on 2 words appearing anywhere in the Email_address. For example if there are 4 email addresses in the database as above and I query 'hot' and 'choc' I want it to return 1 result(s). 'hot_chocolate@gmai'.
These results will display in the terminal window and say Press * Key to export results to CSV/TXT file. It will then generate a unique text file and save in the folder.
Other requirements:
- Most importantly this db will hold a vast amount of data. It must therefore be able to upload to the database quickly (Less than 3 minutes per 1,000,000) and query the data quickly (Less than a few seconds per query)
- I have 3 script that have been made but the upload and query becomes very slow when the database gets to 10,000,000. I can share the scripts with you hence the new project.
- This will run on Ubuntu 20.04
- Should be delivered in 3 .py files not all together in 1 file
- To be made in SQLite (If you have any better ideas of where to build this, I am open to suggestions).
I would like the script to be broken into 3 smaller scripts. All scripts, output database, CSV/TXT files will all be in the same path.
Script part 1 - Building the database
1, Database name 'Main_Database' (there will only be 1 db)
2, Table name 'user_data' (there will only be 1 table in the database)
3, The table will have 1 column 'Email_address'.
4, Must Create Unique index on 'user_data' (Email_address) [The DB will hold a lot of data so want searching to be as fast as possible]
Script part 2 - Input data to the database
I want to be able to add data periodically to the database from .txt and .csv files. The data will always be in this format. EmailAddress:SubscriberType. Only insert unique values to the table and only insert text in to the column before the first instance of ":". The unique value is based upon the EmailAddress.
5, Insert values of this sheet into the database built in 'Script 1' ensuring that there are no duplicates added based on the EmailAddress.
6, The files will be either CSV or TXT, Email address and Subscriber type will be separated by a :
7, I only need to upload 1 file at a time. I want it to update the database and not create a new one each time. The files are large and it should insert 100,000 at a time. if(count % 10000 == 0):
Script part 3 -
Script to query the database. I would like to be able to query the database in 3 ways. I only wish to query EmailAddress. There is no need to query SubscriberType.
1st method - Exact match of email address. Only display the results that are the exact match of the email address
2nd method - like. Partial match based on 1 word. For example if there are 4 email addresses in the database as below and I query 'ho' I want it to return 2 results. 'hot_chocolate@gmai' and 'hot_toffee@outl'
hot_chocolate@gmai
green_wren_spotter@hotm
Hamptonlife@yaho
hot_toffee_chocolate@outl
3rd method - basic boolean search using AND. Partial match based on 2 words appearing anywhere in the Email_address. For example if there are 4 email addresses in the database as above and I query 'hot' and 'choc' I want it to return 1 result(s). 'hot_chocolate@gmai'.
These results will display in the terminal window and say Press * Key to export results to CSV/TXT file. It will then generate a unique text file and save in the folder.
Other requirements:
- Most importantly this db will hold a vast amount of data. It must therefore be able to upload to the database quickly (Less than 3 minutes per 1,000,000) and query the data quickly (Less than a few seconds per query)
- I have 3 script that have been made but the upload and query becomes very slow when the database gets to 10,000,000. I can share the scripts with you hence the new project.
- This will run on Ubuntu 20.04
- Should be delivered in 3 .py files not all together in 1 file
- To be made in SQLite (If you have any better ideas of where to build this, I am open to suggestions).