![]() ![]() It aids in effectively gathering and summarising unique values and is especially helpful when working with large datasets.ġ.It enables you to obtain a count of unique occurrences while eliminating duplicates, giving you important information about the cardinality of the data.A SQL function called COUNT DISTINCT determines how many values in a table column are distinct and do not repeat.Hadoop, Data Science, Statistics & others Using both functions togetherly can prove helpful in many situations in real-time case scenarios, such as retrieving the count of the grouped values with distinct records. We can use the functions count and distinct togetherly to find the number of records with different values from the resultset. We can use distinct SQL functions to retrieve the unique values from the result set of the specific query statement’s output. Further, there can be redundancy which means repetition of the same values for the specific result set. Using the count function, we can find the number of records for values of the resultset of the particular query in SQL. Unlike the regular COUNT function, which counts the total number of rows, COUNT DISTINCT focuses on counting the number of distinct (unique) values within a particular column. This is where the COUNT DISTINCT function comes into play. When working with data, knowing how many total records exist and how many unique values are present in a specific column is essential. The COUNT DISTINCT function is a valuable component of SQL’s aggregation capabilities. One of the fundamental operations in SQL is data aggregation, which involves summarizing and analyzing data to gain insights. For example, change the directory to your database location and enter sqlite3 eco.SQL, standing for Structured Query Language, wields significant prowess as a versatile instrument in the realm of database management and querying. connect( './eco.db') cur = con.cursor() # Queries cur.execute( "SELECT ROUND(weight/1000,2) FROM survey LIMIT 2 ") # change weight to kg print(cur.fetchall()) # cur.execute( """ SELECT ROUND(weight/1000,2) FROM survey WHERE (year > 2000) AND (species_id IN ('DM','DS','DO')) AND (weight > 1) LIMIT 2 """) print(cur.fetchall()) # cur.execute( """ SELECT species_id, sex, count(*) FROM survey WHERE species_id IN ('DS','DO') GROUP BY sex, species_id ORDER BY count(*) DESC """) print(cur.fetchall()) # cur.execute( """ SELECT species_id, sex, count(*) FROM survey GROUP BY sex, species_id HAVING species_id IN ('DS','DO') ORDER BY count(*) DESC """) print(cur.fetchall()) # cur.execute( """ SELECT species_id, sex, count(*) FROM survey WHERE species_id LIKE 'D%' GROUP BY sex, species_id ORDER BY count(*) DESC """) print(cur.fetchall()) # cur.execute( """ SELECT * FROM survey JOIN species ON species.species_id = survey.species_id WHERE weight '||Īlso, we can use SQLite3 directly from command line for getting queries. Import sqlite3 # Connect and read the db con = sqlite3. The following are more examples of different SQL queries in Python: ![]() connect( './eco.db') cur = con.cursor() # SQL query cur.execute( "SELECT name FROM PRAGMA_TABLE_INFO('survey') ") # select fields' name # Store output out = cur.fetchall() name = for x in out] print(name) # Exe query, store output and close the connection cur.execute( 'SELECT * FROM survey LIMIT 2 ') # select first two rows out = cur.fetchall() con.close() print(out) # Output as a list of dictionaries d = for i in out: d.append( dict( zip(name,i))) print(d) # Now, we can use execute command for running SQL commands in Python. Let’s use the above function to convert “survey.csv” and “species.csv” to tables in “eco.db”. split( ',')) for i in range( len(ro))] zip_ = list( zip(hd, list(fields_type))) hd_fl = header_field = ','.join(hd_fl) header = ','.join(hd) cur.execute( "CREATE TABLE IF NOT EXISTS %s ( %s ) " % (table_name,header_field)) cur.executemany( "INSERT INTO %s ( %s ) VALUES ( %s ) " % (table_name,header,( '?,' * len(hd))), db) con.commit() con.close() if _name_ = '_main_': # Example fields_type_1 = + * 6 + * 2 fields_type_2 = + * 3 csv_sql( './surveys.csv', 'survey', 'eco.db',fields_type_1) csv_sql( './species.csv', 'species', 'eco.db',fields_type_2) Queries connect(database_name) cur = con.cursor() # Drop the current table before re-create: cur.execute( "DROP TABLE IF EXISTS %s " % table_name) with open(file_dir, 'r') as fl: hd = fl.readline().split( ',') ro = fl.readlines() db =. #!/usr/bin/python3 import sqlite3 def csv_sql(file_dir,table_name,database_name,fields_type): con = sqlite3. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |