Posts tagged ‘SQL’

SQL Inserts auf die clevere Art erzeugen mit pandas

Hier ein cleveres Beispiel aus dem Internet (Quelle habe ich leider nicht mehr) dafür, wie man mit pandas einfach SQL Inserts erzeugen kann. In der Datei Daten.csv finden sich die einzufügenden Daten zusammen mit den entsprechenden Spaltennamen der Datenbanktabelle.

Über df.columns bekommen wir dann beim Insert die benötigten Spaltennamen aus dem DataFrame geliefert, über das Tuple der Zeilenwerte row.values die einzufügenden Werte.

import pandas as pd
 
df = pd.read_csv('Daten.csv', sep=';', decimal=',')
 
with open('Statements2.sql', 'w') as o:
    for index, row in df.iterrows():
        o.write('INSERT INTO aaaaaa('+ str(', '.join(df.columns))+ ') VALUES '+ str(tuple(row.values))+';\n')

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

Overviews of SQL Joins (with LaTeX code)

Here’s a short overview of SQL joins, find the source code of the document attached to the PDF (use Adobe Reader to access it).

SQL Joins

\documentclass[12pt]{article}
\usepackage{xcolor}
\usepackage{arev}
\usepackage{attachfile}
 
 
\usepackage{tikz}
\usetikzlibrary{shapes,snakes}
\def\firstcircle{(0,0) circle (2cm)}
\def\secondcircle{(0:3cm) circle (2cm)}
 
\colorlet{circle edge}{blue!50}
\colorlet{circle area}{blue!20}
\colorlet{white area}{white}
 
\tikzset{
filled/.style={fill=circle area, draw=circle edge, thick},
outline/.style={draw=circle edge, thick},
white/.style={fill=white area, draw=circle edge, very thick}
}
 
\begin{document}
 
\textattachfile{\jobname.tex}{\LaTeX~Code}
 
\section{Inner Join}
 
Inner joins return those records, that are present in table 1 and table 2.
 
\begin{center}
\begin{tikzpicture}[scale=1, every node/.style={scale=1}] % Inner Join
    \begin{scope}
        \clip \firstcircle;
        \fill[filled] \secondcircle;
    \end{scope}
    \draw[outline] \firstcircle node {A};
    \draw[outline] \secondcircle node {B};
\end{tikzpicture}
\end{center}
 
\section{Left Join}
 
Every record from table 1 is returned, regardless if it has a matching record in table 2. If the record has a matching record in table 2 this record from table 2 is returned as well.
 
\begin{center}
\begin{tikzpicture}[scale=1, every node/.style={scale=1}] % Left Join
    \begin{scope}
        \clip \firstcircle;
        \draw[filled] \firstcircle node {A}
                                     \secondcircle;
    \end{scope}
    \draw[outline] \firstcircle
                   \secondcircle node {B};
\end{tikzpicture}
\end{center}
 
\clearpage
 
\section{Left Outer Join}
 
Those records from table 1 are returned, that do not have a match in table 2.
 
\begin{center}
\begin{tikzpicture}[scale=1, every node/.style={scale=1}] % Left Outer Join
    \draw[filled] \firstcircle node {A};
    \draw[white] \secondcircle node {B};
    \draw[outline] \firstcircle node {A};
\end{tikzpicture}
\end{center}
 
\section{Right Join}
 
Every record from table 2 is returned, regardless if it has a matching record in table 1. If the record has a matching record in table 1 this record from table 1 is returned as well.
 
\begin{center}
\begin{tikzpicture}[scale=1, every node/.style={scale=1}] % Right Join
    \begin{scope}
        \clip \secondcircle;
        \draw[filled] \firstcircle \secondcircle node {B};
    \end{scope}
    \draw[outline] \firstcircle node {A} \secondcircle;
\end{tikzpicture}
 
\end{center}
 
\clearpage
 
\section{Right Outer Join}
 
Those records from table 2 are returned, that do not have a match in table 1.
 
\begin{center}
\begin{tikzpicture}[scale=1, every node/.style={scale=1}] % Right Join 2
    \draw[filled]   \secondcircle node {B};
    \draw[white]  \firstcircle node {A};
    \draw[outline] \secondcircle node {B};
\end{tikzpicture}
\end{center}
 
 
 
\section{Theta Join}
 
Every row from dataset 1 is multiplied by every row of dataset 2. Usually a mistake unless you really want this Cartesian product.
 
\begin{center}
\begin{tikzpicture}[scale=1, every node/.style={scale=1}] % Theta
    \draw[filled] \firstcircle node {A} \secondcircle node {B};
\end{tikzpicture}
\end{center}
 
\clearpage
 
\section{Full Outer}
 
Returns all records from table 1 and table 2 regardless if they have matches in the other table. Records, that do match, are linked together via key. 
 
\begin{center}
\begin{tikzpicture}[scale=1, every node/.style={scale=1}] % Full Outer
    \draw[filled] \firstcircle node {A};
    \draw[filled] \secondcircle node {B};
    \begin{scope}
        \clip \firstcircle;
         \fill[white] \secondcircle;
        \draw[outline] \secondcircle;  
        \draw[outline] \firstcircle;  
    \end{scope}
\end{tikzpicture}
\end{center}
 
\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

SQL: Nur den aktuellsten Eintrag aus einer gejointen Tabelle laden

Ich habe seit ein paar Monaten wieder verstärkt Gelegenheit, meine rostigen SQL-Kenntnisse aufzufrischen. Kürzlich bin ich dabei an einer kleinen Herausforderung vorbeigekommen, die mich doch ein paar Minuten mehr beschäftigt hat. Gegeben sind zwei Tabellen, Namen und Adressen.

Namen

ID Name
1 Anna Amsel
2 Bernd Borstel
3 Cesar Causel
4 Dagmal Dack

Adressen

ID NameID Adresse
1 1 Amselweg 2
2 2 Badgasse 34
3 3 Chausseeweg 123
4 3 Dorfstraße 34
5 1 Alabasterpfad 32

ID ist jeweils der Primary Key, NameID der Fremdschlüssel von Adressen auf Namen. Ziel der Übung ist es, alle Namen mit der (sofern vorhanden) aktuellsten Adresse abzufragen.

Ein inner join bringt ganz klar das falsche Ergebnis, denn Dagmar Dack fehlt in der Ergebnismenge, Anna und Cesar sind zweimal vertreten.

SELECT n.Name, a.Adresse FROM Namen n JOIN Adressen a ON n.ID = a.NameID ;

Ein left join muss her, was das Ergebnis aber nur ein wenig besser macht: Dagmar ist jetzt drin, Anna und Cesar aber immer noch doppelt:

SELECT n.Name, a.Adresse FROM Namen n JOIN Adressen a ON n.ID = a.NameID ;

Beholfen habe ich mir dann mit einem Subselect:

SELECT n.Name, a.Adresse FROM Namen n
LEFT JOIN (SELECT MAX(ID) ID, MIN(NameID) NameID, MIN(Adresse) Adresse FROM Adressen GROUP BY NameID) a
ON n.ID = a.NameID

Ergebnis

Name Adresse
Anna Amsel Alabasterpfad 32
Bernd Borstel Badgasse 34
Cesar Causel Chausseeweg 123
Dagmal Dack NULL

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

Blobs auf der Festplatte speichern

Hier ein Beispiel, wie man Blobs auf die Festplatte extrahieren kann:

http://blogs.technet.com/b/austria/archive/2010/02/25/wie-bekomme-ich-bin-re-dokumente-blobs-aus-dem-sql-server.aspx

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

Kumulative Summe mit T-SQL

Hier ein kurzes Beispiel, wie man mit T-SQL eine kumulative Summe ausrechnet.

CREATE TABLE #test(datum DATE, VALUE INT)
 
INSERT INTO #test VALUES('2010-01-01',10)
INSERT INTO #test VALUES('2010-01-02',20)
INSERT INTO #test VALUES('2010-01-02',30)
 
SELECT t1.*, (SELECT SUM(VALUE) FROM #test t2 WHERE t2.datum<=t1.datum) [cumulative amount]
FROM #test t1
ORDER BY t1.datum
DROP TABLE #test

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

T-SQL: Median und Ausreißer berechnen

Unter http://www.insidesql.org/blogs/frankkalis/2004/07/13/median-berechnen und http://quantmeditate.blogspot.com/2005/03/computing-interquartile-range.html habe ich Informationen zur Berechnung von Median und Quartilen in T-SQL gefunden.

DROP TABLE #data
CREATE TABLE #data(NUMBER FLOAT)
 
INSERT INTO #data VALUES(1.0);INSERT INTO #data VALUES(2.0);
INSERT INTO #data VALUES(3.0);INSERT INTO #data VALUES(4.0);
INSERT INTO #data VALUES(5.0);INSERT INTO #data VALUES(6.0);
INSERT INTO #data VALUES(7.0);INSERT INTO #data VALUES(8.0);
INSERT INTO #data VALUES(9.0);INSERT INTO #data VALUES(10.0);
INSERT INTO #data VALUES(11.0);INSERT INTO #data VALUES(12.0);
INSERT INTO #data VALUES(13.0);INSERT INTO #data VALUES(14.0);
INSERT INTO #data VALUES(15.0);INSERT INTO #data VALUES(16.0);
INSERT INTO #data VALUES(17.0);INSERT INTO #data VALUES(18.0);
INSERT INTO #data VALUES(19.0);INSERT INTO #data VALUES(20.0);
INSERT INTO #data VALUES(21.0);INSERT INTO #data VALUES(22.0);
INSERT INTO #data VALUES(23.0);INSERT INTO #data VALUES(24.0);
INSERT INTO #data VALUES(25.0);INSERT INTO #data VALUES(26.0);
INSERT INTO #data VALUES(27.0);INSERT INTO #data VALUES(28.0);
INSERT INTO #data VALUES(29.0);INSERT INTO #data VALUES(30.0);
INSERT INTO #data VALUES(31.0);INSERT INTO #data VALUES(32.0);
INSERT INTO #data VALUES(33.0);INSERT INTO #data VALUES(34.0);
INSERT INTO #data VALUES(35.0);INSERT INTO #data VALUES(36.0);
INSERT INTO #data VALUES(37.0);INSERT INTO #data VALUES(38.0);
INSERT INTO #data VALUES(39.0);INSERT INTO #data VALUES(40.0);
INSERT INTO #data VALUES(-100.0);INSERT INTO #data VALUES(100.0);
 
DECLARE @median FLOAT
DECLARE @perc25 FLOAT
DECLARE @perc75 FLOAT
DECLARE @iqr FLOAT
 
SET @median = (SELECT   MAX(NUMBER) AS Median FROM (SELECT TOP 50 PERCENT NUMBER FROM #data ORDER BY NUMBER) a)
SET @perc25 = (SELECT   MAX(NUMBER) AS Median FROM (SELECT TOP 25 PERCENT NUMBER FROM #data ORDER BY    NUMBER) a)
SET @perc75 = (SELECT   MAX(NUMBER) AS Median FROM (SELECT TOP 75 PERCENT NUMBER FROM #data ORDER BY    NUMBER) a)
 
print @median
print @perc25
print @perc75
 
SET @iqr = @perc75-@perc25
print 'IQR'
print @iqr
 
SELECT * FROM #data WHERE NUMBER <  (@median + 1.5* @iqr) AND 
NUMBER >  (@median - 1.5* @iqr)
 
print 'number of outliers'
SELECT COUNT(*) FROM #data WHERE NUMBER <  (@median - 1.5* @iqr)
OR NUMBER >  (@median + 1.5* @iqr)

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

Powershell und SQL Insert

Auf http://richardspowershellblog.wordpress.com/2007/04/19/insert-values-to-a-sql-server-table/ habe ich ein kurzes Statement gefunden, um per Powershell Einträge in einer SQL Datenbank vorzunehmen.

# open connection to the server
$conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=localhost; Initial Catalog=Posh; Integrated Security=SSPI")
$conn.Open()
 
# create command object
$cmd = $conn.CreateCommand()
# create statement
$cmd.CommandText ="INSERT myTable VALUES ('Hello', 'World', 123)"
# execute command
$cmd.ExecuteNonQuery()
# close connection
$conn.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

Mit C# Bilder aus einer Datenbank lesen

Heute hatte ich die Aufgabe, mal Bilder binär in eine Datenbank zu übertragen, was mit dem bulk Feature des SQL Servers auch recht gut klappt, sofern man den Dateipfad richtig angibt. Liegen die Dateien nicht auf dem Datenbank-Server, so sind UNC-Pfade zu verwenden.

-- Erstelle Tabelle
CREATE TABLE [dbo].[Bilder](
	[ID] [INT] NULL,
	[ID2] [INT] NULL,
	[DATA] [image] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
 
-- Füge Bild ein
INSERT INTO Bilder 
VALUES (1,2,(SELECT binary_data 
FROM OPENROWSET(Bulk 'c:/test.jpg', SINGLE_BLOB) 
AS F(binary_data)))

Für das Testen des Uploads habe ich dann bei http://support.microsoft.com/kb/317701 entsprechenden Code gefunden, den ich innerhalb von fünf Minuten am Laufen hatte und der auf Knopf-Druck das Bild aus der Datenbank in eine Picturebox liest. Mission accomplished 😉

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 Powershell SQL Server abfragen

Habe mir heut mal angeschaut, wie man mit Powershell Daten aus einer SQL Server Datenbank abfragen kann. Wie man es allgemein macht, muss ich noch rausfinden, folgendes (von http://thepowershellguy.com/blogs/posh/archive/2008/02/28/listing-all-the-databases-from-a-sql-server-from-powershell.aspx) läuft aber bei mir:

    $Server = '127.0.0.1' 
    $Database = 'Skript'
    $sqlCon = New-Object Data.SqlClient.SqlConnection
    $sqlCon.ConnectionString = "Data Source=$server;Integrated Security=True;Initial Catalog=$Database"
    $sqlCon.open()
    $sqlCmd = New-Object Data.SqlClient.SqlCommand
    $sqlCmd.Connection = $sqlCon
    $sqlCmd.CommandType = CommandType.Text
    $sqlCmd.CommandText = "SELECT Wert FROM Test"
    $sqlCmd.ExecuteReader() |% {$_.GetString(0)}

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

Großgeschriebene String mit SQL finden

Hatte heute das Problem, dass ich alle Namen finden wollte, die komplett in Großbuchstaben geschrieben waren. Das Problem „alle Buchstaben groß“ konnte zu „mehr als der Anfangsbuchgstabe groß“ umgewandelt werden, was die Lösung erleichterte. Geprüft wird einfach, ob der zweite Buchstabe im Namen groß ist:

SELECT [Name]
FROM [DATABASE].[namestable]
WHERE ASCII(SUBSTRING([NAME],2,1)) BETWEEN 65 AND 90

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