SQLite database and Python Query script 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 2 columns 'Email_address' , 'subscriber_type'
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, the unique value is based upon the EmailAddress. The 'SubscriberType' does not have to be unique
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 10,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 'hot' 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 'chocolate' I want it to return 2 results. 'hot_chocolate@gmai' and 'hot_toffee@outl'.
These results will display in the terminal window and say Press * Key to export results to CSV/TXT file. It will then generate a text file and save in the folder.
Other requirements:
- 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
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 2 columns 'Email_address' , 'subscriber_type'
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, the unique value is based upon the EmailAddress. The 'SubscriberType' does not have to be unique
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 10,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 'hot' 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 'chocolate' I want it to return 2 results. 'hot_chocolate@gmai' and 'hot_toffee@outl'.
These results will display in the terminal window and say Press * Key to export results to CSV/TXT file. It will then generate a text file and save in the folder.
Other requirements:
- 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