uweziegenhagen.de

CREATE TABLE paymentplan (
    contract INTEGER NOT NULL,
    paymentdate INTEGER NOT NULL,
    paymenttype INTEGER NOT NULL,
    amount NUMERIC(12, 2) NOT NULL
);

import pandas as pd
from datetime import datetime
from dateutil.relativedelta import relativedelta
 
def genPaymentPlan(contract, principal, paymentsperyear, interestrate, startdate, years):
    interest = interestrate/100*principal/paymentsperyear
    date = datetime.strptime(startdate, '%Y%m%d').date()
    output = pd.DataFrame(columns=['Contract', 'Paymentdate', 'Paymenttype', 'Amount'])
 
    # Add a row to the DataFrame
    start = {'Contract': contract, 'Paymentdate': startdate, 'Paymenttype': 1, 'Amount' : principal}
    output = pd.concat([output, pd.DataFrame([start])], ignore_index=True)  
 
    for payment in range(paymentsperyear*years):
        new_date = (date + relativedelta(months=12/paymentsperyear))
        date = new_date
        interest_line = {'Contract': contract, 'Paymentdate': new_date.strftime('%Y%m%d'),'Paymenttype': 2, 'Amount' : -1*interest}
        output = pd.concat([output, pd.DataFrame([interest_line])], ignore_index=True)  
 
    end = {'Contract': contract, 'Paymentdate': date.strftime('%Y%m%d'), 'Paymenttype': 3, 'Amount' : -principal}
    output = pd.concat([output, pd.DataFrame([end])], ignore_index=True)          
    return output
 
 
test = genPaymentPlan(123458, 2000, 2, 12, '20260101', 4)
#print(test)
 
 
 
# Generate INSERT statements
print("Generated SQL INSERT statements:\n")
 
for index, row in test.iterrows():
    contract = int(row['Contract'])
    date = row['Paymentdate']  # already string in format YYYY-MM-DD
    type =  row['Paymenttype'] 
    amount = float(row['Amount'])
 
 
    insert = f"INSERT INTO paymentplan (contract, paymentdate,paymenttype, amount) VALUES ({contract}, '{date}', {type} , {amount});"
    print(insert)

import pandas as pd
from openpyxl import Workbook
from openpyxl.utils.dataframe import dataframe_to_rows
 
 
# output control
files = ['']
 
wb = Workbook()
wb.remove(wb['Sheet'])
 
for index, file in enumerate(files,0):
    temp = pd.read_csv(file + '.csv',sep='\t')
    ws1 = wb.create_sheet()
    ws1.title = file
    rows = dataframe_to_rows(temp, index=False, header=True)
 
    for r_idx, row in enumerate(rows, 1):
        for c_idx, value in enumerate(row, 1):
            ws1.cell(row=r_idx, column=c_idx, value=value)
 
wb.save('files45646.xlsx')
wb.close()

import pandas as pd
 
# initialize data of lists.
data = {'Hersteller': ['VW', 'BMW', 'VW', 'Porsche'],
        'Modell': ['Golf', '1er', 'Polo', '911']}
 
 
df = pd.DataFrame(data)
 
 
print(df,'\n')
 
 
df['Hersteller'] = df['Hersteller'].replace(
    {"VW": "Volkswagen", "Horch": "Audi"})
 
 
print(df)

import duckdb as ddb
import pandas as pd
 
con = ddb.connect(':memory:')
 
con_p = ddb.connect('my_database.db')
 
con_p.execute('CREATE OR REPLACE TABLE telefonnummern(fullname VARCHAR,phone VARCHAR);')
 
 
con_p.execute("INSERT INTO telefonnummern VALUES ('Max Mustermann', '0123-4567890')")
 
print(con_p.sql('SHOW ALL TABLES'))
 
print(con_p.sql('SELECT * FROM telefonnummern;'))
 
 
ddb_object = con_p.sql('SELECT * FROM telefonnummern;')
 
df = ddb_object.to_df()
 
ddb_tuple = ddb_object.fetchall()
 
print(df)
 
print(ddb_tuple)

"""
icecream examples
"""
 
 
from icecream import ic
 
# define some function
def addiere(x, y):
    return x + y
 
# call ice
ic(addiere(1, 2))
 
# Output:
# ic| addiere(1, 2): 3
 
 
d = {'i': 2, 'j': 3, 'k': 4711}
ic(d['k'])
 
 
struct = {
    "hersteller": "VW",
    "modell": "Golf",
    "Farben": ["gelb", "rot"]
}
 
ic(struct)
 
ic.disable()
ic(struct) # no output
ic.enable()
 
 
def logstuff(text):
    # log to output file
    print(text)
 
 
ic.configureOutput(prefix="Hallo| ", outputFunction=logstuff)
 
ic(addiere(7, 7))
 
 
ic.configureOutput(prefix="Welt| ", outputFunction=logstuff)
 
ic(addiere(7, 7))

import os
import paramiko
 
# Replace these variables with your specific values
host = '192.168.0.22'
port = 22
username = '<user>'
private_key_path = 'keyfile'
remote_directory_path = '/home/uwe/uploadtest'
local_directory_path = 'E:/uploadtest'
 
 
# Establish an SSH transport session
private_key = paramiko.RSAKey(filename=private_key_path)
transport = paramiko.Transport((host, port))
transport.connect(username=username, pkey=private_key)
 
# Create an SFTP client
sftp = paramiko.SFTPClient.from_transport(transport)
 
try:
    # Iterate through local files in the specified folder
    for local_file in os.listdir(local_directory_path):
        local_file_path = os.path.join(local_directory_path, local_file)
 
        # Check if the file is a CSV file
        if os.path.isfile(local_file_path): # and local_file.lower().endswith('.csv'):
            remote_file_path = os.path.join(remote_directory_path, local_file)
 
            # Upload the CSV file
            sftp.put(local_file_path, remote_file_path)
            print(f"Uploaded: {local_file} to {remote_file_path}")
 
finally:
    # Close the SFTP session and SSH transport
    sftp.close()
    transport.close()

import os
import paramiko
 
# Replace these variables with your specific values
host = '192.168.0.238'
port = 22
username = '<user>'
private_key_path = '<keyfile>'
remote_directory_path = '/home/uwe/downloadtest'
local_directory_path = 'E:/downloadtest'
 
# Establish SSH connection
try:
    # Create a new SSH client
    ssh_client = paramiko.SSHClient()
 
    # Automatically add the server's host key
    ssh_client.set_missing_host_key_policy(paramiko.AutoAddPolicy())
 
    # Load the private key for authentication
    private_key = paramiko.RSAKey.from_private_key_file(private_key_path)
 
    # Connect to the server
    ssh_client.connect(hostname=host, port=port, username=username, pkey=private_key)
 
    # Open an SFTP session on the SSH connection
    sftp = ssh_client.open_sftp()
 
    # Change to the remote directory
    sftp.chdir(remote_directory_path)
 
    # List all files in the remote directory
    files = sftp.listdir()
 
    # Download each CSV file in the remote directory
 
    for file_name in files:
        # os path join uses system slashes, must make sure they are right
        remote_file_path = os.path.join(remote_directory_path, file_name).replace("\\","/")
        local_file_path = os.path.join(local_directory_path, file_name).replace("\\","/")
        print(remote_file_path, local_file_path)
 
        # Check if the file is a CSV file
        if file_name.lower().endswith('.txt'):
            sftp.get(remote_file_path, local_file_path)
            print(f"File '{file_name}' downloaded successfully to '{local_directory_path}'")
 
    # Close the SFTP session and SSH connection
    sftp.close()
    ssh_client.close()
 
except paramiko.AuthenticationException:
    print("Authentication failed. Please check your credentials or SSH key path.")
except paramiko.SSHException as e:
    print(f"SSH connection failed: {e}")
except FileNotFoundError:
    print("File not found. Please provide the correct file paths.")
except Exception as e:
    print(f"An error occurred: {e}")

import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
 
def send_email():
    # Email content
    sender_email = 'YOUR_EMAIL_ADDRESS'
    password = 'YOUR_PASSWORD'
    recipient_email = 'RECIPIENT_EMAIL_ADDRESS'
    subject = 'SUBJECT'
    body = 'EMAIL_BODY'
 
    # Create a multipart message and set headers
    message = MIMEMultipart()
    message['From'] = sender_email
    message['To'] = recipient_email
    message['Subject'] = subject
 
    # Add body to email
    message.attach(MIMEText(body, 'plain'))
 
    try:
        # Connect to SMTP server (for Gmail use 'smtp.gmail.com', for others, refer to your provider's settings)
        smtp_server = smtplib.SMTP('smtp.yourprovider.com', 587)
        smtp_server.starttls()  # Enable encryption for security
        smtp_server.login(sender_email, password)
 
        # Send email
        smtp_server.sendmail(sender_email, recipient_email, message.as_string())
        print("Email sent successfully!")
 
        # Close the connection
        smtp_server.quit()
    except Exception as e:
        print(f"Error: {e}")
        print("Email was not sent.")
 
# Call the function to send the email
send_email()

import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
 
def send_email():
    # Email content
    sender_email = 'YOUR_EMAIL_ADDRESS'
    recipient_email = 'RECIPIENT_EMAIL_ADDRESS'
    subject = 'SUBJECT'
    body = 'EMAIL_BODY'
 
    # Create a multipart message and set headers
    message = MIMEMultipart()
    message['From'] = sender_email
    message['To'] = recipient_email
    message['Subject'] = subject
 
    # Add body to email
    message.attach(MIMEText(body, 'plain'))
 
    try:
        # Connect to SMTP server
        smtp_server = smtplib.SMTP('smtp.yourprovider.com')  # Replace with your SMTP server address
        smtp_server.sendmail(sender_email, recipient_email, message.as_string())
        print("Email sent successfully!")
 
        # Close the connection
        smtp_server.quit()
    except Exception as e:
        print(f"Error: {e}")
        print("Email was not sent.")
 
# Call the function to send the email
send_email()

import requests
from bs4 import BeautifulSoup
 
headers = {
    'Access-Control-Allow-Origin': '*',
    'Access-Control-Allow-Methods': 'GET',
    'Access-Control-Allow-Headers': 'Content-Type',
    'Access-Control-Max-Age': '3600',
    'User-Agent': 'Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:52.0) Gecko/20100101 Firefox/52.0'
}
 
 
url = "https://www.somecoolstore.de/de_DE/EUR/someproductpage"
req = requests.get(url, headers)
soup = BeautifulSoup(req.content, 'html.parser')
 
a=mydivs = soup.find("span", {"class": "padlr0-xsl"})
 
print(a.text)
input('Push any key')