Reading file and storing details in MySQL database table

Read the CSV file as downloaded from Analytics
Read all the file details from MySql table getting tracking page name
Create table for the month using previous month table structure.
Run the query to create table in SQLITE database.

import pandas as pd
# path and name of downloaded csv file from analytics.
# change this file name here
path_dest = "E:\\xampp\\htdocs\\plus2net\\z_analysis\\p-hits\\y23_02.csv"
table_name = "y23_02"  # New table to be created
##### No need to change below ####
df = pd.read_csv(path_dest, skiprows=6)  # remove first 6 lines
df.dropna(subset=["Page"], inplace=True)
my_delete = df[(df["Page"].str.len() < 12) & (df["Page"].str.len() > 3)].index
df.drop(my_delete, inplace=True, axis=0)
df = df[~df.Page.str.contains("\?")]
print(df.shape)  # shape of csv file
df.drop(labels="Page Value", axis=1, inplace=True)  # drop column
df.set_index("Page")
df.columns = ["Page", "p_view", "u_view", "avg", "entrance", "b_rate", "b_exit"]
####################################
from sqlalchemy import create_engine

engine = create_engine("mysql+mysqldb://id:pw@localhost/analytics")
file1='sqlite:///'+ 'E:\\xampp\\htdocs\\plus2net\\templates\\temp1\\analytics3.db' 
engine_sqlite = create_engine(file1)
########### create table ####
#str1 = "create table " + table_name + " like y23_02"
str1= "CREATE TABLE " + table_name + " AS SELECT * FROM y22_10 WHERE 0 "
#engine_sqlite.execute(str1)
###########
print(df.count())
sql = "SELECT * FROM plus2_search_file"
my_data2 = pd.read_sql(sql, engine) # updated to sqlite engine 
my_data2["url"] = "/" + my_data2["dir"] + my_data2["file"]
my_data3 = pd.merge(df, my_data2, how="left", left_on=["Page"], right_on=["url"])
### Creating new table emp or appending existing table
my_data3.to_sql(
    con=engine_sqlite, name=table_name, if_exists="append"
)  # update table name here
## SQL to update the table if Pandas is not used in above code#
# Update `plus2_search_file` a , y22_10 b  set b.track =a.track where  concat('/',a.dir,a.file) = b.Page
'''
CREATE TABLE `y22_10` (
  `index` bigint(20) DEFAULT NULL,
  `Page` text DEFAULT NULL,
  `p_view` text DEFAULT NULL,
  `u_view` text DEFAULT NULL,
  `avg` text DEFAULT NULL,
  `entrance` text DEFAULT NULL,
  `b_rate` text DEFAULT NULL,
  `b_exit` text DEFAULT NULL,
  `file_id` double DEFAULT NULL,
  `dir` text DEFAULT NULL,
  `file` text DEFAULT NULL,
  `title` text DEFAULT NULL,
  `des` text DEFAULT NULL,
  `keywords` text DEFAULT NULL,
  `top_link` text DEFAULT NULL,
  `h1` text DEFAULT NULL,
  `body` text DEFAULT NULL,
  `link_page` text DEFAULT NULL,
  `track` text DEFAULT NULL,
  `size` double DEFAULT NULL,
  `dtm` date DEFAULT NULL,
  `rating` text DEFAULT NULL,
  `sch` double DEFAULT NULL,
  `url` text DEFAULT NULL
) 
'''

Creting plus2_search_file in MySql

Create list of directories to be read.
Loop through all list of directories and create path.
Inside the loop read file modified data, file size.
Inside the file read the title, file description , keyword and track ( Unique identification of the file )
Insert all these details to the MySQL table
Display error message if any

Reading file details of the directories and then storing in MySQL database

import re,os
from datetime import datetime
#path1='E:\\xampp\\htdocs\\plus2net\\python\\string-capitalize.php'
from sqlalchemy import create_engine
from sqlalchemy.exc import SQLAlchemyError
# Connect to database using your user id and password
my_conn = create_engine("mysql+mysqldb://id:pw@localhost/analytics")
r_set = my_conn.execute('TRUNCATE plus2_search_file')  # remove all records
path1='E:\\xampp\\htdocs\\plus2net\\'  # root path of all my directories
# List of directories to read and get required details.

l1=["jquery/","php_tutorial/","sql_tutorial/","javascript_tutorial/","html_tutorial/","articles/",
    "c-tutorial/","jquery/msg-demo/","python/","java_tutorial/","angular/"]
i=1 # counter to list Number of errors
#l1=["angular/"] # to reduce number of directories for testing
for dir in l1:
    path2=path1+dir    # get the full path including the current dir name
    #print(path2)
   
    for f in os.listdir(path2): # Loop through all files of the dir
        if f.endswith(".php"):  # if this is a .php file only
            path=path2+f  # create the full path including the file name
            t_stamp=os.path.getmtime(path) # for file modificaton time
            dt_mod = datetime.fromtimestamp(t_stamp)
            dtm = datetime.strftime(dt_mod, '%Y-%m-%d') # Change format
            size=os.path.getsize(path) # file size
            #print(path)
            fob=open(path,'r',errors="ignore") # Open in read mode
            data=fob.read() # collect data
            fob.close()   # close the file pointer
            track = re.search('tracking_page_name="(.*)";', data)
            title = re.search('<title>(.*)</title>', data)
            des = re.search('<META NAME=\\\\"DESCRIPTION\\\\" CONTENT=\\\\"(.*)\\\\">', data)
            kw = re.search('<META NAME=\\\\"KEYWORDS\\\\" CONTENT=\\\\"(.*)\\\\">', data)
            my_flag=True
            if title: # not None
                title=title.group(1)
                title=title.replace("'", "\\'") # escape single quotes
            else:
                title,my_flag='',False  # exclude adding if title is blank
               
            if des:
                des=des.group(1)
                des=des.replace("'", "\\'")
            else:
                des=''
            if kw:
                kw=kw.group(1)
                kw=kw.replace("'", "\\'")
            else:
                kw=''
            if track:
                track=track.group(1)
            else:
                track=''
            #print(track.group(1))
            if size>100 and my_flag: # Create Query to store in Database
                sql_str="INSERT INTO plus2_search_file ( dir, file, title, des, keywords, top_link, h1, body, link_page, track, size, dtm, rating, sch) VALUES ";
                sql_str = sql_str + "('" + dir +"','"+ f+"','" + title+"','"+ des+"','"+ kw+ "', NULL, NULL, NULL, NULL,'"+ track+ "','"+ str(size) +"','"+ dtm+"', NULL, 0)";
                #print(sql_str)
                try:
                    r_set = my_conn.execute(sql_str)  # execute query
                except SQLAlchemyError as e:
                    error=str(e.__dict__['orig'])
                    print(error,'\n\n')
                    print(sql_str,'\n\n')
                    i=i+1
    else:
        print("**"+path)
print(i)

Using SQLite

import re,os
from datetime import datetime
#path1='E:\\xampp\\htdocs\\plus2net\\python\\string-capitalize.php'
from sqlalchemy import create_engine
from sqlalchemy.exc import SQLAlchemyError
# Connect to database using your user id and password
my_conn = create_engine("mysql+mysqldb://root:test@localhost/analytics")
file1='sqlite:///'+ 'E:\\xampp\\htdocs\\plus2net\\templates\\temp1\\plus2_search_file.db' 
my_conn = create_engine(file1)
#r_set = my_conn.execute('TRUNCATE plus2_search_file')  # remove all records
r_set = my_conn.execute('DELETE FROM plus2_search_file')  # remove all records
path1='E:\\xampp\\htdocs\\plus2net\\'  # root path of all my directories
# List of directories to read and get required details.

Creating Sitemap xml file from a directory
Reading Sitemap xml file and fetching details to store

Moving the file position by seek() We can read the file position by tell()

File Append File Write

File handling methods list Python- Tutorials

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author Watch on YouTube

Connect:

Subscribe to our YouTube Channel here

Reading directory and storing file details in Database

Creting plus2_search_file in MySql

Using SQLite

Subhendu Mohapatra