Posts tagged ‘Python’

Mit Python camt.053 aus MT940 erzeugen

Für Dante e.V. bestand die Notwendigkeit, aus MT940 Dateien moderne CAMT.053 zu erzeugen, dank Python wurde das eine lösbare Aufgabe.

Schritt 1

Die MT940 Datei parsen und die Transaktionen in einen pandas DataFrame überführen.

import mt940
import pprint
import pandas as pd

df = pd.DataFrame()

transactions = mt940.parse('Umsaetze_2310007_22.07.2024.mta')

print('Transactions:')
pprint.pprint(transactions.data)

for transaction in transactions:
    print('Transaction: ', transaction)
    pprint.pprint(transaction.data)
    t = transaction.data
    tt = pd.DataFrame(t, index=[0])

    df = pd.concat([df,tt],ignore_index=True)
    
df.to_excel('AllBookings.xlsx',index=False)

Schritt 2

Aus dem DataFrame das XML befüllen, die für den Kopf der XML-Datei notwendigen Kontostandsinformationen holen ich dazu aus der MT940 Datei.

import pandas as pd # data wrangling
import jinja2 # template engine
import os # for file-related stuff
import mt940
from datetime import datetime


today = datetime.today()
now = today.strftime("%Y-%m-%d")



transactions = mt940.parse('Umsaetze_2310007_22.07.2024.mta')

opening = transactions.data['final_opening_balance']
openingamount = str(opening.amount)[:-4]
openingdate = opening.date

closing = transactions.data['final_closing_balance']
closingamount = str(closing.amount)[:-4]
closingdate = closing.date

 
# create jinja env that can load template from filesystem
jinja_env = jinja2.Environment(loader = jinja2.FileSystemLoader(os.path.abspath('.')))
 
df = pd.read_excel('AllBookings.xlsx', dtype={'date': str,'amount':str})

df['CreditDebit'] = ''
df['CreditDebit'] = df['CreditDebit'].where(df['amount'].str.get(0).isin(['-']), 'CRDT')
df['CreditDebit'] = df['CreditDebit'].where(~df['amount'].str.get(0).isin(['-']), 'DBIT')

df['date'] = df['date'].str[:-9]


# Währung weg
df['amount'] = df['amount'].str[:-4]
# Vorzeichen weg
df['amount'] = df['amount'].str.replace('-','')

#df['amount'].replace('-','',inplace=True)
#df["amount"] = df["amount"].apply(lambda x: x.str.replace("-", ""))

template = jinja_env.get_template('Ntry.xml')
 
with open('FertigesXML.xml','w') as output:
    output.write(template.render(data=df,
                                 openingamount=openingamount,
                                 openingdate=openingdate,
                                 closingamount=closingamount,
                                 closingdate=closingdate
                                 ))

Jinja2 XML-Template

Das XML-Template für Jinja2 findet ihr hier:

Ntry_blog

Uwe

Uwe Ziegenhagen likes LaTeX and Python, sometimes even combined. Do you like my content and would like to thank me for it? Consider making a small donation to my local fablab, the Dingfabrik Köln. Details on how to donate can be found here Spenden für die Dingfabrik.

More Posts - Website

Python und LaTeX in einem Lauf kombinieren mit pylualatex

Mi dem pylualatex Paket gibt es eine neue Möglichkeit, Python und LaTeX miteinander zu „verheiraten“. Das Besondere an diesem Paket ist, dass es keine zwei Durchläufe benötigt, sondern nur einen einzigen.

%!TEX TS-program = Arara
% arara: lualatex: {shell: yes}
 
\documentclass{article}
 
\usepackage[executable=python.exe,localimports=false]{pyluatex} 
 
\begin{document}
 
\py{2**2**2}
 
\end{document}

Uwe

Uwe Ziegenhagen likes LaTeX and Python, sometimes even combined. Do you like my content and would like to thank me for it? Consider making a small donation to my local fablab, the Dingfabrik Köln. Details on how to donate can be found here Spenden für die Dingfabrik.

More Posts - Website

SFTP mit Python und der Paramiko-Bibliothek – Upload

Neben dem Download von Dateien klappt auch der Upload von Dateien problemlos.

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()

Uwe

Uwe Ziegenhagen likes LaTeX and Python, sometimes even combined. Do you like my content and would like to thank me for it? Consider making a small donation to my local fablab, the Dingfabrik Köln. Details on how to donate can be found here Spenden für die Dingfabrik.

More Posts - Website

SFTP mit Python und der Paramiko-Bibliothek – Download

Aktuell benötige ich Funktionen, um mit Python Dateien von SFTP Servern zu holen bzw. Dateien auf diese hochzuladen. Chat GPT hatte folgenden Code für mich, der sehr gut funktioniert.

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}")

Uwe

Uwe Ziegenhagen likes LaTeX and Python, sometimes even combined. Do you like my content and would like to thank me for it? Consider making a small donation to my local fablab, the Dingfabrik Köln. Details on how to donate can be found here Spenden für die Dingfabrik.

More Posts - Website

E-Mails senden aus Python heraus

Aktuell benötige ich Funktionalitäten in Python, um E-Mails automatisch versenden zu lassen. Über Chat-GPT habe ich mir passenden Code basteln lassen, der recht gut funktioniert.

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()

Falls der SMTP-Server keine Authentifizierung braucht, dann reicht auch das folgende

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()

Uwe

Uwe Ziegenhagen likes LaTeX and Python, sometimes even combined. Do you like my content and would like to thank me for it? Consider making a small donation to my local fablab, the Dingfabrik Köln. Details on how to donate can be found here Spenden für die Dingfabrik.

More Posts - Website

Umlaute rendern mit jinja2

Vor kurzem bin ich gefragt worden, wie man mit jinja2 Umlaute rendern kann. Grundsätzlich hatte ich auch angenommen, dass dies wegen Unicode und so kein Problem sein kann, konnte aber das aufgetretene Problem „öäüÖÜÄ,“ nachstellen.

Die Lösung war dann die folgende:

from jinja2 import Environment, BaseLoader
 
myString = 'öäü{{hello}}'
 
template = Environment(loader=BaseLoader).from_string(myString)
 
with open('render2.tex','wb') as output:
    x = template.render(hello='ÖÜÄ')
    output.write(x.encode('utf-8'))

Uwe

Uwe Ziegenhagen likes LaTeX and Python, sometimes even combined. Do you like my content and would like to thank me for it? Consider making a small donation to my local fablab, the Dingfabrik Köln. Details on how to donate can be found here Spenden für die Dingfabrik.

More Posts - Website

Historisierung von Tabellen mit Python

Ich bin aktuell dabei, mich mehr in die Anwendungsprogrammierung mit Python einzuarbeiten. Irgendwann läuft es auf ein MVC-Framework hinaus, bis dahin ist erst einmal Experimentieren angesagt. Das folgende Beispiel legt eine SQLite In-Memory Datenbank an, fügt einige Datensätze ein und ändert einen der Datensätze ab. Die Anpassungen werden dabei historisiert über das Validfrom and Validto.

import toml # handle toml files
import sqlite3
from datetime import datetime
import time
 
 
settings = toml.load('settings.toml')
dbfilename = settings['dbfilename'] 
 
conn = sqlite3.connect(":memory:")
c = conn.cursor()
 
with conn:
    c.execute(
        """
    create table if not exists
        contacts (id integer primary key, 
                  personid integer, 
                  validfrom text, 
                  validto text,
                  firstname text,
                  lastname text,
                  phonenumber text);
    """
    )
 
    now = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
 
    c.execute(f"INSERT INTO contacts (personid, validfrom, validto, firstname, lastname, phonenumber) values (1,'{now}','9999-12-31 23:59:59','Mickey','Mouse','0123-456')")
    c.execute(f"INSERT INTO contacts (personid, validfrom, validto, firstname, lastname, phonenumber) values (2,'{now}','9999-12-31 23:59:59','Donald','Duck','0123-123')")
 
    time.sleep(6)
    now = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
 
    c.execute(f"UPDATE contacts set validto = '{now}' where id = 1")
 
    time.sleep(6)
    now = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
 
    c.execute(f"INSERT INTO contacts (personid, validfrom,validto, firstname, lastname, phonenumber) values (1, '{now}','9999-12-31 23:59:59','Mickey','Mouse','0123-789')")
 
 
    result = c.execute(f"select * from contacts where validto > '2023-12-31';").fetchall()
 
    for row in result:
        print(row)

Uwe

Uwe Ziegenhagen likes LaTeX and Python, sometimes even combined. Do you like my content and would like to thank me for it? Consider making a small donation to my local fablab, the Dingfabrik Köln. Details on how to donate can be found here Spenden für die Dingfabrik.

More Posts - Website

Aus Python heraus Pakete installieren

import pip
 
def install(package):
    if hasattr(pip, 'main'):
        pip.main(['install', package])
    else:
        pip._internal.main(['install', package])
 
 
# Beispiel für spyder ide
if __name__ == '__main__':
    install('spyder')

Uwe

Uwe Ziegenhagen likes LaTeX and Python, sometimes even combined. Do you like my content and would like to thank me for it? Consider making a small donation to my local fablab, the Dingfabrik Köln. Details on how to donate can be found here Spenden für die Dingfabrik.

More Posts - Website

Mit Python mehrfache Leerzeichen durch ein einziges ersetzen

Der folgende Trick hat mir einige Klimmzüge erspart. In einer Textdatei gab es an diversen Stellen mehrfache Leerzeichen, die ich durch ein einzelnes ersetzen wollte.

Ich hätte jetzt einfach so lange doppelte Leerzeichen durch ein einzelnes ersetzen können, bis keine doppelten Leerzeichen mehr vorhanden sind, über einen regular expression geht es aber viel eleganter.

import re
 
s = 'a     b     c'
 
print(re.sub('\s+',' ',s))

Uwe

Uwe Ziegenhagen likes LaTeX and Python, sometimes even combined. Do you like my content and would like to thank me for it? Consider making a small donation to my local fablab, the Dingfabrik Köln. Details on how to donate can be found here Spenden für die Dingfabrik.

More Posts - Website

Klammern in Cognos-Reports zählen

Ich hatte vor kurzem das Problem (oder die „Challenge“), einen sehr komplexen Cognos-Report anpassen zu müssen. In mehreren dutzend Formeln mussten Formeln angepasst werden, den Formel-Check zur Prüfung meiner Anpassungen konnte ich jedoch nicht nutzen, da die Datenüberprüfung unendlich lang gedauert hätte (schlechtes Report-Design…).

Mit Python gab es aber eine einfache und elegante Lösung, die mit dem Export des Report-Designs in eine XML-Datei begann.
In der XML-Datei fanden sich dann Schnipsel wie

<expression>abs(total((if ([Database].[storedProc].[ColumnID]=6) then ([Database].[storedProc].[Amount]) else (0))))</expression>

Mit dem folgenden Programm-Code konnte ich dann innerhalb der expression-Tags einfach die öffnenden und schließenden Klammern zählen. Wenn die Zahl der öffnenden Klammern nicht der Zahl der schließenden Klammern entsprach, war die Formel noch falsch.

import re
buffer = open('./Report.xml','r').read()
 
results = re.findall(r"(<expression>)(.*)(</expression>)",buffer,re.MULTILINE)
 
for result in results:
    expression = result[1]
    opened = expression.count('(')
    closed = expression.count(')')
 
    if opened != closed:
        print(expression)

Uwe

Uwe Ziegenhagen likes LaTeX and Python, sometimes even combined. Do you like my content and would like to thank me for it? Consider making a small donation to my local fablab, the Dingfabrik Köln. Details on how to donate can be found here Spenden für die Dingfabrik.

More Posts - Website