I have a question a little hard to explain:
I need to do some database queries and the data is structured in the following way:
Alias table:
SELECT id, alias_type, alias_value, individual_id FROM 'snpdb_alias'
(84, 'ID_ASSOC', 'PMGS0001', 66),
(85, 'ID_ASSOC', 'PMGS0002', 67),
(86, 'ID_ASSOC', 'PMGS0003', 68),
(87, 'ID_ASSOC', 'PMGS0004', 69),
(88, 'ID_ASSOC', 'PMGS0005', 70),
(89, 'ID_ASSOC', 'PMGS0006', 71),
(90, 'ID_ASSOC', 'PMGS0007', 72),
(91, 'ID_ASSOC', 'PMGS0008', 73),
(92, 'ID_ASSOC', 'PMGS0009', 74),
(93, 'ID_ASSOC', 'PMGS0010', 75),
(94, 'ID_ASSOC', 'PMGS0011', 76),
(95, 'ID_ASSOC', 'PMGS0012', 77),
(96, 'ID_ASSOC', 'PMGS0013', 78),
(97, 'ID_ASSOC', 'PMGS0014', 79),
(98, 'ID_ASSOC', 'PMGS0015', 80),
(99, 'ID_ASSOC', 'PMGS0016', 81),
Individual Table:
SELECT id, sex, data_nascimento, population_id, codigo_pai_id, codigo_mae_id FROM individual
(66, 'M', '2016-10-11', 39, 84, 83),
(67, 'M', '2016-10-11', 39, 86, 85),
(68, 'M', '2016-10-10', 39, 88, 87),
(69, 'M', '2016-10-11', 39, 89, 83),
(70, 'M', '2016-10-10', 39, 89, 83),
(71, 'M', '2016-10-10', 39, 91, 90),
(72, 'M', '2016-10-07', 39, 93, 92),
(73, 'M', '2016-10-12', 39, 89, 94),
(74, 'M', '2016-10-16', 39, 89, 95),
(75, 'M', '2016-10-07', 39, 97, 96),
(76, 'M', '2016-10-13', 39, 99, 98),
(77, 'M', '2016-10-10', 39, 84, 83),
(78, 'M', '2016-10-09', 39, 100, 85),
(79, 'M', '2016-10-12', 39, 99, 101),
(80, 'M', '2016-10-09', 39, 88, 96),
(81, 'M', '2016-10-11', 39, 103, 102),
(82, 'M', '2016-10-08', 39, 104, 92),
Samples table:
SELECT id, plate, well, sentrix_position, call_rate, individual_id, manifest_id, idat_available FROM snpdb_sample'
(1, '142759', 'A1', 'abc', NULL, 66, 1, 0),
(2, '142759', 'B1', 'abc', NULL, 67, 1, 0),
(3, '142759', 'A11', 'abc', NULL, 68, 1, 0),
(4, '142759', 'B11', 'abc', NULL, 69, 1, 0),
(5, '142759', 'C11', 'abc', NULL, 70, 1, 0),
(6, '142759', 'D11', 'abc', NULL, 71, 1, 0),
(7, '142759', 'E11', 'abc', NULL, 72, 1, 0),
(8, '142759', 'F11', 'abc', NULL, 73, 1, 0),
(9, '142759', 'G11', 'abc', NULL, 74, 1, 0),
I need to fetch the ones from a CSV that is generated like this:
Index ID 1 PMGS0001 2 PMGS0002 3 PMGS0003 4 PMGS0004 5 PMGS0002 6 PMGS0005 7 PMGS0006 8 PMGS0007 9 PMGS0002 10 PMGS0009 11 PMGS0002 12 PMGS0003 13 PMGS0010 14 PMGS0011 15 PMGS0012
The Name, is a value of the alias table, needs to be returned, what sample does that item refer to.
The data is structured correctly through Alias, Individuals, Samples in Django, with foreign key and etc.
I need to query so that the answer looks like this:
(1,2,3,4,2,5,6,7,2,9,2,3,10,11,12)
I've tried using this:
import pandas as pd
sample_file = pd.read_table('/Sample_Map.csv', delimiter='\t')
Sample.objects.filter(Q(individual__alias__alias_value__icontains=list(sample_file.get(key='ID')))).all()
PS: The final values of the PMGS ending with the number I want is coincidence.