Querying the Database using Python having List of Users and List of SQL Quries

import pandas as pd

import os

import cx_Oracle

df=pd.read_csv('D:\Oracle_Account\DATA\Python_Work\SQL_STMT_Read\SQL_List.csv')

df2=pd.DataFrame()

dfusers=pd.read_csv('D:\Oracle_Account\DATA\Python_Work\SQL_STMT_Read\Rquired_Users.csv',dtype=str)

Userlist = dfusers.OurUsers.values.tolist()

Userlist

res=str(Userlist)[1:-1]

print(res)

type(res)

dsn_tns = cx_Oracle.makedsn('localhost', '1531', 'XE')

conn=cx_Oracle.connect(user='abc', password='def', dsn=dsn_tns)

for i in range(0,3):

    query=df.loc[i,'sql_quries']

    query=query.format(res)

    print(query)

    df2= pd.read_sql(query, conn)

    df2.to_csv("D:\\Oracle_Account\\DATA\Python_Work\\SQL_STMT_Read\\"+str(i)+".csv", index = True, header=True) 

conn.close()

Reading List Element Under the Condition in Python

# Reading List Element Under the Condition in Two Ways

mylist=["ABC","DEF","DEFG","ABBC"]

for i in mylist:

    if i[0:2]=="AB":

        print(i)

    if i.startswith("DE"):

        print(i)

    

Oracle Database Concepts

#Oracle Database Knowledge and Concepts.  

 To check the password which is used during installation check the below file
[root@ol7-19 ~]# grep password  -A1 $ORACLE_HOME/install/response/db_install.rsp

SQL> show con_name
     or Run alter session set container=PDB;
      session altered
SQL>set markup csv on;
    
CON_NAME
------------------------------
CDB$ROOT
SQL> show PDBs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORCLPDB                        MOUNTED

SQL> alter pluggable database ORCLPDB  open;

Pluggable database altered

SQL> show PDBs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORCLPDB                        READ WRITE NO

------------------------------------------------------------------
To print the database services register in oracle listener.

[root@ol7-19 ~]#  lsnrctl status

[root@ol7-19 ~]#sqlplus sys/password@LOCALHOST:1521/pdb1 as sysdba

SQL>show con_name

To unlock a user:
-------------------
Alter User ${} Account unlock
Alter user hr  account unlock;

To set a password for a user
------------------------------
Alter user hr Identified by  password


Query HR Schema
-----------------------
SQL>set markup csv on;
SQL>select * from hr.employees;


#count employee and give raise
sqlplus hr/hrpass@192.168.1.15/ddb1<<EOF
select count(*) from employees;
update employee set slary=salary*2
commit
quit
EOF

To check fairewall status
# systemctl status firewalld

To stop fairewall status
# systemctl stop firewalld

To disable fairewall status
# systemctl disable firewalld


To configure a instance
----------------------
1) initialization parameter file is used to configure an instance.
2) location of the file is $ORACLE_HOME/dbs directory
3) We can change initalization parameter file with ALTER SYSTEM command
4) A SPFILE is created automatically when you create a CDB.
5) PFILE is a text parameter file contain parameter values in name/value pair
6) parameter are a) sessions b) transactions c) processes
7) SGA_TARGET  parameter (shared memory structure) data and control information
8) Alter System and Alter session command to change the parameter values in SQL plus
9) SQL>create pfile='/home/oracle/pfile_2021.conf' from spfile;
10) Query the view V$PARAMETER
11)select name,value,ises_modifiable,issys_modifiable,ispdb_modifiable from V$PARAMETER
    where name in (nls_date_format', sec_max_failed_login_attempts');
12) ALTER Session set NLS_DATE_FORMAT='mon dd yyyy';
13 ) ISSYS_MODIFIABLE column tell you when the system level parameter change is made by using
     ALTER System command
14) ISSYS_MODIFIABLE values are
     1) immediate
     2) Deffered
     3) False ( parameter with false are called static parameter)
15) Scope caluse
Alter system set SEC_MAX_Failed_login_Attempts=5  Scope=SPFILE;
16) We only change the parameter values with Alter System command wher the
    ISSYS_Modifiable is true in V$Parameter View.
17 SQL> show parameter pool; or select name,value from v$parameter where name like %pool%
18) View V$SPParameter contain information about server parameter file
19) View V$Parameter2 contain information about  parameter which has currently effect of the session
20) View V$System_Parameter2: contain information about the initialzation parameter which has currently in effect of instance

Starting up Oracle Instance
1) startup nomount
2) V$Database view  is not availabe in nomount mode of instance
SQL>select dbid,name,open_mode fro v$database; (give us database not mounted)
SQL>alter database open;
3) startup Mount
    a) associating old started instance with the new started instance
    b) Locatingf and opening all the control files specified in parameter file
    c) Reading the control files to obtain the name of the datafiles and redo log files
     
    Datafiles
      a) system01.dbf
      b) user01.dbf
      c) sysaux01.dbf
4) startup Opend
    a) Open the data file and redo log file as register in  control files
5) Alert Log file
   location can be determine using select * from V$diag_info; view
   location $Oracle_base/diag/rdbms/db_name/SID/trace/alert_<sid>.log
   create, alter, drop datbase and tablespaces  
------------------------------------------------
Start up the instance and compare the logs
------------------------------------------------
T1
oracle@oracle$ . oraenv
oracle@oracle$ echo $Oracle_base
/u01/app/oracle
oracle@oracle$ sqlplus / as sysdba
SQL>startup

T2
oracle@oracle$ tail $ORACLE_BASE/diag/rdbms/orcldb/orcl/trace/alter_orcl.log
 | tee $ORACLE_HOME/example_of_successful_start_up_operation.log
Working with PDBs.
----------------
SQL>show pdbs;
Opening and Closing PDB:
------------------------
Alter Pluggable database  PDB_Name Open;
Alter Pluggable database  PDB_Name Closed;
PDB have 3 Modes
1) R/W
2) ReadOnly
3) MIGRATE
4) MOUNTED (The PDB is shut down/closed)
When instance is started
1) inialization parameter file is read by process
2) path $ORACLE_HOME/dbs directory
3) need to set 5 or 30 or upto 300
Two type of parameter files
1) Server parameter file(SPFILE)
    An SPFILE is automatically created when you create a CDB.
    binary file
    can not be edited  manually
    need ALTER SYSTEM Command for changes in the file
2) Text parameter file (PFILE)
     a) db can not write to this file
     b) manual changes in file required
     c) db instance  restart is required for any change
     d) not downtime free
---------------------------
Diagnostic Repository
---------------------------
Trace file:- When a process detects an error it dumps or write the error information to trace file
and the data is store in ADR automatic diagnostic Reporsitory for traces,alter log, health reports of DBs
---------------------

ADRCI utility to

---------------------

oracle@oracle$ adrci
ADR base= /u01/app/oracle
adrci> show homes
adrci> set home diag/rdbms/orcldb/orcl
adrci> show alert  
ADR home= diag/rdbms/orcldb/orcl:
output the results to files: /tmp/alert_orcl_1.ado
adrci>select shortp_policy,longp_policy from ADR_CONTROL;
adrci>help to see the list of avaiable commands
adrci>show incident
adrci>show incident -mode detail -p " incident_id=123"  then report to oracle support

Note ADR has purging mechanism for  incidents and alert log / trace files
1) Time based retention (ShortP_Policy 720h=30day/Long_Policy 8760h=365days)
2) The Size-base retention for an ADR

Dictionary cache
--------------------------
Data Dictionary
---------------------------
 Is a collection of database tables,view,reference info about the DB and
its structures and its users
Note dynamic view started with V$view_name
1) SQL>Select * from Dictionary order by table_name;
2) Select con_id,dbid,name,open_mode,restricted,total_size from v$pdbs
3) Select owner,table_name from all_tables where owner='HR';
--List currently Login able to login
select username,account_status from dba_users where account_status='OPEN'
--List of table created in a Schema
select table_name,tablespace_name from user_tables;
-- List the name of sequences
select sequence_name,min_value,max_value,incremented by from all_sequences
where sequence_owner in ('MDSYS','XDB')
select * from Dictionary;
DESCRIBE dba_indexes

Note all data dictionary view is updated automatically for consistancy

CDBs_ and DBA_  view are seen by DBA
All_  and User_ view can be Query by everyone.
Quering Dynamic Performance view in Oracle
------------------------------------------
To check session from a specific machine on yesterday
--------------------------------------
select * from V$session where machine='ABC2' and logon_time>sysdate-1;

To  check file states of datafile or dba_data_files
---------------------------------------------------
select * from v$DataFiles

To Find out session and user who locked the database objects
----------------------------------------------------------
select sid,ctime from v$lock where block>0;
To find the SQL statment ( and their associate numberof executions) is the CPU time
is> than 100,00 microseconds
select sql_text,executions from v$sql where cup_time>100,000;
To see the list of backgroud processes
select * from V$BGPROCESS

Shutdown the Database Instance
-----------------------------
*uncommited changes rolled back
*Database buffer cache written to datafiles
*Resources released
*No instance recover on startup

shutdown
shutdown transactional
shutdown immediate

--------------------
Creating PDBs
Ways to find the location of PDBs
-----------------------------
$ grep dataLocation $ORACLE_HOME/install/response/db_install.rsp
oracale.install.db.config.starterdb.fileSystemStorage.dataLocation=/u01/app/oracle/product/19.0.0/db_1/mydbfiles

The parameter name DB_CREATE_FILE_DEST tell the location of oracleManaged datafile (OMF)
2) SQL>show parameter db_create_file_dest;
3rway to find the location of files is to query dba_data_files view
SQL>show con_name
Con_Name
CDB$ROOT
3) select  FILE_NAME from dba_data_files
/u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/undotbs01.dbf
/u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/users01.dbf

SQL>alter session set container=PDB1;
session altered
SQL> select FILE_NAME from dba_data_files
/u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/pdb1/undotbs01.dbf
/u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/pdb1/users01.dbf
[root@oracle] su oracle
[root@oracle]$ cd
[root@oracle root] . oraenv
ORACLE_SID= [oracle] ? orcl
[root@oracle root]$ export PS1='printf"\033[30m$ \033[47m"'

To create PDB from SEED we can use the following statement

CREATE PLUGGABLE DATABASE {} ADMIN USER {} IDENTIFIED BY {PWD} ROLES={dba}

SQL>CREATE PLUGGABLE DATABASE pdb2 ADMIN USER admin2 IDENTIFIED BY Oracle_2021 ROLES=(CONNECT) FILE_NAME_CONVERT=
('/u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/pdbseed', '/u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/pdb2')
[oracle@oracle]$sqlplus / as sysdba
[oracle@oracle]$ls /u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/pdbseed/
[oracle@oracle]$ls /u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/pdb2/
contains same files

SQL> select PDB_ID,PDB_NAME,STATUS,CON_ID,CREATION_TIME from CDB_PDBS;
SQL>show pdbs;
con_id, con_name, Open_Mode, Restricted
2       PDB_SEED   READ ONLY  NO
3       PDB1       READ WRITE NO
4       PDB2       MOUNTED
SQL>alter pluggable database PDB2 open;
pluggable database altered

Select Tablespace_name,status,con_id from CDB_tablespaces
4 new tablespaces have been created from our PDB
1) SYSTEM,
2) SYSAUX
3)  UNDOTBS
4   TEMP
Clonning the Database
1) Hot Clonning
The Source PDB can be open in READ WRITE mode and remain operational during the cloning process
Requires ARCHIVELOG and local undo mode
2) Cold Clonning
The source PDB should be opened in Read ONLY mode
exercise creating PDB3 from PDB1
1) Open PDB1 in READ ONLY mode
we need to  
2) Alter pluggable database pdb1 open Read only; --> open database in Readonly mod
3) CREATE PLUGGABLE DATABASE pdb3 from pdb1 create_file_Dest ='/u01/app/oracle/product/19.0.0/db_1/mydbfiles';
pluggable database created
select name from V$datafiles; give the file location for new PDB datab
select name,con_id, guid from v$pdbs
name con_id, guid
PDB1  3     BB8906
PDB1  5     C704
Cloning Remote PDB
----------------------
SQL create public database link  LINK_PDB1 conect ot  hr identified by "hr" using
'(Description=(Address=(Protocal=TCP)(HOST=localhost)(PORT=1521)(CONNECT_DATA=
 (SERVICE_NAME=pdb1)))';

CREATE PLUGGABLE DATABASE pdb4 from pdb1@link_pdb1
Listener is running on the remote side
$lsnr status
SQL>alter session set db_create_file_dest='/u01/app/oracle/product/19.0.0/db_1/mydbfiles';
session altered.
On the remote side Grant the permission to create pluggable database
SQL> Grant create pluggable database to hr;
SQL> alter session set container=CDB$ROOT;
SQL>create public database link  LINK_PDB1 conect ot  hr identified by "hr" using
'(Description=(Address=(Protocal=TCP)(HOST=localhost)(PORT=1521)(CONNECT_DATA=
 (SERVICE_NAME=pdb1)))';
SQL>alter pluggable database PDB4 open;
SQL>show pdbs;
SQL> drop public database link LINK_PDB1;
SQL>CREATE pluggable database pdb1_ref_cln1 from pdb1@link_pdb1 refresh mode every 60 Minutes
SQL>CREATE pluggable database pdb1_ref_cln1 from pdb1@link_pdb1 parallel 2 standbys=NONE Refresh Mode Manual;
SQL>Alter pluggable database pdb1_ref_cln2 referesh Mode NONE;
SQL> select * from pdb_plug_in_violations where name='pdb1_ref_cln2';
To unplug a PDB
1)Close PDB
2) Generate an XML metadata file (.xml ext)
alter pluggable database pdb4 unplug into '/home/oracle/pdb4.xml';
3) Drop PDBj
4) Copy files to Remote System if it is needed
To plugging a PDB using 3 Methods
1) NOCOPY Method
Create pluggable database pdbx using '/home/oracle/pdb4.xml' NOCOPY TEMPFILE REUSE;
2) COPY Method
Create pluggable database pdbx using '/home/oracle/pdb4.xml' COPY FILE_NAME_CONVERT('pdb4_dir','pdb5_dir');
3) AS CLONE or MOVE/COPY Method
Create pluggable database pdbx using '/home/oracle/pdb4.xml' MOVE;
select * from gV$session s  join gv$pdbs p
on  p.con_id=s.con_id  and p.inst_id=s.inst_id
Oracle Net
Listener
 listener is a process that runs onthe the server and listens for client connection requests
Database Service:
is logical representation of a database, which is the way that the base is presented to clients.
Oracle Net configuration files are
1) $ORACLE_HOME/network/admin/listener.ora
1) $ORACLE_HOME/network/admin/sqlnet.ora
[root@oracle] su oracle
[root@oracle]$ cd
[root@oracle root] . oraenv
ORACLE_SID= [oracle] ? orcl
[oracle@oracle]$sqlplus / as sysdba
[oracle@oracle]$ cat $ ORACLE_HOME/network/admin/listener.ora
LSNRCTL command is used to administer the listener.
[oracle@oracle]$lsnrctl start
[oracle@oracle] ps auxww | grep tnslsnr
Net Service Covers Firewall,Network Traffic Encryption and Listener Security.
Listener Registration Process
LREG
1)Name of the database services
2) Name of the database instance
3) Service handlers available for the instance
SQL> show parameter listener
Name, Type, Value,
forward_listener,string
listener_network,string
local_listener, string,   Listener_orcl
remote_listener string
SQL>cat $ORACLE_HOME/network/admin/tnsnames.ora
To check the list of all registered service
[oracle@oracle]$lsnrctl status
[oracle@oracle]$sqlplus hr/hr@pdb1_conn ( alias name defined in Listener)
------------------------------------------------------------------------
Data Access Control Covers
Privilege/Roles
Virtual private Database
User Creation
Creat User statement
Create user c##abc identified by pass123 Container=ALL default tablespace users Temporary Tablespace temp Account Unlock
User created in specified PDBs
alter session set container=pdb1;
show con_name
Create user c##abc identified by pass123
alter session set container=cdbs$Root;
show con_name
Select  p.pdb_id,

 p.pdb_name,
 u.username,
 u.common
from DBA_PDBs p , CDB_Users u
where  p.pdb_id=u.con_id
order by p.pdb_id;

GRANT create session to abcuser container=ALL
Creat ROLE  abcrole Container=ALL
------------------- create a role then assign grant priviliage to those Role as shown below---
Create Role HR_Clerk;
Grant create session to HR_Clerk
Grant select,update on Hr.employees To hr_clerk
Grant ALL on hr.jobs to Hr_clerk;
Grant hr_clerk to hr_mngr
Now Grant Roles to the users in the environment
tmp table space
Quota size on tablespace
select * from CDB_TEMP_FILES
Revoke select on hr.employee from hr_clerk
Drop Role VIP_SECURE;
Drop User TOM
Authetication of user by Operating System
--------------------------------------------
[oracle@oracle]$grep 'oracle' /etc/group
groupname:password:GroupID:user_list
oinstall :   x:      54321:           oracle

orapwORACLE_SID
[oracle@oracle]$ ls $ORACLE_HOME/dbs/orapworcl

SQL>show parameter REMOTE_LOGIN_PASSWORDFILE;
SQL>select username,sysdba from v$PWFILE_USERS;

profile of users
-------------------
vim $ORACLE_HOME/rdbms/admin/utlpwdmg.sql
utlpwdmg.sql script changes the Default profile with Alter Profile command
To create profile
----------------------
Function of profile:-
------------------------------
Using Profile we can control following
---------------------------------------

1)CPU Resources( limit on per session )
2)Network and Memory Resources(idle time of concurrent Sessions)
3)Disc IO Resources(limit on total number of reads)

A)Create user joey identified by oracle
B)Grant create session to joey;
C)Select username,user_id,profile from cdb_user where username='JOEY';
D)Alter profile abc limit idle_time 30;
SQL>create profile abc_profile limit
SQL>select * from CDB_Profiles
SQL>create profile abc  limit idle_time 60;
SQL>select * from dba_TS_QuoTas

DATA Confidentiality Covers following
Data Maksing
DBMS_CRYPTO
DB Storage Security
---------------------------
Transparent Data Encription
Data Encryption/ Oracle Secure Backup Encription in following ways
  1)Data files and OS files goes to OSB---Encripted-- TapeDevices
                   2)Redo log files
                   3)Archived Redo log files
                   4)Backup files
is  manages by key automatically.
Expdp Data is encrypted to dump file
Data is import/Exported using Parameter
                  1) ENCRYPTION
                  2) ENCRYPTION_MODE
                  3) ENCRYPTION_PASSWORD
                  4)  ENCRYPTION_ALGORITHM
STEPS
Create Software Keysotre
Create a wallet Directory
mkdir -p $ORACLE_BASE/admin/orcldb/wallet/tde
Set the key store Location & Type
WALLET_ROOT="/u01/app/oracle/admin/orcldb/wallet"

Create a password-base Software KeyStore
create keystore '/u01/app/oracle/admin/orcldb/wallet/tde'
--------------------------------------------------------
[oracle@oracle]$mkdir -p $ORACLE_BASE/admin/orcldb/wallet/tde
[oracle@oracle]$sqlplus / as sysdba
To Create a pfile from SPFILE
SQL> create pfile='home/oracle/backup/init786.ora' from spfile;
SQL>alter system set WALLET_ROOT="/u01/app/oracle/admin/orcldb/wallet" scope=spfile;
SQL>shutdown immediate
Database closed.
Database dismounted.
Oracle instance shut down.
SQL>startup
Total System Global Area
Fixed Size
Variable Size
Database Buffer
Reo Buffer
Database mounted.
Database Opened.
SQL>alter system set TDE_CONFIGURATION="KEYSTONE_CONFIGURATION=FILE" scope=spfile;
System altered.
SQL>shutdown immediate
Database closed.
Database dismounted.
SQL>startup
Total System Global Area
Fixed Size
Variable Size
Database Buffer
Reo Buffer
Database mounted.
Database Opened.
SQL>administer key management create keystore '/u01/app/oracle/admin/orcldb/wallet/tde'
keystore altered.
SQL> select status from v$encryption_wallet;
SQL>set markup csv on;
SQL>select WRL_TYPE,SRL_Parameter,status,con_id from v$encryption_wallet;
SQL>!ls -ltr /u01/app/oracle/admin/orcldb/wallet/tde
SQL>select Tablespace_name,encrypted from  dba_tablespaces;
Do regular copies of Keystore to some 2nd storages
-------------------------------------------------------------------------------------
TableSpace:-
A tablespace is an allocation of space in DB that can contail shema objects
When a table is created a segment is create to hold it data.
A samll file tablespace contains 1022 datafiles
each of which contains 4 million blocks
Collection of segments is called table space.
We can create 3 Type of table spaces
1) Permanent tablespaces
2) Temporary tablespaces
3) Undo tablespaces
SQL>select property_value from database_properties
    where property_name='Default_TBS_Type';
SmallFile.
Create tablespace orcl_cdb_users
default '/u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/orcl_users01.dbf' size 1M;
Tablespace created
SQL> alter session set contain=PDB1;
 session altered.
Create tablespace orcl_pdb_users
default '/u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/orcl_users01.dbf' size 1M;
create table t1( id int generated always as identity,
           column varchar2(32)
)tablespace orcl_pdb_users;
SQL>Begin
For counter IN 1..100 loop
    insert into t1(column1)
    values(sys_guid())
End loop;
End;
sql procedure successfully completed.
select tablespace_name,
 bytes /1024/1024 MB
 from
 dba_free_space
where tablespace_name='ORCL_PDB_USERS';
TableSpaceName,"MB"
ORCL_PDB_USERS, 1.2
We can increase the size of ORCL_PDB_USERS01.dbf file from 1MB to 10MB using following command
SQL>Alter database datafile '/u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/orcl_users01.dbf' resize 10m;
SQL>Create tablespace orcl_pdb_users
default '/u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/orcl_users01.dbf' size 1M
AUTOEXTEND ON NEXT 10M MAXSIZE 3G OR UNLIMITED
Create BigFILE tablespace orcl_pdb_users
default '/u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/orcl_users01.dbf' size 1G;
select tablespace_name,
 status, Bigfile
 from
 dba_free_space
Create tablespace orcl_pdb_encrypt
default '/u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/orcl_users01.dbf'
size 100M
Encryption using '3DES168' --,AES128,AES192
default storage (encrypt)
view related to tablespace are
1) cdb_tablespaces
2) dba_tablespaces
3) v$tablespace
data files information will in following view
Cdb_data_files
dba_data_files
V$datafiles
Temp file information
1)cdb_temp_files
2) dba_temp_files
3) v$tempfile
Alter TableSpace
Increase/Decrease File Size
Alter database datafile '/u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/orcl_users01.dbf' resize 10m;
Add Data Files to existing Table Space
Alter tablespace ORCL_PDB_USERS
 datafile '/u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/orcl_users02.dbf' resize 10m;
Alter tablespace ORCL_PDB_USERS
 datafile '/u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/orcl_users02.dbf' resize 10m;
 autoextend on
next 512K
Maxsize 2G
--To check the files associate to tablespace ORCL_PDB_USERS
select tablespace_name,file_name, bytes /1024 /1024 MB from dba_data_files

Change Satee of Table space
Alter tablespace ORCL_PDB_USERS Read Only;
Online/offline
Alter tablespace ORCL_PDB_USERS Read Write;
Alter tablespace ORCL_PDB_USERS offline Normal;
Move Data Files
Take TS offline
Move Files
 mkdir /u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/pdb1_extra
 mv    /u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/orcl_users02.dbf /u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/pdb1_extra/orcl_pdb1_users02.dbf
Alter TableSpace Rename DataFiles
Take TS onlin
----------------------------------------------------------------------------------
Oracle Managed Files
Parameters                    Description
DB_CREATE_FILE_DEST          (define the location of the default file system directory for data files&temporary files)
DB_CREATE_ONLINE_LOG_DEST    Define the location for redo log files and control file creation
DB_RECOVERY_FILE_DEST        Gives the default location for the fast recovery area
SQL>alter system set db_create_file_dest='/u01/app/oracle/product/19.0.0/db_1/mydbfiles' scope=Both
SQL CREATE tablespace orcl_CDB_OK
-------------------------------------
Enable ARCHIVELOG mode
---------------------------------------
SQL>archive log list;
Database log mode                 No Archive Mode
Automatic archival                Disabled
Archive destination               USE_DB_RECOVERY_FILE_DEST
Oldest Online log sequence         114
Current log sequence               116
SQL>shutdown immediate
SQL>startup mount
SQL>alter database archivelog;
SQL>alter database open;
SQL>select log_mode from v$database;
Log_mode=ARCHIVELoG
SQL>select flashback_on from v$database
Flashback_on
----
NO
SQL>alter database flashback on;
SQL>show con_name;
con_name
----------
pdb
Show user
User is HrS
SQL>create table us_states(id number(8) not null, name varchar2(50) not null);
    table created
SQL>Insert into us_states vlaues(101,'SANDI');
commit;
wait for some time ///Run FLASHBACK Query to get uno data by specifing SCN
SQL> select to_char(sysdate,'dd-mm-yyyy hh24:mi:ss') from dual
to_char(sysdate,'DD
--------------------
select * from hr.us_states as of timestamp to_timestamp('15-10-2021 06:15:00','dd-mm-yyyy hh24:mi:ss')
select to_char(sysdate,'dd-mm-yyyy hh24:mi:ss') ddate, dbms_flashback.get_system_change_number scn from dual;
DDATE                   SCN
----------              -----
14-10-2022 05:30:41      6664607

select scn_to timestamp(6664607) ddate, timestamp_to_scn(to_timestamp('15-10-2021 06:15:00','dd-mm-yyyy hh24:mi:ss')) scn  from dual;
UNDO management
-------------
alter session set container=pdb1;
show parameter undo_management;
select tablespace_name,content,status from dba_tablespaces where contents='UNDO'
to view all the datafile used by undoTBS1
SQL>select file_name from dba_data_files where tablespace_name=undotbs1' order by 1;
DBA_UNDO_EXTENTS
select * from V$UNDOSTAT
A-------------------------------------------------------------------------------------------------------------

(FAST Recovery Area) Backup_Recovery Parameter

db_recovery_file_dest_

db_recovery_file_dest_size (used to control the max of space to store backup file in side FRA)

db_recovery_file_dest-control (the location of FRA)

SQL>show parameter db_recovery_file_dest_size

name,type,value

db_recovery_file_dest,string,/u01/app/oracle/product/19.0.0/db_1/myfiles

db_recovery_file_dest_size,big integer,10G

To increase the size of FRA

----------------------------

SQL>Alter System set db_recovery_file_dest_size=15G SCOPE=BOTH 

SQL>show parameter db_recovery_file_dest_size

name,type,value

db_recovery_file_dest,string,/u01/app/oracle/product/19.0.0/db_1/myfiles

db_recovery_file_dest_size,big integer,15G

To monitor FRA you can query two Views

V$Recovery_File_Dest and V$Recovery_Area_Usage

Other thing We can do the following use

Multiplex control files, traced back ups and may be used to view the content of control files.

Control files can be back up to a trace file

Lets create a backup of control File,

to a trace file to see the information store in a control files

SQL>show con_name

CDB_$Root

SQL>alter database backup controlfile to trace;

Database altered

Step Now check the record in the alert log ,

     that point to the file name on the O.C for our backup

oracle@racle]tail /u01/app/oracle/diag/rdbms/orcldb/orcl/trace/alert_orcl.log

backup written to control as follows

/u01/app/oracle/diag/rdbms/orcldb/orcl/trace/orcl_ora_14147.trc

Current setting of our control files

--------------------------------------

show parameter control files

name,         type,            value

control files,string,/u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/control01.ctl,

                     /u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/control02.ctl

Creating 3rd copy of control file for your self 

------------------------------------------------

Alter system set control_file='/u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/control01.ctl',

                              '/u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/control02.ctl',

                              '/u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/control03.ctl' SCOPE=SPFILE;

System altered.

SQL>shutdown immediate.

database closed

database dismounted.

oracle instance shut down.

[oracle@oracle]cp 

Then copy an existing control file to the another location by your selection.

[oracle@oracle~]cp /u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/control02.ctl /u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/control03.ctl

[oracle@oracle~]ls /u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/control0*

SQL>startup 

Next step to multiplex files and redo log group

---------------------------------------------------

Instance/Disk failure is recoverdusing redo log information is used to roll data file forward

Redo log group are made up of redo log files

Redo log is duplicate to group at least two file per group

Join V$log and V$logfile to see all redo log member and their status

--------------------------------------------------------------------

select b.group#,a.status, b.status,b.member from V$log , V$logfile where a.group#=b.group# order by 1,2

Group#,status,status,member

Add addition member to each group

--------------------------------

SQL>Alter database 

add logfile member '/u01/app/oracle/product/19.0.0/db_1/mydbfiles/another_disk/redo01.log'

to  group 1;

exit;

[oracle@oracle~]mkdir /u01/app/oracle/product/19.0.0/db_1/mydbfiles/another_disk

[oracle@oracle~]sqlplus / as sysdba

SQL>set markup csv on;

SQL>Alter database 

add logfile member '/u01/app/oracle/product/19.0.0/db_1/mydbfiles/another_disk/redo01.log'

to  group 1

SQL>add logfile member '/u01/app/oracle/product/19.0.0/db_1/mydbfiles/another_disk/redo02.log'

to  group 2

Next Step is to enable archiving of the redo logs

------------------------------------------------

Note Every single 

1)Insert,

2)delete

3)update statemetn is recorded in the redo logs files.

------------------------------------------------------

Redo log Switch

------------------------------------------------------

When Oracle finished writing to  a redo log grop that is group number 1,

It performs a redo log Switch.

After that redo log group 1 is archiving to redo log backup to some external storage for recovery purpose.

----------

Note:-We rely on the DB archive redo logs that contains/records the history of all committed DB Transactions

---

SQL>archive log list; 

----------

(FAST Recovery Area) Backup_Recovery Parameter

db_recovery_file_dest_

db_recovery_file_dest_size (used to control the max of space to store backup file in side FRA)

db_recovery_file_dest-control (the location of FRA)

SQL>show parameter db_recovery_file_dest_size

name,type,value

db_recovery_file_dest,string,/u01/app/oracle/product/19.0.0/db_1/myfiles

db_recovery_file_dest_size,big integer,10G

To increase the size of FRA

----------------------------  FRA------------------------------------------------------                

SQL>Alter System set db_recovery_file_dest_size=15G SCOPE=BOTH 

SQL>show parameter db_recovery_file_dest_size

name,type,value

db_recovery_file_dest,string,/u01/app/oracle/product/19.0.0/db_1/myfiles

db_recovery_file_dest_size,big integer,15G

To monitor FRA you can query two Views

V$Recovery_File_Dest and V$Recovery_Area_Usage

Other thing We can do the following use

Multiplex control files, traced back ups and may be used to view the content of control files.

Control files can be back up to a trace file

Lets create a backup of control File,

to a trace file to see the information store in a control files

SQL>show con_name

CDB_$Root

SQL>alter database backup controlfile to trace;

Database altered

Step Now check the record in the alert log ,

     that point to the file name on the O.C for our backup

oracle@racle]tail /u01/app/oracle/diag/rdbms/orcldb/orcl/trace/alert_orcl.log

backup written to control as follows

/u01/app/oracle/diag/rdbms/orcldb/orcl/trace/orcl_ora_14147.trc

Current setting of our control files

--------------------------------------

show parameter control files

name,         type,            value

control files,string,/u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/control01.ctl,

                     /u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/control02.ctl

Creating 3rd copy of control file for your self 

------------------------------------------------

Alter system set control_file='/u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/control01.ctl',

                              '/u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/control02.ctl',

                              '/u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/control03.ctl' SCOPE=SPFILE;

System altered.

SQL>shutdown immediate.

database closed

database dismounted.

oracle instance shut down.

[oracle@oracle]cp 

Then copy an existing control file to the another location by your selection.

[oracle@oracle~]cp /u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/control02.ctl /u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/control03.ctl

[oracle@oracle~]ls /u01/app/oracle/product/19.0.0/db_1/mydbfiles/ORCLDB/control0*

SQL>startup 

Next step to multiplex files and redo log group

---------------------------------------------------

Instance/Disk failure is recoverdusing redo log information is used to roll data file forward

Redo log group are made up of redo log files

Redo log is duplicate to group at least two file per group

Join V$log and V$logfile to see all redo log member and their status

--------------------------------------------------------------------

select b.group#,a.status, b.status,b.member from V$log , V$logfile where a.group#=b.group# order by 1,2

Group#,status,status,member

Add addition member to each group

--------------------------------

SQL>Alter database 

add logfile member '/u01/app/oracle/product/19.0.0/db_1/mydbfiles/another_disk/redo01.log'

to  group 1;

exit;

[oracle@oracle~]mkdir /u01/app/oracle/product/19.0.0/db_1/mydbfiles/another_disk

[oracle@oracle~]sqlplus / as sysdba

SQL>set markup csv on;

SQL>Alter database 

add logfile member '/u01/app/oracle/product/19.0.0/db_1/mydbfiles/another_disk/redo01.log'

to  group 1

SQL>add logfile member '/u01/app/oracle/product/19.0.0/db_1/mydbfiles/another_disk/redo02.log'

to  group 2

Next Step is to enable archiving of the redo logs

-------------------------------------------------

Note Every single 

1)Insert,

2)delete

3)update statemetn is recorded in the redo logs files.

------------------------------------------------------

Redo log Switch

------------------------------------------------------

When Oracle finished writing to  a redo log grop that is group number 1,

It performs a redo log Switch.

After that redo log group 1 is archiving to redo log backup to some external storage for recovery purpose.

----------

Note:-We rely on the DB archive redo logs that contains/records the history of all committed DB Transactions

---

SQL>archive log list; 

----------

RMAN is the primary backup technology of oracle.

that manages 

1)Backup

2)Restore

3)Recovery process

4)RMAN can store backup on disk for Quick recovery.

5)RMAN can do incremental,full backup and whole complete database backup

6)RMAN support Full backup(copy of all Data Blocks),incremental backup(only copy data block of DB which is changed compared to previous Backup)

7)RMAN have built in configuration options.

RMAN>

Backup incremental level 0 database;

Backup incremental level 1 database

Backup incremental level 1 CUMULATIVE database

DB 

Data Files  

Control Files 

Archived Log goes into the --->RMAN-->Backup set/Backup Pieces

Image copies are exact byte for the byte copies of files and recorded in RMAN respository.

RMAN creates image copies only on disk.

The file protected by RMAN includes to reconstruct the database.

1)data files

2) control files

3)Server parameter files

4)Archived redo log files

Logical backup includes

exporting database object such as  

1) table 

2)table space 

which become supplyment to physical backups for database.

[root@oracl] su oracle

[oracle@oracl]$ cd

[oracle@oracl]$ rman target=/

Recovery Manager: Release ----

Connected to target db:abcDB

RMAN>show all; --to see the available configuration option of RMAN

configure backup retention policy to redundancy 1

RMAN>show retention policy;

RMAN configuration parameters for db with unique_name abcDB are

configure backup retention policy to redundancy 1; #default

RMAN>configure retention policy to recovery window of 7 days

new RMAN configuration parameters:

RMAN>show retention policy;

 RMAN>configure retention policy clear

---------------------------------------------

To take full backup of the database

---------------------------------------------

[root@oracl] su oracle

[oracle@oracl]$ cd

[oracle@oracl]$ rman target=/

Recovery Manager: Release ----

Connected to target db:abcDB

RMAN>backup database plus archivelog;

starting backup at date-------

using target database control file instead of recovery catalog

allocated channel:ora_disk_1

RMAN>backup database root;

RMAN>backup pluggable database pdb;

[oracle@oracl]$ lsnrctl status

-------------

Directly connect to pdb database through RMAN and do backup

----------------

[oracle@oracl]$rman traget=sys / or_2022@localhost/pdb

RMAN> backup database;

or

RMAN> backup database tag 'pdb_test_Bckup'

-----

Incremental backups

------

RMAN> backup incremental level 0                database tag 'cdb_incm_level0' plus archivelog;

RMAN> backup incremental level 1                database tag 'cdb_incm_level1' plus archivelog;

RMAN> backup incremental level 1  cumulative     database tag 'cdb_incm_level1' plus archivelog;

RMAN> list backup

RMAN> list backup summary;

RMAN> list backup of database pdb2

RMAN> list backup of datafile 10

RMAN> list backup of datafile 'u01/app/oracle/product/19.0.0/db_1/yourdbfiles/orcDB/pdb2/system01.dbf'(show only the image copy of datafile)

RMAN> list backup by file;

RMAN> list archivelog all;

RMAN> list backup of controlfile

---------------------------------

RMAN Views

---------------------------------

V$RMAN_Backup_job_details

V$RMAN_status

V$backup_set

V$pdbs(contain all PDEs in CDB)

Understanding Basic of Pandas

 Basic of Pandas data frame

import pandas

l = [["a", 12, 12], [None, 12.3, 33.], ["b", 12.3, 123], ["a", 1, 1]]

df = pandas.DataFrame(l, columns=["a", "b", "c"])
print(df)

Loading the data
df=pd.read_csv("D:\\Pandas\\data.csv")

Printing its column
print(df.columns)

Query the columns
  result_df = df.query("colname=='valueincolum'").groupby(['columnname']).agg({'columname': 'sum'})

  Example #sum_df = df.groupby(['year','time']).agg({'cricketscore': 'sum', 'Totalscore': 'sum'})

Combining the two dataframes

df3=pd.concat([df, result_df])

 Remaining the columns of a dataframe

 df.rename(columns={"Transport_Cost(PKR)":"TransferCostPKR","PerUnitPrice(PKR)":"UnitPrice_in_PKR"},inplace=True)
df.info()

 

Query the data set  using Pandas
import pandas as pd
df=pd.read_csv("data.csv")
df=df[["car_model_name","car_type_name","car_Speed_Miles"]]
df1=df[(df.car_model_name=='COONDAA') & (df.car_type_name.str.contains("CKIAA"))]
df1=df1.groupby(["car_model_name","car_type_name"]).agg({'car_Speed_Miles':'sum'})
print(df1)

Making Joins on two data set using Pandas
import pandas as pd
df1=pd.read_csv("1.csv")
df2=pd.read_csv("2.csv")
df3=pd.merge(df1,df2,on='common_column_name',how='inner')
print(df3)

Converting Object to Date and getting Month from the date in Pandas

 import pandas as pd
df=pd.read_csv("D:\\Pandas\\Practice\data.csv")
df["Date"]   =pd.to_datetime(df["Date"])
if df["Date"].dt.month==1:
    print("Jan")

Concatenating and Traverse a list  in Pandas

import pandas as pd
df=pd.read_csv('datalist.csv')
l=df['first_name']
names=''
start=0
end=start+4
for i in range(1,4):#1,2,3
    if i>1:
       start=end
       end= start+4
    
    for j in range(start,end):
        names=names+"'"+str(l[j])+"'"+","
        print(j)
print(names)

Taking the name one by one in  a list  in Pandas

sample name list:first_name_samples,Tahir,Art,Khalid,Lion,Simon,John,Lota,Sage,Kris,Minna,Table 

 import pandas as pd
store=[]
df=pd.read_csv('datalist.csv')
l=df['first_name']
names=''
start=0
p=0
end=start+4
for i in range(1,4):#1,2,3
     
    if i>1:
       start=end
       end= start+4
    
    for j in range(start,end):
        names=names+"'"+str(l[j])+"'"+","
    store.append(names)
    names=''       
print(store[2])

Reading Files name from the Folder

import os
y=[]
with os.scandir() as i:
    for entry in i:
        if entry.is_file():
            n=entry.name.split('.')
            y.append(n[0])
            print(n[0])
            print(len(y)) 

Applying SQLQueries on data frames using pandassql Package

import os
import pandas
import pandasql  
df=pandas.read_csv("D:\\Pandas\\data.csv")
query = "SELECT *  FROM df "
queryresult=pandasql.sqldf(query)
print(queryresult) 

 

Using List to Prepare a IN Clause String to Query  in SQL Script

import pandas as pd

msisdn_list=[]

batch1=''

query = """

SELECT  num

FROM tableA a,

             tableB b,

WHERE a.abc=b.abc

and num  in ({})

"""

 df = pd.read_csv('yourdata.csv')

df['num'] = df['num'].astype(str)

df['num']= df['num'].map(lambda m: m.split('.')[0])

num_list=df['num'].unique().tolist()

#num_list=str(num_list)[1:-1]

final_num_list=num_list

 

for j in range(0,999):

    batch1= batch1+"'"+str(final_num_list[j])+"'"+","

batch1=batch1[:-1]

print(batch1)

query= query.format(batch1)

Result= pd.read_sql(query, con)

Result.to_csv('./yourresult.csv', index=False, encoding='utf-8-sig')

 Adding File Name as column in data frame

import os
import pandas as pd
fname=[]
for f in os.listdir("D:\\New folder"):
    if f.endswith('csv'):
    print(f)
    fname.append(f)
df = pd.DataFrame()
for i in range(1,len(fname)):
    data=pd.read_csv("D:\\New folder\\"+fname[i]+" ", dtype=str)
    data['FileName']=fname[i]
    df = df.append(data)
print(df)
df.to_csv('.\\abc.csv',index=False)

Practical Python Codes

How to Merge Multiples tabs in excel into One Excel sheet

import pandas as pd
import numpy as np
import os, collections, csv
from os.path import basename
df = []
f = "D:/Test/Any_Data.xlsx"
numberOfSheets = 5 #Modify this.
for i in range(1,numberOfSheets+1):    
     data = pd.read_excel(f, sheet_name = 'Sheet'+str(i), header=None)
     df.append(data)
final = "D:/Test/mergedfile3.xlsx"
df = pd.concat(df)
df.to_excel(final)

How to Merge Multiples ExcelFiles into One excel sheet

import os
import pandas as pd
os.chdir('D:\\Test\\')
cwd = os.path.abspath('D:\\Test\\')
files = os.listdir(cwd)  
## Method 1 gets the first sheet of a given file
df = pd.DataFrame()
 

for file in files:
    if file.endswith('.xlsx'):
        df = df.append(pd.read_excel(file), ignore_index=True)
df.head()
df.to_excel('total_sales.xlsx')

Information updating using Pandas Writing Queries on CSV files and put it in your XL sheet.

import pandas as pd
import xlwings as xw
from pandasql import sqldf
pysqldf = lambda q: sqldf(q, globals())
df= pd.read_csv('Test.csv')
df1=pysqldf("SELECT * FROM df where columnname not like 'None' and column_2 in('abc') order by host, case areas when 'London' then 1 when 'UK' then 2 when 'Pakistan' then 3 END ASC")
new_header = df1.iloc[0] #grab the first row for the header
df1 = df1[1:] #take the data less the header row
df1.columns = new_header #set the header row as the df header
print(pysqldf('SELECT * FROM df1'))
#load workbook that has a chart in it
wb = xw.Book('D:\\Python_Work\\data\\AnyReport.xlsx')
ws = wb.sheets['Sheet1']
ws.range('S5').options(index=False).value = df1
wb.save('D:\\Python_Work\\data\\AnyReport2.xlsx')
xw.apps[0].quit()

Loading an Excel File and then Update the sheet information on certain Row and Column

#Using openpyxl
from openpyxl import workbook #pip install openpyxl
from openpyxl import load_workbook
wb = load_workbook("D:\TEST\T.xlsx")
sheets = wb.sheetnames
Sheet1 = wb[sheets[Sheet1]]
#Then update as you want it
Sheet1 .cell(row = 2, column = 4).value = 5 #This will change the cell(2,4) to 4
wb.save("HERE PUT THE NEW EXCEL PATH")
 

Email Handling in Python

import datetime as dt
from datetime import timedelta

todayDaysDateTime = dt.datetime.now() - dt.timedelta(days = 0)
Outlook = win32com.client.Dispatch("Outlook.Application").GetNamespace("MAPI") #Opens Microsoft Outlook
folder = Outlook.Folders[0] #your Inbox
print(folder)
subFolder = folder.Folders[1] #your inbox
subFolderMessages = subFolder.Items #item of your inbox mean your emails
message = subFolderMessages.GetFirst()

while message:
    #Here needs filter which should print only those mails having approved keyword
               if(message.Subject=="sub1" ):
                              print("yes")
                              break
               else:
                              message = subFolderMessages.GetNext()
print(message.Subject) #get the subjet of your email
myattachements=message.Attachments  #attaments of your email.
attach = myattachements.item(1)   # get the attahement of email.
attach.SaveAsFile('A:\\dir1\\dir2\\'+str(attach))  # save it to the location. 

Other Approach Using for Loop

import win32com.client
import time
import datetime as dt
from datetime import timedelta

todayDaysDateTime = dt.datetime.now() - dt.timedelta(days = 0)
day_num = todayDaysDateTime.strftime("%Y-%m-%d")
Outlook = win32com.client.Dispatch("Outlook.Application").GetNamespace("MAPI") #Opens Microsoft Outlook
folder = Outlook.Folders[0] #your Inbox
print(folder)
subFolder = folder.Folders[1] #your inbox
subFolderMessages= subFolder.Items #item of your inbox mean your emails


for message in subFolderMessages:
    if('youremailsubject' in message.Subject and str(message.ReceivedTime).split(' ')[0] = f'{day_num}'):
        print(message.ReceivedTime)
        print(message.Subject)
        break
    
myattachements=message.Attachments  #attaments of your email.
attach = myattachements.item(1)   # get the attahement of email.
attach.SaveAsFile('A:\\path1\\path2\\'+str(attach))  # save ito the location.

##############

#import pyodbc
import pandas.io.sql as psql
import pandas as pd
import datetime
from datetime import timedelta
import win32com.client

#Variables for con, dataframe
con=pyodbc.connect("DSN=WorldISWorldFULL",autocommit=True)
cursor=con.cursor()
MY_ARRAY_TAB= ["ABC","DEF","GHI"]
D='abc123'
b='abc123'

#Generating Questions
def queries(Dates,CAB,CABDAY,CABM):
    if 'InTheMYARY' in CAB:      
        return """
               s '{}' as D,'{}' as CAB, count(**************) as CTS from {}_{}
        """.format(Dates,CAB,CAB,CABDAY)
    else:    
        return """
               s '{}' as D,'{}' as CAB, count(************) as CTS from {}_{}
               dare datefdateabc(abc,def+cz*circle,2020-11-11),dd/MM/yy)='{}'
        """.format(Dates,CAB,CAB,CABM,Dates)


#Dates YourDaysRNumbered Convt
def date_conversion(x,CAB):
    sysdate=datetime.datetime.YourDaysRNumberednow()
    unixdate=datetime.datetime(2020,11,11)
    YourDaysRNumbered=sysdate-timedelta(days=x)
    CABDAY=str((YourDaysRNumbered - unixdate).days)
    CABM=(sysdate.year - unixdate.year) * 144 + (sysdate.month - unixdate.month)
    Date = datetime.datetime.now()-timedelta(days=x)
    Date=str(Date.strftime("%d/%m/%y"))
    return Date,queries(Date,CAB,CABDAY,CABM)

#Default value if CAB NF
def default_frame(d,t):
    default_value = [[d,t,'NF']]
    return pd.DataFrame(default_value,columns=['Dates','CAB','CTS'])


def send_mail(df):
        o = win32com.client.Dispatch("Outlook.Application")
        Msg = o.CreateItem(0)
        Msg.Importance = 0
        Msg.Subject = 'Hello World'
        Msg.To = DESTINATARIES
        Msg.SOnBehalfOfName = sby
        Msg.HTMLBody='''<HTMLH>TEST ONE<HTMLH>{}'''.format(df.to_html(index=False))
        Msg.Send()


# Main loop for CAB
def main():
    df = pd.DataFrame()
    for CAB in MY_ARRAY_TAB:
        for x in range(7,11):       
            dates,sql = date_conversion(x,CAB)
            try:
                df1=psql.read_sql(sql,con)
                df = pd.concat((df,df1))
            except  Exception as e:
                if 'NF(2020)' in e.args[0]:
                    df=pd.concat((df,default_frame(dates,CAB)))
                else:
                    print(e.args[0])
                          
    send_mail(df)        




if __name__ == "__main__":
    main() 

######

Query An Excel files Using Pandas and  SQLDF and Saving it as to a  Comma Separated Values

import pandas as pd
from pandasql import sqldf
from datetime import date, timedelta  

dt = date.today()   
today = date.today()- timedelta(1)
day_num = today.strftime("%Y-%m-%d")
print(day_num)
pysqldf = lambda q: sqldf(q, globals())
df= pd.read_excel('1.xlsx', sheet_name='sheet1')
df.columns = df.columns.str.strip().str.lower().str.replace(' ', '_').str.replace('(', '').str.replace(')', '')
df["my_date_"] = df["my_date"].dt.date # tell the data frame that take my_date as date colume type
df1=pysqldf(f"SELECT  my_date, count(1) FROM df where my_date='{day_num}' group by my_date ") #
print(pysqldf('SELECT * FROM df1'))
df1.to_csv(f'YourFileName_{day_num}.csv',index=False)

Reading a value from the CSV file and then put that value to an Excel file on a target cell

import pandas as pd
import xlwings as xw

#read the csv file in data frame
df= pd.read_csv('1.csv')
df["columnNameof_CSVFile"]
#read a particular value from the column of the dataframe
print(df['columnNameof_CSVFile'].values[indexNo])#7 or 0

# load workbook
wb = xw.Book('2.xlsx')
ws = wb.sheets['SheetName in 2.xlsx']
x=ws.range('C' + str(ws.cells.last_cell.row)).end('up').row + 1
cell = 'C' + str(x)
ws.range(cell).value=df['columnNameof_CSVFile'].values[7]
wb.save('2.xlsx')

Query your Database and write the result of your sql query to the CSV file

import pyodbc
import csv

def write_queryresult_to_csv_file(cursor, sql, csv_file):
        cursor.execute(sql)
        file_header = ''
        f = open(csv_file, 'w')
        columns = [column[0] for column in cursor.description]
        for col in columns:
            file_header = file_header + col + ','
        f.write(file_header[:-1] + '\n')
        for row in cursor:
            file_row = ''
            for element in row:
                if element is None:
                    element = ''
                file_row = file_row + element.__str__().replace(',', ' ') + ','
            f.write(file_row[:-1] + '\n')
        f.close()
       
   
         
pyodbc.autocommit = True
ch=pyodbc.connect('DSN=odbcdbname', autocommit=True)
curs = ch.cursor()
sql="yourSQLQuery"
file_name="D:\\dirname\1.csv"
write_queryresult_to_csv_file(curs,sql,file_name)

Dealing with the Weekdays in Python

x=0
day ='day/month/YY'
dt = datetime.strptime(day, '%d/%m/%y')
print("Date given by the use"+str(dt))
if dt.weekday()==6:#Weekday return 6 for Sunday and Zero for Monday in python
    start = dt - timedelta(days=x)
    end = start + timedelta(days=6)
    print(start)
    print(end)
    if dt.weekday()==0:
        x=1
        start = dt - timedelta(days=x)
        end = start + timedelta(days=6)
        print(start)
        print(end)
   
else:
    start = dt - timedelta(days=dt.weekday()+1)
    end = start + timedelta(days=6)
    print(start)
    print(end)

print("Week day="+str(dt.weekday()))

Machine Learning and Data Science using Python

Environment of Work in DataScience

1) Work Environment using Jupyter
2) Data Analysis our data using library using Pandas
3) Numpy (library for data science is NumPy, a fundamental for a data science guy)
4) Data Visualization( library matplotlib that allows to make neat graphs & visuals describe our data)
5) Use models, train models & Check the accuracy of the ML Models (using Scikit-learn library)
6) Types of Learning (Supervised Learning Regression and Classification)
7) Special topic such as ( Deep Learning and neural networks, and transfer learning) using latest version of TensorFlow and Keras


What is Machine Learning
In simple words When computer learn from given input of data.

Machine learning is, using an algorithm or computer program to learn about different patterns in data, and then taking that algorithm and what it's learned to make predictions about the future using similar data machine learning algorithms are also called models.

Type of Machine Learning
1) Supervised Learning is done by drawing lines based on the given data. Example is regression
2) Unsupervised Learning when we have given data without Labels this is called unsupervised Learn eg; csv files without column names.In this we associated rules with set of given data.
3) Reinforcement is teaching Machine through trial and error through rewards and punishment

Steps in Data Models

1. Problem definition (What type of problem we have at hand)

2 Data (What type data we need to analyze)

3. Evaluation of Success and Failure

4. Features (feature we should model)

5. Model (what Kind of Model we should use to solve the problem)

6. Test Different combination we should apply 

Conclusion.

Keep doing the steps until we solve the problem using our model.

Flow of data science work

 A practical and usual data science workflow started with

1)      opening a csv file in  Jupiter notebook

2)      exploring the data and performing data analysis using pandas

3)      Making visualizations/ graphs and comparing different data points using map plot lib

4)      Building machine learning models on the data using Scikit-learn.

Key

 *Pandas   (a python library for data analysis)

*map plot lib (a python library for Visualization and graphs)

*Jupiter notebook (a tool for building machine learning projects)

*Scikit-learn also know as sklearn (free machine learning library for the Python)

Type of Data 

Unstructured data are things like images, mobile calls voice data, videos

Structured data like patient medical history of disease

Type of Machine Learning

1.       Supervised learning

2.       Unsupervised learning

3.       Transfer learning

4.       Reinforcement learning

Type of Evaluation

Classification	Regression	Recommendation
Accuracy	Mean Absolute error (MAE)	Precision at K
Precision	Mean Square error(MSE)
Recall	Root Mean Square error RMSE

 

Feature variables are used to predict the target variables

Feature variable are the attributes describing your data.

Variables in Data Science

Feature variables are used to predict the target variables

Feature variable are the attributes describing your data.

Kinds of feature variables

1.       Numerical feature (like body height number)

2.       Categorical feature (like gender Male and Female)

3.       Derived feature ( visit by a person to hospital in a year)

Important concept of ML

Split your data into 3 different sets 

1.      training dataset        to train your model

2.      validation data set    to choosing your model

3.      test data set               to test and compare your model with different models

split your data usually

70% for training data set

15 % for validation data set

15% for test data set

Three parts of modelling

1 choosing a model

2 training a model

3 Comparing a model

Choose a model

Working with structured data following model will work best

1.      decision trees

such as random forest /gradient boosting algorithms (cat boost in, x g boost)  

Working with unstructured data following model will work best

1.       deep learning neural networks

2.       transfer learning

Training of the model

By align your input (feature variables) and output (target variable) of the data

Feature variable input to the model  àModel (mode will find the patterns)àTarget variable as output

 input (feature variables) =X the data

is used to predict

output (target variable)= y

Improvement of your Model

 hyper parameters (choose different hyper parament for your model)

Fitting the model line on data point  is conceptualized as follows

1.       overfishing happens due to data leakage

2.       underfunding happen due to  data mismatch

3.       balanced(Goldilocks Zone)

Data leakage & Data Mismatch

Data leakage happens when some of your test data leaks into your training data

This often results in overfishing or a model doing better on the test set then on the training dataset

Data mismatch happens data used of testing is different to the data used for training that is

Training data is not equal (! =) testing data set

features in the training data set! = features in the testing data set.

Model Tuning

Training and Testing data set are to do Model Tuning

Training data set is used for

1.       Validation

2.       model tuning

Testing data set used for

1.      Testing

2.      Model comparison

Model comparison(comparing cars to cars)

To Build the Environment.

In anaconda prompt window(Miniconda3)
conda create --prefix ./env pandas numpy matplotlib scikit-learn
or I want to install the saperate jupyter
conda install jupyter
C:\Users\Tahir\Desktop\sample_pro\env>conda activate C:\Users\Tahir\Desktop\sample_project_1\env
after running the command prompt converts into following.
(C:\Users\Tahir\Desktop\sample_pro\env)
Note:-For Kernel error in jupyter notebook install as follows:-pip install jupyter_client --upgrade

Pandas  in Python for Data Analysis

import pandas as pd
names=pd.Series(["Tah","Zah","Mah"])
colour=pd.Series(["Red","green","Blue"])
df=pd.DataFrame({"k1":names,"k2":colour})
#import and read data  from csv using data frame
df.to_csv("abc.csv",index=False)
df2=pd.read_csv("abc.csv")
#attribues of DF
df.dtypes
df.columns
df.index
#function of DF
df.info()
df.describe()
df2["column_name"].sum()
df2.column_name
      

       Envivronment Keys:-
        Hit Esc then m , x b ,a in Jupyter Notebook
       Hit  shit+tab to see the description of  function
      Hit shit enter to execute the command in Jupyter Notebook
Hit Ctr+shit - to break the  lines in Jupyter Notebook
     

Data frames in Pandas

Column represent by axis=1
Row       represented by axis=0
index number starts at 0 by default.

pd.crosstab(df["col1"],df["col2"])
df["col1"].fillna(df["col1"].mean(),inplace=True)
df["col1"].dropna(inplace=True)
## how to create colum using series in python
new_column=pd.Series([7,7,7,7))

##how to create column from python  list

##should be the same length as  data frame df

capcity_column=[7.2,8.2,3.2,2.2]

df["An_other_new_Column"]=capcity_column

## To drop a colum using the function

df.drop("ColumnName", axis=1)

## to shuffule the data frame

       new_DF=df.smaple(frac=0.5) or frac=1 for 100%
## to remove the extra index colum using
df.reset_index(drop=True,inplace=True)

anonymous function.

apply a function to the column of the data frame

     df["columnName"]=df["columnName"].apply(lambda x: x/2)

this is say assign x=x/2 in function name lambda takes x then reassign x to x after dividing by 2

## ndarray in python of numpy package

       mysamplearray=np.ones()  # hit the key shift+tab to see the description of th function
mysamplearray=np.ones()
mysamplearray=np.zeros()
mysamplearray=np.arrange(0,10,2) #0=start, end=10 and 2= the step interval
mysamplearray=np.arrange(0,10,2)
mysamplearray=np.random.randint()
mysamplearray=np.random.rand(5,3) # 5 row and 3 column array
mysamplearray=np.random.seed()# Pseudo-random number.

##ploting using matplotlib in python

%matplotlib inline

import matplotlib.pylot as   mplt

import pandas as pd

import numpy as np

x=[1,2,3,4]
y=[11,22,33,44]
mplt.plot(x,y)
------------------

## Flow of matplotlib

# 1. import matplotlib and get it ready for  plotting in jupyter

     %matplotlib inline
import matplotlib.pyplot as plt

# 2 prepare data

      x=[1,2,3,4]
y=[11,22,33,44]

#3 setup plot

fig,ax=plt.subplots(figsize=(10,10)); # (width,hight)

# add data

   ax.plot(x,y)

# 4. customize plot

ax.set(title="SimplePlot", xlabel="x values", ylabel="y values")

# 5. save your figure

fig.savefig("images/simpleplot.png")

## Making figures using NumPy arrays

    #Line plot

    # Scatter plot

    # Bar Plot

    # Histogram

import numpy as np

#create some data

       x=np.linspace(0,10,100)
       x[:10]

#plot the data and create a line plot

       fig,ax=plt.subplots()
       ax.plot(x,x**2);

 
       fig,ax=plt.subplots()
ax.scatter(x,np.sin(x))

# making a plot from dictionary

    prices={"butter":10, "Cake":20, "Milk":50}
fig,ax=plt.subplots()
ax.bar(prices.keys(),prices.values())
ax.set(title='butter Cake Milk Shop',ylabel="prices")

#How to Plot our data frame using Pandas

      import pandas as pd

#make a data frame

      cs=pd.read_csv("CS.csv")
cs["Price"]=cs["Price"].str.replace('[\$\,\.]','')
cs["Price"]=cs["Price"].str[:-2]

#adding date column in our dataframe

     cs["S_date"]=pd.date_range("1/1/2020",periods=len(cs))

cs["Total_S"]=cs["Price"].astype(int).cumsum()

# now plot the s_date and Total_S

cs.plot(x="S_date",y="Total_S");














# Plot a bar graph
x=np.random.rand(10,4)
# Now Make  a data frame of these random data.
df=pd.DataFram(x,column=['a','b','c','d']

df.plot.bar()

cs[].plot(x="Make",y="Meter",kind="bar")

Histogram?

cs["Price"].plot.hist()





cs["Price"].astype(int)
cs.plot(x="Odm)",y="Pr", kind="scatter");

#Using subplots making all column plot in one graph.

      cs.head()
cs.plot.hist(figsize=(10,30),subplots=True);

#Object Oriented  Method

fix,ax=plt.subplots(figsize=(10,6))
of.plot(kind='scatter',x='age','high',c='tgt',ax=ax);
ax.set_xlim([50,100])

## OO Method from the base

Fig,ax=plt.subplots(figsize=(10,6))

#Plot the data
scatter=ax.scatter(x=df["age"],
                                    y=df["ch"],
                                     c=df["trgt"])

#Customize the plot

ax.set(
          title="HDD and LL" ,
         xlabel="Age"
          ylabel="ch")

# Add a legend

ax.legend("scatter.legend_elements(),title="trgt");                                     

#Add a horixontal line

ax.axhline(df["ch"].mean(),linestyle='--')

# subplot of  age, hight, weight

fig,(ax0,ax1)=plt.subplots(nrow=2,ncols=1,figsize=(10,10))

SK-Lean or Scikit-Lear is Machine Learning Library of Python 

Work Flow of Machine Learning Models

Getting data ready (to be used with ML Models)

Choosing a machine learning Models

Fitting a model to the data

Making prediction with a model

Evaluating model predictions

Improving model prediction

saving and loading models

Example for this

# Standard imports
%matplotlib inline
import matplotlib.pyplot as plt
import pandas as pd
import numpy as np

#1. Get the data ready
###################################################################
# Import dataset
bad_disease = pd.read_csv("../data/bad-disease.csv")
# View the data
bad_disease.head()
####################################################################
# Create X (all the feature columns)
X = bad_disease.drop("target", axis=1)
# Create y (the target column)
y = bad_disease["target"]
####################################################################
# Split the data into training and test sets
from sklearn.model_selection import train_test_split
X_train, X_test, y_train, y_test = train_test_split(X, y)
# View the data shapes
X_train.shape, X_test.shape, y_train.shape, y_test.shape

#2. Choose the model/estimator
from sklearn.ensemble import RandomForestClassifier
model = RandomForestClassifier()

#3. Fit the model to the data and use it to make a prediction
#A model will (attempt to) learn the patterns in a dataset by calling the fit() function on it and passing it the data.
model.fit(X_train, y_train)

#Once a model has learned patterns in data, you can use them to make a prediction with the predict() function.
# Make predictions
y_preds = model.predict(X_test)

# This will be in the same format as y_test
y_preds

X_test.loc[209]
bad_disease.loc[209]

# Make a prediction on a single sample (has to be array)
model.predict(np.array(X_test.loc[209]).reshape(1, -1))
#array([0])


4. Evaluate the model
#A trained model/estimator can be evaluated by calling the score() function and passing it a collection of data.

# On the training set
model.score(X_train, y_train)

# On the test set (unseen)
model.score(X_test, y_test)

#5. Experiment to improve (hyperparameter tuning)
#A model's first evaluation metrics aren't always its last. One way to improve a models predictions is with hyperparameter tuning.

# Try different numbers of estimators (n_estimators is a hyperparameter you can change)
np.random.seed(42)
for i in range(10, 100, 10):
    print(f"Trying model with {i} estimators...")
    model = RandomForestClassifier(n_estimators=i).fit(X_train, y_train)
    print(f"Model accruacy on test set: {model.score(X_test, y_test)}")
    print("")

#6. Save a model for later use
#A trained model can be exported and saved so it can be imported and used later. One way to save a model is using Python's pickle module.

import pickle
# Save trained model to file
pickle.dump(model, open("random_forest_model_1.pkl",

# Load a saved model and make a prediction on a single example
loaded_model = pickle.load(open("random_forest_model_1.pkl", "rb"))
loaded_model.predict(np.array(X_test.loc[209]).reshape(1, -1))

Another Way of the Example

--We fit the model on the training data and train the model on testing data
--Classification model( random forest)  find the patters in the training data 
-- Make prediction on test data

import pandas as pd
hd=pd.read_csv("data/baddisease.csv")
#create x the feature columns
X=hd.drop("target",axis=1)
#create y the label
y=hd["target"]
#Choose the right  model and hyperparameter
# RandomForestClassifier is a classifcation Machnine Learning Model
from sklearn.ensemble import RandomForestClassifier
clf=RandomForestClassifier(n_estimators=100)
# we will keep the default hyperparameter
clf.get_params()
# 3. Fit the model to the training data
from sklearn.model_selection import  train_test_split
X_train, X_test, y_train, y_test= train_test_split(X, y,test_size=0.2)
clf.fit(X_train,y_train) -- Fitting the model to the training data
y_preds=clf.prdict(X_test) -- X_test making prediction on test data
y_preds
#4 Evaluate the model on the training data and testing data.
clf.score(X_train,y_train)
clf.score(X_test,y_test)