SQLALCHEMY : dynamische dataframe in een tabel stoppen zonder class te definieren (ORACLE 12)

#  Hoe krijg ik een grote dataset snel in een tabel
#  Oracle heeft zijn eigen specifieke dingetjes mbt datatypes etc

from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy.sql import *

from sqlalchemy import Table, MetaData, Column, Integer, String, ForeignKey
from sqlalchemy.orm import mapper
from sqlalchemy.dialects.oracle import VARCHAR2

sqllite_DB='sqlite:///C:\\Users\\wagene002\\Documents\\Python\\howto\\DB_ZenG.db'
engine = create_engine(sqllite_DB,echo=False)
Base = declarative_base()

import cx_Oracle

#  method 2: met service naam
oracle_connection_string = ('oracle+cx_oracle://DM:*****@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=***)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=********)(SERVER=DEDICATED)))')engine = create_engine(oracle_connection_string)
Base = declarative_base()



#==>  Maak een Pandas dataset
import pandas as pd
df=pd.read_csv('C:\\Users\wagene002\Documents\Python\howto\levering1.csv')
df1=df[[ 'valid_bsn','bsn', 'code_voorziening', 'jaar', 'bedrag']]


# speciaal voor Oracle: to_sql maakt clobs van characters, via dtype dit oplossen

# manier 1: maak handmatig types aan
dict_types={'bsn': VARCHAR2(128), 'code_voorziening': VARCHAR2(100)}
# manier 2: maak automatisch dict aan met alle velden  varchar
dict_types={}
for i in df1.columns:
    dict_types[i]= VARCHAR2(150)

#maak een tabel van een gestripte dataset (3 records)
dfDef=df1.iloc[0:2,]
dfDef.to_sql(name='LeveringenSociaal',con=engine, index=False,if_exists="replace" ,dtype=dict_types)

# ORACLE TRUUK:  maak alle velden van de de dataset die weggeschreven worden string values
#zonder deze stap krijg je bij wegschrijven naar ORAClEfoutmelding TypeError: expecting string or bytes object
df8=df1.astype(str)


#===> Map een Database Tabel aan een Class Object cLev
class cLev(object):
    pass

metadata=MetaData(engine)
tblLeveringen=Table('LeveringenSociaal', metadata,Column("id", Integer, primary_key=True) ,autoload=True)
engine.execute(tblLeveringen.delete())
mapper(cLev,tblLeveringen)


# Nu de volledige dataset in de tabel stoppen
Session = sessionmaker(bind=engine)
session = Session()
session.bulk_insert_mappings(cLev, df8.to_dict(orient="records"))
session.commit()
session.close()

SQLALCHEMY: links

https://auth0.com/blog/sqlalchemy-orm-tutorial-for-python-developers/

http://www.blog.pythonlibrary.org/2010/09/10/sqlalchemy-connecting-to-pre-existing-databases/

https://www.freecodecamp.org/news/sqlalchemy-makes-etl-magically-easy-ab2bd0df928/

https://www.codementor.io/bruce3557/graceful-data-ingestion-with-sqlalchemy-and-pandas-pft7ddcy6


https://sdsawtelle.github.io/blog/output/large-data-files-pandas-sqlite.html

SQLALCHEMY : Oracle tabel aanmaken

from sqlalchemy import *
from sqlalchemy import create_engine, ForeignKey
from sqlalchemy import Column, Date, Integer, String

from sqlalchemy.dialects.oracle import VARCHAR2
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, backref
import cx_Oracle

#  method 2: met service naam
oracle_connection_string = ('oracle+cx_oracle://DM:######@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=******.basis.lan)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=********)(SERVER=DEDICATED)))')

engine = create_engine(oracle_connection_string)
# engine = create_engine('sqlite:///student.db', echo=True)
Base = declarative_base()

########################################################################
class Student(Base):
    """"""
    __tablename__ = "student"

    id = Column(Integer, primary_key=True)
    username = Column(VARCHAR2(255))
  

    #----------------------------------------------------------------------
    def __init__(self, username, firstname, lastname, university):
        """"""
        self.username = username
       

# create tables
Base.metadata.create_all(engine)

SQLALCHEMY: Hoe vul je tabel uit Statische dataframe via ORM class.

from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy.sql import *


sqllite_DB='sqlite:///C:\\Users\\wagene002\\Documents\\Python\\howto\\DB_ZenG.db'
engine = create_engine(sqllite_DB)
Base = declarative_base()

class LeveringenSociaal(Base):
    __tablename__ = "LeveringenSociaal"
    Index = Column(Integer, primary_key=True)
    valid_bsn = Column(String)
    bsn = Column(String)
    code_voorziening = Column(String)
    jaar = Column(String)
    bedrag = Column(String)

LeveringenSociaal.__table__.create(bind=engine, checkfirst=True)



==>
import pandas as pd
df=pd.read_csv('C:\\Users\wagene002\Documents\Python\howto\levering1.csv')

df1=df[['valid_bsn','bsn', 'code_voorziening', 'jaar', 'bedrag']]



===> manier 1. Niet zo snel. Per record Inserten
leveringensociaal=[]

for index,row in df1.iterrows():
    leveringensociaal.append(row)
  
Session = sessionmaker(bind=engine)
session = Session()

for lever in leveringensociaal:
    row = LeveringenSociaal(**lever)
    session.add(row)
  

session.commit()


===>  manier 2: Zeer Snel. Via Bulk Loader
Session = sessionmaker(bind=engine)
session = Session()
session.bulk_insert_mappings(LeveringenSociaal, df1.to_dict(orient="records"))
session.commit()
session.close()

SQLALCHEMY : dynamische dataframe in een tabel stoppen zonder class te definieren (SQLLITE)

#  Hoe krijg ik een grote dataset snel in een tabel
from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy.sql import *

from sqlalchemy import Table, MetaData, Column, Integer, String, ForeignKey
from sqlalchemy.orm import mapper

sqllite_DB='sqlite:///C:\\Users\\wagene002\\Documents\\Python\\howto\\DB_ZenG.db'
engine = create_engine(sqllite_DB,echo=False)
Base = declarative_base()

#==>  Maak een Pandas dataset

import pandas as pd
df=pd.read_csv('C:\\Users\wagene002\Documents\Python\howto\levering1.csv')
df1=df[[ 'valid_bsn','bsn', 'code_voorziening', 'jaar', 'bedrag']]



#maak een tabel van een gestripte dataset (3 records)
dfDef=df1.iloc[0:2,]
dfDef.to_sql(name='LeveringenSociaal',con=engine, index=False,if_exists="replace" )

#===> Map een Database Tabel aan een Class Object cLev

class cLev(object):
    pass

metadata=MetaData(engine)
tblLeveringen=Table('LeveringenSociaal', metadata,Column("id", Integer, primary_key=True) ,autoload=True)
engine.execute(tblLeveringen.delete())
mapper(cLev,tblLeveringen)

# Nu de volledige dataset in de tabel stoppen

Session = sessionmaker(bind=engine)
session = Session()
session.bulk_insert_mappings(cLev, df1.to_dict(orient="records"))
session.commit()
session.close()

Password hash Security

Workzeug is a package for password hashing

>>> from werkzeug.security import generate_password_hash
>>> hash = generate_password_hash('foobar')
>>> hash
'pbkdf2:sha256:50000$vT9fkZM8$04dfa35c6476acf7e788a1b5b3c35e217c78dc04539d295f011f01f18cd2175f'

Verification process

>>> from werkzeug.security import check_password_hash
>>> check_password_hash(hash, 'foobar')
True
>>> check_password_hash(hash, 'barfoo')
False

multiple hash

Werkzeug generate_password_hash("same password") genereates different output each time when i run it multiple times

The password is salted, yes. The salt is added to the password before hashing, to ensure that the hash isn't useable in a rainbow table attack.

Because the salt is randomly generated each time you call the function, the resulting password hash is also different. The returned hash includes the generated salt so that can still correctly verify the password.

Demo:

>>> from werkzeug.security import generate_password_hash
>>> generate_password_hash('foobar')
'pbkdf2:sha1:1000$tYqN0VeL$2ee2568465fa30c1e6680196f8bb9eb0d2ca072d'
>>> generate_password_hash('foobar')
'pbkdf2:sha1:1000$XHj5nlLU$bb9a81bc54e7d6e11d9ab212cd143e768ea6225d'

These two strings differ; but contain enough information to verify the password because the generated salt is included in each:

# pbkdf2:sha1:1000$tYqN0VeL$2ee2568465fa30c1e6680196f8bb9eb0d2ca072d
  ^^^^^^^^^^^^^^^^   salt   ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
      algo info    ^^^^^^^^        actual hash of the password
  (PBKDF2 applied SHA1 1000 times)

Because the random salt is tYqN0VeL for one and XHj5nlLU, the resulting hash is also different.

The foobar password can still be verified against either hash:

>>> from werkzeug.security import check_password_hash
>>> check_password_hash('pbkdf2:sha1:1000$tYqN0VeL$2ee2568465fa30c1e6680196f8bb9eb0d2ca072d', 'foobar')
True
>>> check_password_hash('pbkdf2:sha1:1000$XHj5nlLU$bb9a81bc54e7d6e11d9ab212cd143e768ea6225d', 'foobar')
True 


ll

Datetime

Datetime Namespace

Just like the string namespace, there is also a datetime namespace with specials datetime methods. In order to apply these methods, the column/Series has to have a datetime datatype.

date_strings = pd.Series(['3/11/2000', '3/12/2000', '3/13/2000'])

date_strings

0    3/11/2000
1    3/12/2000
2    3/13/2000
dtype: object

Let's convert this to a DateTime Series using pd.to_datetime(). It tries to infer the datetime format automatically:

pd.to_datetime(date_strings)

0   2000-03-11
1   2000-03-12
2   2000-03-13
dtype: datetime64[ns]

Sometimes this doesn't work correctly. You can give Pandas some extra information to correctly infer the format:

pd.to_datetime(date_strings, dayfirst=False, yearfirst=False)

0   2000-03-11
1   2000-03-12
2   2000-03-13
dtype: datetime64[ns]

Or you can give Pandas an exact format:

pd.to_datetime(date_strings, format='%m/%d/%Y')

0   2000-03-11
1   2000-03-12
2   2000-03-13
dtype: datetime64[ns]

Pandas used datetime formats as defined in the Python time module: https://docs.python.org/2/library/time.html#time.strftime

PS
datetime format is altijd YYYY-MM_DD. bij inlezen dataframe ook eraan denken dat als oorspronkelijke data een timestamp bevat dit ook opgegeven moet worden bij inlezen in datatime veld

bijvoorbeeld.
df

	#Passengers
Month
1949-01	112
1949-02	118
1949-03	132
1949-04	129

pd.to_datetime(df['Month'], format='%Y-%m')

Accesing datetime namespace

Access the datetime namespace using .dt on a Series of datetime objects:

ratings['timestamp'].dt.month.value_counts()

RESAMPLE

A DataFrame or Series that has a datetime index can be resampled to a desired frequency, like months, using the .resample method. This effectively means a groupby on the months of the datetimes:

ratings_new.rating.resample('M').mean().head(10)