Archive for the ‘Programmierung’ Category.
T-SQL: Mit Daten rechnen
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 |
T-SQL Rowcount von Tabellen ermitteln
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;
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() |
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}") |
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() |
Produktverfügbarkeit checken mit Python
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') |
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')) |
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) |
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') |