<jupyter_start><jupyter_text>INSAID Capstone project - group 1015 Installing and importing mysql.connector/python<jupyter_code>!pip install --user mysql-connector-python
import mysql.connector
import pandas as pd<jupyter_output><empty_output><jupyter_text>Creating db connection and storing in capdb<jupyter_code>capdbConfig = {
     'host' : 'cpanel.insaid.co',
     'user' : 'student',
      'password' : '<PASSWORD>', 
      'database' : 'Capstone1',
      #increasing connection_timeout to support fetching larger results - doesn't seem to have any impact though
      'connection_timeout' : 1000,
      'use_pure' : False
      #use_pure = False ensures the C extension is used for MySQL connection which is faster than pure python connection
      #false is the default value for a connection in newer MySQL versions(>8); here it is included as version is not known
 }
 capdb = mysql.connector.connect(**capdbConfig)<jupyter_output><empty_output><jupyter_text>Creating python cursor for executing mysql queries, checking tables, structures and count<jupyter_code>capdbCursor = capdb.cursor()
#below code intended to set max served packet size as 1 GB (to fetch large data) - but access denied in python client 
#capdbCursor.execute("SET GLOBAL max_allowed_packet=1024*1024*1024")
#closing and recreating cursor each time to ensure reduced load on next data fetch
capdbCursor.close()
capdbCursor = capdb.cursor()
capdbCursor.execute("show tables")
capdbTables = capdbCursor.fetchall()
for table in capdbTables:
    print(table)
capdbCursor.close()
capdbCursor = capdb.cursor()
capdbCursor.execute("describe events_data")
#fetchall fetches all records retrieved in cursor in a single go
#fetchall works fine when queried table is of small size
print(capdbCursor.fetchall())
capdbCursor.close()
capdbCursor = capdb.cursor()
query =   """select count(*)
            from events_data
            where state = 'delhi'"""
capdbCursor.execute(query)
#fetchone fetches single record retrieved in cursor
#fetchone is useful when only one record result is expected
#SQL connection is lost if fetchall is used for large size table
capdbRecords = capdbCursor.fetchone()
for record in capdbRecords:
    print(record)
capdbCursor.close()
capdbCursor = capdb.cursor()
#fetching single record to create pandas dataframe
query =   """select *
            from events_data
            where state = 'delhi'
            limit 1"""
capdbCursor.execute(query)
capdbRecords = capdbCursor.fetchall()
#to check data format is correct
eventsData
capdbCursor.close()
capdbCursor = capdb.cursor()
query =   """select *
            from events_data
            where state = 'delhi'"""
capdbCursor.execute(query)

while True:
  #fetching 100 records at a time and appending to previously created df 'eventsData' till all records fetched
  result = capdbCursor.fetchmany(100)
  if not result:
    sys.exit("Complete")
  else:
    dfTemp = pd.DataFrame(capdbRecords,
                          columns = ['event_id', 'device_id', 'timestamp', 'longitude', 'longitude', 'city', 'state'])
    eventsData.append(dfTemp)

#dropping the first record as it was fetched initially as well, hence duplicate
eventsData.drop([0],axis=0,inplace=True)

#check column and row count
eventsData.shape 
capdbCursor.close()
capdbCursor = capdb.cursor()
capdbCursor.execute("describe phone_brand_device_model")
print(capdbCursor.fetchall())<jupyter_output><empty_output><jupyter_text>Closing db connection<jupyter_code>capdb.close()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.08982376350198977, 'fraction_numerical': 0.009096077316657191, 'mean_word_length': 4.684975767366721, 'pattern_counts': {'":': 0, '<': 18, '<?xml version=': 0, '>': 19, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '369040', 'license': 'CC0-1.0', 'n_tokens_mistral': 1054, 'n_tokens_neox': 1009, 'n_words': 334, 'path': 'INSAID-DS-capstone/archive/INSAID DS capstone project - old.ipynb', 'repo_name': 'arunk-vnk-chn/bookish-giggle', 'size': '3515'}