Archive for the ‘Programmierung’ Category.
2024-12-14, 11:18
Hier ein paar Beispiele, wie man mit icecream print() Ausgaben ersetzen kann.
"""
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)) |
"""
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))
2024-09-07, 09:23
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
2024-03-17, 23:22
Text lässt sich „einfach“ umkehren in Excel, auch ohne VBA.

Wenn der umzukehrende Text in Zelle A1 steht, dann hilft die folgende Formel
=TEXTVERKETTEN("";WAHR;TEIL(A1;LÄNGE(A1)-ZEILE(INDIREKT("1:"&LÄNGE(A1)))+1;1))
Mit Strg-Shift-Enter abschließen, um eine Array-Formel zu erstellen.
2024-03-03, 10:57
Hier eine Übersicht zum Errechnen von Datumswerten in T-SQL
| Datum |
SQL |
| Today |
getdate() |
| Yesterday |
DATEADD(day, -1, CAST(GETDATE())) |
| Tomorrow |
DATEADD(day, 1, CAST(GETDATE())) |
| First day of the previous month |
DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0) |
| Last day of the previous month |
DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, -1) --Last Day of previous month |
2024-02-24, 16:59
Stackoverflow (https://stackoverflow.com/questions/2221555/how-to-fetch-the-row-count-for-all-tables-in-a-sql-server-database) hatte gestern interessanten Code für mich, um den Rowcount aller Tabellen in einer MS SQL Server DB zu ermitteln:
SELECT o.NAME,
i.rowcnt
FROM sysindexes AS i
INNER JOIN sysobjects AS o ON i.id = o.id
WHERE i.indid < 2 AND OBJECTPROPERTY(o.id, 'IsMSShipped') = 0;
Category:
SQL,
Programmierung |
Kommentare deaktiviert für T-SQL Rowcount von Tabellen ermitteln
2024-01-13, 11:14
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() |
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()
2024-01-13, 11:12
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}") |
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}")
2024-01-13, 11:08
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() |
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() |
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()
2023-01-29, 12:28
Aktuell warte ich auf die Verfügbarkeit eines bestimmten Werkzeugs bei einem Online-Händler. Das geht auch gut mit Python 🙂
Man könnte das noch weiter automatisieren und beispielsweise eine E-Mail verschicken, wenn sich der Status ändert.
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') |
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')
2022-12-25, 22:38
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')) |
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'))