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)

python modules etc

python module :   

Modules in Python are simply Python files with a .py extension. The name of the module will be the name of the file. A Python module can have a set of functions, classes or variables defined and implemented

Python Package

Packages are namespaces which contain multiple packages and modules themselves. They are simply directories, but with a twist.

Each package in Python is a directory which MUST contain a special file called __init__.py. This file can be empty, and it indicates that the directory it contains is a Python package, so it can be imported the same way a module can be imported.

__init__.py.

 In addition to labeling a directory as a Python package and defining __all__, __init__.py allows you to define any variable at the package level. Doing so is often convenient if a package defines something that will be imported frequently, in an API-like fashion. This pattern promotes adherence to the Pythonic "flat is better than nested" philosophy.

When you import a package, the __init__.py executes and defines what symbols the package exposes to the outside world.

from app import app

from package app import object app
So the first appis the name of the package (which is a folder with a __init__.py file inside) and the second is the name of the imported object from that package.