Jupyter notebook notebook.ipynb

Views: ¹³⁶

Kernel: Python 2 (Ubuntu Linux)

DB Zugang

Zugang zu einer MSSQL Datenbank. Bei Amazon AWS Trail gehostet.

In [3]:

import pymssql

db ='dbatwork.cckoaz5lxapo.eu-central-1.rds.amazonaws.com:1433'

conn = pymssql.connect(host=db, user='adm', password='helloworld',database='master')
try:
    cur = conn.cursor()
    print("done")
    
    #cur.execute("""USE NewDB;""")
except pymssql.InterfaceError: 
    print("Exception while getting connection.") 
    #print 'la'

ImportErrorTraceback (most recent call last)
<ipython-input-3-5a0e93ffdcbc> in <module>()
----> 1 import pymssql
      2 
      3 db ='dbatwork.cckoaz5lxapo.eu-central-1.rds.amazonaws.com:1433'
      4 
      5 conn = pymssql.connect(host=db, user='adm', password='helloworld',database='master')
ImportError: No module named pymssql

In [6]:

%lsmagic

Available line magics:
%alias  %alias_magic  %autocall  %automagic  %autosave  %bookmark  %cat  %cd  %clear  %colors  %config  %connect_info  %cp  %debug  %dhist  %dirs  %doctest_mode  %ed  %edit  %env  %gui  %hist  %history  %killbgscripts  %ldir  %less  %lf  %lk  %ll  %load  %load_ext  %loadpy  %logoff  %logon  %logstart  %logstate  %logstop  %ls  %lsmagic  %lx  %macro  %magic  %man  %matplotlib  %mkdir  %more  %mv  %notebook  %page  %pastebin  %pdb  %pdef  %pdoc  %pfile  %pinfo  %pinfo2  %popd  %pprint  %precision  %profile  %prun  %psearch  %psource  %pushd  %pwd  %pycat  %pylab  %qtconsole  %quickref  %recall  %rehashx  %reload_ext  %rep  %rerun  %reset  %reset_selective  %rm  %rmdir  %run  %save  %sc  %set_env  %store  %sx  %system  %tb  %time  %timeit  %unalias  %unload_ext  %who  %who_ls  %whos  %xdel  %xmode

Available cell magics:
%%!  %%HTML  %%SVG  %%bash  %%capture  %%debug  %%file  %%html  %%javascript  %%js  %%latex  %%perl  %%prun  %%pypy  %%python  %%python2  %%python3  %%ruby  %%script  %%sh  %%svg  %%sx  %%system  %%time  %%timeit  %%writefile

Automagic is ON, % prefix IS NOT needed for line magics.

In [22]:

#faster connection then pymsql to db with less overhead
#no cursor() => cant use pandas
import _mssql

def my_msg_handler(msgstate, severity, srvname, procname, line, msgtext):
    """
    Our custom handler -- It simpy prints a string to stdout assembled from
    the pieces of information sent by the server.
    """
    print("my_msg_handler: msgstate = %d, severity = %d, procname = '%s', "
          "line = %d, msgtext = '%s'" % (msgstate, severity, procname,
                                         line, msgtext))
#how to fetch print etc

#cur.connection.set_msghandler(my_msg_handler)

conn2 = _mssql.connect(server=db, user='adm', password='helloworld',database='master')
try:
    conn2.set_msghandler(my_msg_handler)  # Install our custom handler
    conn2.execute_non_query("USE NewDB")  # It gets called at this point
finally:
    conn2.close()
#print("done")

ImportErrorTraceback (most recent call last)
<ipython-input-22-a6f2816fa368> in <module>()
      1 #faster connection then pymsql to db with less overhead
      2 #no cursor() => cant use pandas
----> 3 import _mssql
      4 
      5 def my_msg_handler(msgstate, severity, srvname, procname, line, msgtext):
ImportError: No module named _mssql

In [502]:

#conn2.execute_non_query("USE NewDB")

DB Erstellung

In [124]:

#DONT TOUCH THIS IF NOT NEEDED!
#cur.execute("""USE master""")
#cur.execute("""CREATE DATABASE NewDB;""")
#cur.execute("""COMMIT """)
#cur.execute("""USE NewDB;""")

Die pandas Bibliothek dient hier zur alternativen Datenverarbeitung. Zweiterer Befehl wählt die gewünschte Datenbank aus.

In [11]:

%%python3 
import pandas as pd
#cur.execute_query("""USE NewDB;""")

cur.execute("""USE NewDB;""")

DB Test

Der folgende Code dient zum puren Test der Datenbank.

In [1]:

#man beachte alter ist nen sql statement und darf kein atributname sein
cur.execute(""" IF OBJECT_ID('persons', 'U') IS NOT NULL
        DROP TABLE persons
    CREATE TABLE persons (
        P_Alter INT NOT NULL,
        name VARCHAR(100),
        id INT NOT NULL IDENTITY(1,1),
        PRIMARY KEY(id)
    );""")


cur.execute("""INSERT INTO persons VALUES (80,'Konrad Adenauer')""")
cur.execute("""INSERT INTO persons VALUES (80,'Ludwig Erhard')""")

NameErrorTraceback (most recent call last)
<ipython-input-1-24b28b859ed1> in <module>()
      1 #man beachte alter ist nen sql statement und darf kein atributname sein
----> 2 cur.execute(""" IF OBJECT_ID('persons', 'U') IS NOT NULL
      3         DROP TABLE persons
      4     CREATE TABLE persons (
      5         P_Alter INT NOT NULL,
NameError: name 'cur' is not defined

Multi INSERT exec

In [253]:

cur.execute("""INSERT INTO persons VALUES (13,'Dr. Indiana Jones')""")
cur.execute("""INSERT INTO persons VALUES (4,'Dr. Watson')""")
#Lara Croft is missing cause female quota 
cur.executemany(
    "INSERT INTO persons VALUES (%d, %s)",
    [(20, 'John Wayne'),
     (215, 'Bruce Wayne'),
     (21, 'Mickey Mouse'),
     (30, 'Popeye der Seemann'),
     (40, 'Henry Maske')])

Get Data

In [522]:

cur.execute("""SELECT * FROM persons""")
row = cur.fetchall()
#while row:
  #  print("ID=%d, Name=%s" % (row[0], row[1]))
  #  row = cur.fetchone()
row

[(80, 'Konrad Adenauer', 1),
 (80, 'Ludwig Erhard', 2),
 (13, 'Dr. Indiana Jones', 3),
 (4, 'Dr. Watson', 4),
 (20, 'John Wayne', 5),
 (215, 'Bruce Wayne', 6),
 (21, 'Mickey Mouse', 7),
 (30, 'Popeye der Seemann', 8),
 (40, 'Henry Maske', 9)]

In [520]:

sql = """SELECT * FROM persons"""
df = pd.read_sql(sql, conn)
df

   P_Alter                name  id
     80     Konrad Adenauer   1
     80       Ludwig Erhard   2
     13   Dr. Indiana Jones   3
      4          Dr. Watson   4
     20          John Wayne   5
    215         Bruce Wayne   6
     21        Mickey Mouse   7
     30  Popeye der Seemann   8
     40         Henry Maske   9

View test

In [238]:

cur.execute("""DROP VIEW test""")
cur.execute("""CREATE VIEW test AS
SELECT name FROM persons""")

In [249]:

#cur.execute("""SELECT name FROM test""")
# View ohne alter angabe
sql = """SELECT * FROM test"""
df = pd.read_sql(sql, conn)
df

                 name
   Konrad Adenauer
     Ludwig Erhard
         Dr. Jones
        Dr. Watson
        John Wayne
       Bruce Wayne
Popeye der Seemann
       Henry Maske

Test procedures

In [262]:

cur.execute("""DROP PROCEDURE getNames""")
cur.execute("""CREATE PROCEDURE getNames
            @Name VARCHAR(30)           
        AS          
            PRINT N\'Test.\'; 
            SET NOCOUNT ON;
            SELECT * FROM persons WHERE name= @Name""")

In [263]:


sql = """EXEC getNames @Name = "Dr. Watson";"""
df = pd.read_sql(sql, conn)
df

	P_Alter	name	id
0	4	Dr. Watson	4

In [416]:

cur.execute("""DROP PROCEDURE IF EXISTS findName""")
#print not working yet
cur.execute("""CREATE PROCEDURE findName @nm VARCHAR(20) 
AS   
IF (SELECT id FROM persons WHERE name = @nm)  <> NULL
    BEGIN 
        PRINT N'Test';
        RETURN 
    END  
ELSE  
    BEGIN    
        SELECT name FROM persons WHERE name = @nm
        RETURN 
    END;  
    """)

In [427]:

sql = """EXEC findName  @nm = 'Dr. Watson';"""
cur.execute(sql)
#cur
row = cur.fetchmany()
row
#cur.fetchall()
#df = pd.read_sql(sql, conn)
#df

[('Dr. Watson',)]

In [529]:

sql = """EXEC findName  @nm = 'NoOne';"""

try:
    data = cur.execute(sql)
    row = cur.fetchall()
    print(row)
except Exception as e:
    (error_code, error_message) = e
    print(error_message)

cur.execute(sql)
row = cur.fetchall()
#while row:
  #  print("ID=%d, Name=%s" % (row[0], row[1]))
  #  row = cur.fetchone()
print(row)
#print(data)

[]
[]

Bash example

In [195]:

%%bash
uname -a
echo hallo welt ich mag züge :P

Linux compute7-us 4.2.0-42-generic #49-Ubuntu SMP Tue Jun 28 21:26:26 UTC 2016 x86_64 x86_64 x86_64 GNU/Linux
hallo welt ich mag züge :P

DB Zugriff Beispiel mit pandas:

In [160]:

sql = """SELECT * FROM persons"""
df = pd.read_sql(sql, conn)
df

	id	name
0	0	Dr. Jones
1	1	Dr. Watson

DB Medienverleih Implementierung

DROP CONSTRAINTS

In [757]:

cur.execute(""" 
                IF OBJECT_ID('FK_film_medium') IS NOT NULL 
                ALTER TABLE Film DROP FK_film_medium
                
                IF OBJECT_ID('FK_ebook_medium') IS NOT NULL 
                ALTER TABLE EBook DROP FK_ebook_medium
                
                IF OBJECT_ID('FK_spiel_medium') IS NOT NULL 
                ALTER TABLE Spiel DROP FK_spiel_medium
            
                IF OBJECT_ID('FK_kunde_adresse') IS NOT NULL 
                ALTER TABLE Kunde DROP FK_kunde_adresse
                
                IF OBJECT_ID('FK_ausleihe_exemplar') IS NOT NULL 
                ALTER TABLE ausleihe DROP FK_ausleihe_exemplar
                IF OBJECT_ID('FK_ausleihe_kunde') IS NOT NULL 
                ALTER TABLE ausleihe DROP FK_ausleihe_kunde
                
                IF OBJECT_ID('FK_hat_medium') IS NOT NULL 
                ALTER TABLE hat DROP FK_hat_medium
                IF OBJECT_ID('FK_hat_exemplar') IS NOT NULL 
                ALTER TABLE hat DROP FK_hat_exemplar
                
                IF OBJECT_ID('FK_vorbestellt_kunde') IS NOT NULL 
                ALTER TABLE vorbestellt DROP FK_vorbestellt_kunde
                IF OBJECT_ID('FK_vorbestellt_medium') IS NOT NULL 
                ALTER TABLE vorbestellt DROP FK_vorbestellt_medium
            """)

'''
IF OBJECT_ID('FK_zugestellt_rechnung') IS NOT NULL 
ALTER TABLE zugestellt DROP FK_zugestellt_rechnung
IF OBJECT_ID('FK_zugestellt_kunde') IS NOT NULL 
ALTER TABLE zugestellt DROP FK_zugestellt_kunde
'''

"\nIF OBJECT_ID('FK_zugestellt_rechnung') IS NOT NULL \nALTER TABLE zugestellt DROP FK_zugestellt_rechnung\nIF OBJECT_ID('FK_zugestellt_kunde') IS NOT NULL \nALTER TABLE zugestellt DROP FK_zugestellt_kunde\n"

Create Tables

In [758]:


# 6. Medium
cur.execute(""" IF OBJECT_ID('Medium', 'U') IS NOT NULL
        DROP TABLE Medium
    CREATE TABLE Medium (
        M_ID INT NOT NULL IDENTITY(1,1),
        Titel VARCHAR(50) NOT NULL,
        Genre VARCHAR(50) NOT NULL,
        Erscheinungsdatum DATE NOT NULL,
        
        PRIMARY KEY (M_ID),
        UNIQUE(Titel, Genre, Erscheinungsdatum)
    )""")

# 7. Film
cur.execute(""" IF OBJECT_ID('Film', 'U') IS NOT NULL
        DROP TABLE Film
    CREATE TABLE Film (
        M_ID INT NOT NULL,
        Spieldauer INT NOT NULL,
        FSK INT NOT NULL,
        Regisseur VARCHAR(50) NOT NULL,
        
        PRIMARY KEY(M_ID),
        CONSTRAINT FK_film_medium FOREIGN KEY (M_ID) REFERENCES Medium
    )""")

# 8. EBook
# ISBN-13
cur.execute(""" IF OBJECT_ID('EBook', 'U') IS NOT NULL
        DROP TABLE EBook
    CREATE TABLE EBook (
        M_ID INT NOT NULL,
        Autor VARCHAR(50) NOT NULL,
        ISBN VARCHAR(13) NOT NULL,
        
        PRIMARY KEY(M_ID),
        UNIQUE(M_ID),
        CONSTRAINT FK_ebook_medium FOREIGN KEY (M_ID) REFERENCES Medium
    )""")

# 9. Spiel
cur.execute(""" IF OBJECT_ID('Spiel', 'U') IS NOT NULL
        DROP TABLE Spiel
    CREATE TABLE Spiel (
        M_ID INT NOT NULL,
        USK INT NOT NULL,
        Herausgeber VARCHAR(50) NOT NULL,
        
        PRIMARY KEY(M_ID),
        UNIQUE(M_ID),
        CONSTRAINT FK_spiel_medium FOREIGN KEY (M_ID) REFERENCES Medium
    )""")


# 1. Rechnung
#cur.execute(""" IF OBJECT_ID('Rechnung', 'U') IS NOT NULL
#        DROP TABLE Rechnung
#    CREATE TABLE Rechnung (
#        R_ID INT NOT NULL,
#        Rechnungsdatum DATE NOT NULL,
#        Rechnungsbetrag DECIMAL(5,2),
#        
#        PRIMARY KEY(R_ID)
#    )""")


# Adresse
cur.execute(""" IF OBJECT_ID('Adresse', 'U') IS NOT NULL
        DROP TABLE Adresse
    CREATE TABLE Adresse (
        Adr_ID INT NOT NULL IDENTITY(1,1),
        Strasse VARCHAR(50) NOT NULL,
        Hausnummer INT NOT NULL, 
        PLZ VARCHAR(5) NOT NULL,
        Ort VARCHAR(50) NOT NULL,
        
        PRIMARY KEY (Adr_ID),
        UNIQUE(Strasse, Hausnummer, PLZ, Ort)
    )""")


# 3. Kunde
# Hausnummer mit Zusatz? z.B. 4b
cur.execute(""" IF OBJECT_ID('Kunde', 'U') IS NOT NULL
        DROP TABLE Kunde
    CREATE TABLE Kunde (
        K_ID INT NOT NULL IDENTITY(1,1),
        KundenName VARCHAR(40) NOT NULL,
        KundenVorname VARCHAR(40) NOT NULL,
        Nutzername VARCHAR(40) NOT NULL,
        Passwort VARCHAR(40) NOT NULL,
        EmailAdresse VARCHAR(80) NOT NULL,
        TelefonNr VARCHAR(20),
        KundeAktiv BIT NOT NULL,
        Bankeinzug BIT NOT NULL,
        GebDatum DATE NOT NULL,
        Adr_ID INT NOT NULL
        
        PRIMARY KEY(K_ID),
        UNIQUE(KundenName, KundenVorname, Adr_ID),
        UNIQUE(Nutzername),
        UNIQUE(EmailAdresse),
        CONSTRAINT FK_kunde_adresse FOREIGN KEY (Adr_ID) REFERENCES Adresse
    )""")

#4. Exemplar
cur.execute(""" IF OBJECT_ID('Exemplar', 'U') IS NOT NULL
        DROP TABLE Exemplar
    CREATE TABLE Exemplar (
        E_InventarNr INT NOT NULL IDENTITY(1,1),
        Zustand INT NOT NULL,
        Status INT NOT NULL,
        
        PRIMARY KEY (E_InventarNr)
    )""")

# zugestellt
#cur.execute(""" IF OBJECT_ID('zugestellt', 'U') IS NOT NULL
#        DROP TABLE zugestellt
#    CREATE TABLE zugestellt (
#        R_ID INT NOT NULL,
#        K_ID INT NOT NULL,
#        
#        PRIMARY KEY(R_ID, K_ID),
#        CONSTRAINT FK_zugestellt_rechnung FOREIGN KEY(R_ID) REFERENCES Rechnung,
#        CONSTRAINT FK_zugestellt_kunde FOREIGN KEY(K_ID) REFERENCES Kunde
#    )""")

# ausleihe
cur.execute(""" IF OBJECT_ID('ausleihe', 'U') IS NOT NULL
        DROP TABLE ausleihe
    CREATE TABLE ausleihe (
        E_InventarNr INT NOT NULL,
        K_ID INT NOT NULL,
        Entleihdatum DATE NOT NULL DEFAULT GETDATE(),
        Frist DATE,
        Rueckgabedatum DATE,
        Gemahnt BIT,
        Ausleihbetrag Decimal(5,2),
        
        PRIMARY KEY(E_InventarNr, Entleihdatum),
        CONSTRAINT FK_ausleihe_exemplar FOREIGN KEY(E_InventarNr) REFERENCES Exemplar,
        CONSTRAINT FK_ausleihe_kunde FOREIGN KEY(K_ID) REFERENCES Kunde
    )""")

# hat
cur.execute(""" IF OBJECT_ID('hat', 'U') IS NOT NULL
        DROP TABLE hat
    CREATE TABLE hat (
        M_ID INT NOT NULL,
        E_InventarNr INT NOT NULL,
        
        PRIMARY KEY(E_InventarNr),
        CONSTRAINT FK_hat_medium FOREIGN KEY(M_ID) REFERENCES Medium,
        CONSTRAINT FK_hat_exemplar FOREIGN KEY(E_InventarNr) REFERENCES Exemplar
    )""")

# vorbestellt
cur.execute(""" IF OBJECT_ID('vorbestellt', 'U') IS NOT NULL
        DROP TABLE vorbestellt
    CREATE TABLE vorbestellt (
        K_ID INT NOT NULL,
        M_ID INT NOT NULL,
        Datum DATE NOT NULL DEFAULT GETDATE(),
        Abgeholt DATE,
        AbholbarSeit DATE,
        Bereitstellfrist INT,
        
        PRIMARY KEY(K_ID, M_ID),
        CONSTRAINT FK_vorbestellt_kunde FOREIGN KEY(K_ID) REFERENCES Kunde,
        CONSTRAINT FK_vorbestellt_medium FOREIGN KEY(M_ID) REFERENCES Medium
    )""")


print("FERTSCH")

FERTSCH

In [0]:

cur.execute(""" IF OBJECT_ID('', 'U') IS NOT NULL
        DROP TABLE 
    CREATE TABLE  (
        
    )""")

In [142]:

cur.execute("SELECT * FROM sys.foreign_keys")
row = cur.fetchall()
row

{"output_type":"execute_result"}

Views & Methoden

Sandkasten

In [157]:

### Playground
# M_ID USK Herausgeber
cur.execute("""        
        INSERT INTO Medium values('1','Test','1',getDate())
        SELECT * FROM Medium""")

# Inhalt Ausgaben
row = cur.fetchone()
while row:
    print(row)
    row = cur.fetchone()

(1, 'Test', 1, '2017-03-02')

In [0]:

###Sandkasten
cur.execute("""
        CREATE FUNCTION test1()""")

6.4.1. Kunde

In [0]:

# 4.6.1.1. Kunde hinzufügen

In [738]:

##just for testing
#cur.execute("""IF OBJECT_ID('FK_vorbestellt_kunde') IS NOT NULL 
#                ALTER TABLE vorbestellt DROP FK_vorbestellt_kunde""")
cur.execute(""" IF OBJECT_ID('PAdresse', 'U') IS NOT NULL
        DROP TABLE PAdresse
    CREATE TABLE PAdresse (
        Adr_ID INT NOT NULL IDENTITY(1,1),
        Strasse VARCHAR(50) NOT NULL,
        Hausnummer VARCHAR(5) NOT NULL, 
        PLZ VARCHAR(5) NOT NULL,
        Ort VARCHAR(50) NOT NULL,
        
        PRIMARY KEY (Adr_ID),
        UNIQUE(Strasse, Hausnummer, PLZ, Ort)
    )""")
#
#        GebDatum DATE NOT NULL,Nutzername VARCHAR(40) NOT NULL,
cur.execute(""" IF OBJECT_ID('Personen', 'U') IS NOT NULL
        DROP TABLE Personen
    CREATE TABLE Personen (
        PID INT NOT NULL,
        KundenName VARCHAR(40) NOT NULL,
        KundenVorname VARCHAR(40) NOT NULL,
        EmailAdresse VARCHAR(80),
        TelefonNr VARCHAR(20),
        Adr_ID INT NOT NULL
        
        PRIMARY KEY(PID),
        UNIQUE(KundenName, KundenVorname, Adr_ID),
        UNIQUE(EmailAdresse)
    )""")

Test users :

In [742]:

cur.executemany(
    """EXEC personen_anlegen @PID=%d,@KundenName=%s, @KundenVorname =%s,@Strasse=%s,@Hausnummer=%s, @PLZ=%s,@Ort=%s,@EmailAdresse=%s,@TelefonNr=%s;""",
    [( 0,'Wayne','John','Augustusbruecke','0','011097','Dresden','none','none')])
    
     #,
    # ("", 'Bruce Wayne'),
    # ("", 'Mickey Mouse'),
    # ("", 'Popeye der Seemann'),
    # ("", 'Henry Maske')])

---------------------------------------------------------------------------
MSSQLDatabaseException                    Traceback (most recent call last)
pymssql.pyx in pymssql.Cursor.execute (pymssql.c:7184)()
_mssql.pyx in _mssql.MSSQLConnection.execute_query (_mssql.c:11586)()
_mssql.pyx in _mssql.MSSQLConnection.execute_query (_mssql.c:11466)()
_mssql.pyx in _mssql.MSSQLConnection.format_and_run_query (_mssql.c:12746)()
_mssql.pyx in _mssql.check_cancel_and_raise (_mssql.c:16910)()
_mssql.pyx in _mssql.maybe_raise_MSSQLDatabaseException (_mssql.c:17524)()
MSSQLDatabaseException: (15025, b"The server principal '@KundenName' already exists.DB-Lib error message 20018, severity 16:\nGeneral SQL Server error: Check messages from the SQL Server\nDB-Lib error message 20018, severity 16:\nGeneral SQL Server error: Check messages from the SQL Server\nDB-Lib error message 20018, severity 16:\nGeneral SQL Server error: Check messages from the SQL Server\nDB-Lib error message 20018, severity 14:\nGeneral SQL Server error: Check messages from the SQL Server\nDB-Lib error message 20018, severity 16:\nGeneral SQL Server error: Check messages from the SQL Server\n")

During handling of the above exception, another exception occurred:
OperationalError                          Traceback (most recent call last)
<ipython-input-742-0cd7d4bff378> in <module>()
      1 cur.executemany(
      2     """EXEC personen_anlegen @PID=%d,@KundenName=%s, @KundenVorname =%s,@Strasse=%s,@Hausnummer=%s, @PLZ=%s,@Ort=%s,@EmailAdresse=%s,@TelefonNr=%s;""",
----> 3     [( 0,'Wayne','John','Augustusbruecke','0','011097','Dresden','none','none')])
      4 
      5      #,
pymssql.pyx in pymssql.Cursor.executemany (pymssql.c:7841)()
pymssql.pyx in pymssql.Cursor.execute (pymssql.c:7561)()
OperationalError: (15025, b"The server principal '@KundenName' already exists.DB-Lib error message 20018, severity 16:\nGeneral SQL Server error: Check messages from the SQL Server\nDB-Lib error message 20018, severity 16:\nGeneral SQL Server error: Check messages from the SQL Server\nDB-Lib error message 20018, severity 16:\nGeneral SQL Server error: Check messages from the SQL Server\nDB-Lib error message 20018, severity 14:\nGeneral SQL Server error: Check messages from the SQL Server\nDB-Lib error message 20018, severity 16:\nGeneral SQL Server error: Check messages from the SQL Server\n")

In [709]:

#Done
#cur.execute("""Create ROLE userrole ;""")

In [740]:

# 4.6.1.1. Kunde anlegen
# multi line ,...könnte problematisch werden mitn GO; ,
#da mehrere befehle in einem String gesendet werden
# einfach das GO weglassen,..wird eh seperat gesendet
# müsste man in der python api mal gucken wie das geht

# 

cur.execute("""
            IF OBJECT_ID('personen_anlegen') IS NOT NULL
                DROP PROCEDURE personen_anlegen
            """)

#TODO: "[@KundenName]" wird erzeugt
#TODO: 
cur.execute("""CREATE PROCEDURE personen_anlegen  
            @PID INT,
            @KundenName VARCHAR(40),
            @KundenVorname VARCHAR(40),     
            @Strasse VARCHAR(50),
            @Hausnummer VARCHAR(5),
            @PLZ VARCHAR(5),
            @Ort VARCHAR(50),
            @EmailAdresse VARCHAR(40),
            @TelefonNr VARCHAR(20)          
        AS          
            SET NOCOUNT ON;       
            CREATE LOGIN [@KundenName] WITH PASSWORD=N\'passwort\';
            CREATE USER [@KundenName] FOR LOGIN [@KundenName]; 
            GRANT EXECUTE ON OBJECT::dbatwork.test TO [@KundenName]; 
            GRANT CONNECT TO [@KundenName]
            DECLARE @uid INT
            SET @uid =(SELECT uid FROM sys.sysusers WHERE name = @KundenName)
            DECLARE @Adr_ID INT
            INSERT INTO PAdresse (Strasse, Hausnummer, PLZ, Ort) VALUES( @Strasse, @Hausnummer, @PLZ, @Ort);
            SET @Adr_ID = (SELECT Adr_ID FROM PAdresse WHERE Strasse = @Strasse AND Hausnummer = @Hausnummer AND PLZ = @PLZ AND Ort = @Ort)
            INSERT INTO Personen (PID,KundenName, KundenVorname,Adr_ID ) VALUES(@uid, @KundenName, @KundenVorname,@Adr_ID);
            """)

In [745]:

sql = """SELECT * FROM Personen"""
df = pd.read_sql(sql, conn)
df

	PID	KundenName	KundenVorname	EmailAdresse	TelefonNr	Adr_ID

In [0]:

#sql = """EXEC kunde_anlegen  @name = 'Dr. Watson';"""
#cur.execute(sql)
#cur
#row = cur.fetchmany()
#row

In [743]:


cur.execute("""SELECT name,status,uid FROM sys.sysusers""")

# Inhalt Ausgaben
row = cur.fetchone()
while row:
    print(row)
    row = cur.fetchone()

('public', 0, 0)
('dbo', 0, 1)
('guest', 0, 2)
('INFORMATION_SCHEMA', 0, 3)
('sys', 0, 4)
('adm', 0, 5)
('@KundenName', 0, 6)
('db_owner', 0, 16384)
('db_accessadmin', 0, 16385)
('db_securityadmin', 0, 16386)
('db_ddladmin', 0, 16387)
('db_backupoperator', 0, 16389)
('db_datareader', 0, 16390)
('db_datawriter', 0, 16391)
('db_denydatareader', 0, 16392)
('db_denydatawriter', 0, 16393)

In [0]:

# 4.6.1.2. Kunde löschen
cur.execute(""" IF OBJECT_ID('kunde_loeschen') IS NOT NULL
                DROP PROCEDURE kunde_loeschen
            """)

cur.execute(""" CREATE PROCEDURE kunde_loeschen
                    @K_ID INT
                AS
                    SET NOCOUNT ON
                
                    DECLARE @Adr_ID INT
                
                    IF(SELECT E_InventarNr FROM ausleihe WHERE K_ID = @K_ID) IS NOT NULL OR (SELECT M_ID FROM vorbestellt WHERE K_ID = @K_ID) IS NOT NULL
                        RETURN FALSE
                    ELSE
                    BEGIN
                        SET @Adr_ID = (SELECT Adr_ID FROM Kunde WHERE K_ID = @K_ID);
                        DELETE FROM Kunde WHERE K_ID = @K_ID;
                        DELETE FROM Adresse WHERE Adr_ID = @Adr_ID;
                    END
            """)

In [0]:

# 4.6.1.3. Kunde ändern

In [748]:

# 4.6.1.4. Alter Prüfen
cur.execute(""" IF OBJECT_ID('alter_pruefen') IS NOT NULL
                DROP PROCEDURE alter_pruefen
            """)

cur.execute(""" CREATE PROCEDURE alter_pruefen
                    @K_ID INT,
                    @M_ID INT
                AS
                    DECLARE @GebDatum DATE
                    SET @GebDatum = (SELECT GebDatum FROM Kunde WHERE K_ID = @K_ID)
                    
                    DECLARE @Begrenzung INT
                    SET @Begrenzung = NULL
                    
                    IF(SELECT M_ID FROM Film WHERE M_ID = @M_ID) IS NOT NULL
                        SET @Begrenzung = (SELECT FSK FROM Film WHERE M_ID = @M_ID)
                    ELSE IF(SELECT M_ID FROM Spiel WHERE M_ID = @M_ID) IS NOT NULL
                        SET @Begrenzung = (SELECT USK FROM Spiel WHERE M_ID = @M_ID)
                        
                    IF @Begrenzung IS NULL
                        RETURN 1
                    ELSE
                    BEGIN
                        IF DATEDIFF(year, @GebDatum, GETDATE()) >= @Begrenzung
                            RETURN 1
                        ELSE
                            RETURN 0
                    END
            """)

In [0]:

# 4.6.1.5 Kunde suchen

Exemplar 4.6.2.

In [4]:

# Exemplar hinzufügen 4.6.2.1.

cur.execute(""" IF OBJECT_ID('exemplar_hinzufuegen') IS NOT NULL
                DROP PROCEDURE exemplar_hinzufuegen
            """)

cur.execute(""" CREATE PROCEDURE exemplar_hinzufuegen
                    @M_ID INT,
                    @Status INT = 1, -- Entleihbar
                    @Zustand INT = 2 -- Neu
                AS
                    BEGIN TRANSACTION
                    BEGIN TRY
                        SET NOCOUNT ON

                        DECLARE @tableInserted TABLE (E_InventarNr INT)
                        DECLARE @E_InventarNr INT

                        INSERT INTO Exemplar (Status, Zustand) OUTPUT Inserted.E_InventarNr INTO @tableInserted VALUES (@Status, @Zustand)
                        SELECT @E_InventarNr = E_InventarNr FROM @tableInserted
                        INSERT INTO hat (M_ID, E_InventarNr) VALUES(@M_ID, @E_InventarNr)
                    COMMIT
                    END TRY

                    BEGIN CATCH
                        ROLLBACK
                    END CATCH
            """)

In [5]:

# 4.6.2.2. Exemplar ausleihen
cur.execute(""" IF OBJECT_ID('exemplar_ausleihen') IS NOT NULL
                DROP PROCEDURE exemplar_ausleihen
            """)

cur.execute(""" CREATE PROCEDURE exemplar_ausleihen
                    @E_InventarNr INT,
                    @K_ID INT
                AS
                    SET NOCOUNT ON
                    
                    IF(SELECT K_ID FROM Kunde WHERE K_ID = @K_ID) IS NOT NULL
                    BEGIN
                        IF(SELECT E_InventarNr FROM Exemplar WHERE E_InventarNr = @E_InventarNr) IS NOT NULL --AND ((SELECT Status FROM Exemplar WHERE E_InventarNr = @E_InventarNr) = 1)
                            BEGIN
                                DECLARE @M_ID INT
                                SET @M_ID = (SELECT M_ID FROM hat WHERE E_InventarNr = @E_InventarNr)

                                vorbestellung_absagen @M_ID @E_InventarNr @K_ID
                            
                                BEGIN TRANSACTION
                                BEGIN TRY
                                    -- Kunde hat eine Vorbestellung für das Medium
                                    IF(SELECT M_ID FROM vorbestellt WHERE M_ID = @M_ID AND K_ID = @K_ID AND Abgeholt IS NULL AND AbholbarSeit IS NOT NULL) IS NOT NULL
                                    BEGIN
                                        IF(SELECT Status FROM Exemplar WHERE E_InventarNr = @E_InventarNr) = 3
                                            UPDATE vorbestellt SET Abgeholt = GETDATE() WHERE M_ID = @M_ID AND K_ID = @K_ID
                                        ELSE
                                            RETURN 0
                                    END
                                    -- Kunde hat keine Vorbestellung 
                                    ELSE
                                    BEGIN
                                        IF(SELECT Status FROM Exemplar WHERE E_InventarNr = @E_InventarNr) = 3
                                            RETURN 0
                                    END
                                    
                                    INSERT INTO ausleihe (E_InventarNr, K_ID) VALUES(@E_InventarNr, @K_ID)
                                    UPDATE Exemplar SET Status=2 WHERE E_InventarNr=@E_InventarNr
                                COMMIT
                                RETURN 1
                                END TRY
                                
                                BEGIN CATCH
                                    ROLLBACK
                                    RETURN 0
                                END CATCH
                            END
                    END
            """)

NameErrorTraceback (most recent call last)
<ipython-input-5-5d2ab254bd9e> in <module>()
      1 # 4.6.2.2. Exemplar ausleihen
----> 2 cur.execute(""" IF OBJECT_ID('exemplar_ausleihen') IS NOT NULL
      3                 DROP PROCEDURE exemplar_ausleihen
      4             """)
      5 
NameError: name 'cur' is not defined

In [722]:

# 4.6.2.3. Exemplar zurückgeben
cur.execute(""" IF OBJECT_ID('exemplar_zurueckgeben') IS NOT NULL
                DROP PROCEDURE exemplar_zurueckgeben
            """)

cur.execute(""" CREATE PROCEDURE exemplar_zurueckgeben
                    @E_InventarNr INT
                AS
                    DECLARE @M_ID INT;
                    SET @M_ID = (SELECT M_ID FROM hat WHERE E_InventarNr = @E_InventarNr);
                    DELETE FROM ausleihe WHERE E_InventarNr = @E_InventarNr;
                    
                    IF(SELECT M_ID FROM vorbestellt WHERE M_ID = @M_ID) IS NOT NULL
                    BEGIN
                        UPDATE Exemplar SET Status=3 WHERE E_InventarNr = @E_InventarNr
                        
                        DECLARE @K_ID INT;
                        SET @K_ID = (SELECT TOP 1 K_ID FROM vorbestellt 
                                        WHERE M_ID = @M_ID AND AbholbarSeit IS NULL
                                        ORDER BY Datum ASC);
                        UPDATE vorbestellt SET AbholbarSeit=GETDATE() WHERE M_ID = @M_ID AND K_ID = @K_ID;
                    END
            """)

In [626]:

# 4.6.2.4. Exemplar ändern

In [629]:

# 4.6.2.5. Offene Ausleihen abrufen
cur.execute(""" IF OBJECT_ID('offene_ausleihen_abrufen') IS NOT NULL
                DROP VIEW offene_ausleihen_abrufen
            """)

cur.execute(""" CREATE VIEW offene_ausleihen_abrufen
                AS
                    SELECT * FROM Ausleihe AS a LEFT JOIN Exemplar AS e ON a.E_InventarNr = e.E_InventarNr
            """)

In [8]:

# 4.6.2.6. Exemplar löschen
cur.execute(""" IF OBJECT_ID('exemplar_loeschen') IS NOT NULL
                DROP PROCEDURE exemplar_loeschen
            """)

cur.execute(""" CREATE PROCEDURE exemplar_loeschen
                    @E_InventarNr INT
                AS
                    -- Das Exemplar darf weder ausgeliehen noch vorbesteltt sein, um es löschen zu können
                    IF(SELECT Status FROM Exemplar WHERE E_InventarNr = @E_InventarNr) <= 1
                    BEGIN
                        BEGIN TRANSACTION
                        BEGIN TRY
                            DELETE hat WHERE E_InventarNr = @E_InventarNr
                            DELETE Exemplar WHERE E_InventarNr = @E_InventarNr
                        COMMIT
                        RETURN 1
                        END TRY
                        
                        BEGIN CATCH
                            ROLLBACK
                            RETURN 0
                        END CATCH
                    END
            """)

Vorbestellung 4.6.3.

In [0]:

# 4.6.3.1. Vorbestellung abrufen

In [713]:

# 4.6.3.2. offene Vorbestellung abrufen
cur.execute(""" IF OBJECT_ID('offene_vorbestellungen_abrufen') IS NOT NULL
                DROP VIEW offene_vorbestellungen_abrufen
            """)

cur.execute(""" CREATE VIEW offene_vorbestellungen_abrufen
                AS
                    SELECT * FROM vorbestellt WHERE Abgeholt IS NULL
            """)

In [2]:

# 4.6.3.3. Vorbestellung absagen
cur.execute(""" IF OBJECT_ID('vorbestellung_absagen') IS NOT NULL
                DROP PROCEDURE vorbestellung_absagen
            """)

cur.execute(""" CREATE PROCEDURE vorbestellung_absagen
                    @M_ID INT,
                    @E_InventarNr INT = NULL,
                    @K_ID INT = NULL
                AS
                    SET NOCOUNT ON

                    DECLARE @anzahl_exemplare_verfuegbar INT
                    DECLARE @anzahl_exemplare_vorbestellt INT
                    DECLARE @anzahl_vorbestellt INT
                    DECLARE @anzahl_vorbestellt_abholbar INT

                    DELETE vorbestellt WHERE M_ID = @M_ID AND DATEDIFF(day, AbholbarSeit, GETDATE()) > Bereitstellfrist

                    -- Exemplare mit Status 1 (Verfügbar)
                    SET @anzahl_exemplare_verfuegbar = (SELECT COUNT(*) FROM Exemplar AS e LEFT JOIN hat AS h ON e.E_InventarNr = h.E_InventarNr WHERE e.Status = 1 AND h.M_ID = @M_ID)
                    -- Exemplare mit Status 3 (Vorbestellt)
                    SET @anzahl_exemplare_vorbestellt = (SELECT COUNT(*) FROM Exemplar AS e LEFT JOIN hat AS h ON e.E_InventarNr = h.E_InventarNr WHERE e.Status = 3 AND h.M_ID = @M_ID)
                    -- Vorbestellungen für das Medium, die noch offen sind
                    SET @anzahl_vorbestellt = (SELECT COUNT(*) FROM vorbestellt WHERE M_ID = @M_ID AND AbholbarSeit IS NULL)
                    -- Vorbestellungen für das Medium, die Abholbar sind
                    SET @anzahl_vorbestellt_abholbar = (SELECT COUNT(*) FROM vorbestellt WHERE M_ID = @M_ID AND AbholbarSeit IS NOT NULL AND Abgeholt IS NULL)

                    -- Nach der Aussortierung sind mehr Exemplare als vorbestellt markiert, als Vorbestellungen abholbar
                    IF @anzahl_exemplare_vorbestellt > @anzahl_vorbestellt_abholbar
                    BEGIN
                        -- Status der Exemplare anpassen
                        UPDATE TOP (@anzahl_exemplare_vorbestellt-@anzahl_vorbestellt_abholbar) Exemplar SET Status=1 WHERE Status = 3
                        SET @anzahl_exemplare_vorbestellt = @anzahl_vorbestellt_abholbar
                        SET @anzahl_exemplare_verfuegbar = (SELECT COUNT(*) FROM Exemplar AS e LEFT JOIN hat AS h ON e.E_InventarNr = h.E_InventarNr WHERE e.Status = 1 AND h.M_ID = @M_ID)

                        DECLARE @toUpdate INT
                        SET @toUpdate = @anzahl_exemplare_verfuegbar
                        IF @toUpdate > @anzahl_vorbestellt
                            SET @toUpdate = @anzahl_vorbestellt

                        DECLARE @tableUpdate TABLE (M_ID INT, K_ID INT)
                        INSERT INTO @tableUpdate SELECT TOP (@toUpdate) M_ID, K_ID FROM vorbestellt WHERE M_ID = @M_ID AND AbholbarSeit IS NULL ORDER BY Datum ASC

                        UPDATE vorbestellt SET AbholbarSeit = GETDATE() WHERE M_ID IN (SELECT M_ID FROM @tableUpdate) AND K_ID IN (SELECT K_ID FROM @tableUpdate)

                        IF @E_InventarNr IS NOT NULL AND @K_ID IS NOT NULL
                        BEGIN
                            DECLARE @newState INT
                            DECLARE @oldState INT

                            IF (SELECT K_ID FROM vorbestellt WHERE M_ID = @M_ID AND K_ID = @K_ID AND AbholbarSeit IS NOT NULL AND Abgeholt IS NULL) IS NOT NULL
                            BEGIN
                                SET @newState = 3
                                SET @oldState = 1
                            END
                            ELSE
                            BEGIN
                                SET @newState = 1
                                SET @oldState = 3
                            END

                            SET @anzahl_exemplare_verfuegbar = (SELECT COUNT(*) FROM Exemplar AS e LEFT JOIN hat AS h ON e.E_InventarNr = h.E_InventarNr WHERE e.Status = 1 AND h.M_ID = @M_ID)
                            -- Wenn auszuleihendes Exemplar vorbestellt ist, es aber noch andere verfügbare Exemplare gibt, wird ein anderes als vorbestellt markiert
                            -- oder genau entgegengesetzt, wenn der Kunde selbst ein Exemplar vorbestellt hat. Dann wird das auszuleihende Exemplar als vorbestellt markiert.
                            IF @anzahl_exemplare_verfuegbar > 0 AND (SELECT Status FROM Exemplar WHERE E_InventarNr = @E_InventarNr) = @oldState
                                UPDATE TOP (1) Exemplar SET Status=@oldState WHERE Status = @newState AND E_InventarNr <> @E_InventarNr
                                UPDATE Exemplar SET Status=@newState WHERE E_InventarNr = @E_InventarNr
                        END
                    END
            """)

Traceback (most recent call last):
  File "<stdin>", line 2, in <module>
NameError: name 'cur' is not defined

Medien Verwalten (4.6.4.)

Film aufnehmen 4.6.4.1

In [564]:

# 4.6.4.1. Film aufnehmen
cur.execute("""
            IF OBJECT_ID('film_aufnehmen') IS NOT NULL
                DROP PROCEDURE film_aufnehmen
                """)

cur.execute("""
            CREATE PROCEDURE film_aufnehmen
                @Titel VARCHAR(50),
                @Genre VARCHAR(50),
                @Erscheinungsdatum DATE,
                @Spieldauer INT,
                @FSK INT,
                @Regisseur VARCHAR(50)
            AS
                DECLARE @M_ID INT;
                INSERT INTO Medium (Titel, Genre, Erscheinungsdatum) VALUES(@Titel, @Genre, @Erscheinungsdatum);
                SET @M_ID = (SELECT M_ID FROM Medium WHERE Titel=@Titel AND Genre=@Genre AND Erscheinungsdatum=@Erscheinungsdatum);
                INSERT INTO Film (M_ID, Spieldauer, FSK, Regisseur) VALUES(@M_ID, @Spieldauer, @FSK, @Regisseur);
            """)

EBook aufnehmen 4.6.4.2

In [565]:

# 4.6.4.2. EBoob aufnehmen
cur.execute("""
            IF OBJECT_ID('ebook_aufnehmen') IS NOT NULL
                DROP PROCEDURE ebook_aufnehmen
            """)

cur.execute("""
            CREATE PROCEDURE ebook_aufnehmen
                @Titel VARCHAR(50),
                @Genre VARCHAR(50),
                @Erscheinungsdatum DATE,
                @Autor VARCHAR(50),
                @ISBN VARCHAR(13)
            AS
                DECLARE @M_ID INT;
                INSERT INTO Medium (Titel, Genre, Erscheinungsdatum) VALUES(@Titel, @Genre, @Erscheinungsdatum);
                SET @M_ID = (SELECT M_ID FROM Medium WHERE Titel=@Titel AND Genre=@Genre AND Erscheinungsdatum=@Erscheinungsdatum);
                INSERT INTO EBook (M_ID, Autor, ISBN) VALUES(@M_ID, @Autor, @ISBN);
                """)

Spiel aufnehmen 4.6.4.3

In [566]:

# 4.6.4.3. Spiel aufnehmen
cur.execute("""
            IF OBJECT_ID('spiel_aufnehmen') IS NOT NULL
                DROP PROCEDURE spiel_aufnehmen
        """)

cur.execute("""
            CREATE PROCEDURE spiel_aufnehmen
                @Titel VARCHAR(50),
                @Genre VARCHAR(50),
                @Erscheinungsdatum DATE,
                @USK INT,
                @Herausgeber VARCHAR(50)
            AS
                DECLARE @M_ID INT;
                INSERT INTO Medium (Titel, Genre, Erscheinungsdatum) VALUES(@Titel, @Genre, @Erscheinungsdatum);
                SET @M_ID = (SELECT M_ID FROM Medium WHERE Titel=@Titel AND Genre=@Genre AND Erscheinungsdatum=@Erscheinungsdatum);
                INSERT INTO Spiel (M_ID, USK, Herausgeber) VALUES(@M_ID, @USK, @Herausgeber);
            """)

Titel suchen 4.6.4.4

Medium suchen 4.6.4.5

In [585]:

# 4.6.4.5. Medium suchen
cur.execute("""
            IF OBJECT_ID('medium_suchen') IS NOT NULL
                DROP PROCEDURE medium_suchen
            """)

cur.execute("""
            CREATE PROCEDURE medium_suchen
            AS
""")

Medium löschen 4.6.4.6

In [6]:

# 4.6.4.6.
#aussondern TODO: umbennenen
cur.execute("""
            IF OBJECT_ID('medium_loeschen') IS NOT NULL
                DROP PROCEDURE medium_loeschen
            """)

cur.execute("""
            CREATE PROCEDURE medium_loeschen
                @M_ID INT
            AS
                BEGIN TRANSACTION;
                
                BEGIN TRY
                    DECLARE @E_InventarNr INT
                    --FOR @E_InventarNr IN (SELECT E_InventarNr FROM hat WHERE M_ID = @M_ID) LOOP
                    --    IF (exemplar_loeschen @E_InventarNr) == 0
                    --        ROLLBACK
                    --END LOOP;
                
                    IF(SELECT M_ID FROM Film WHERE M_ID = @M_ID) IS NOT NULL
                        DELETE FROM Film WHERE M_ID = @M_ID
                    ELSE IF(SELECT M_ID FROM Spiel WHERE M_ID = @M_ID) IS NOT NULL
                        DELETE FROM Spiel WHERE M_ID = @M_ID
                    ELSE IF(SELECT M_ID FROM EBook WHERE M_ID = @M_ID) IS NOT NULL
                        DELETE FROM EBook WHERE M_ID = @M_ID

                    
                    DELETE FROM Medium WHERE M_ID = @M_ID
                COMMIT;
                
                END TRY
                BEGIN CATCH
                    ROLLBACK;
                END CATCH
            """)

NameErrorTraceback (most recent call last)
<ipython-input-6-d7522ef25306> in <module>()
      1 # 4.6.4.6.
      2 #aussondern TODO: umbennenen
----> 3 cur.execute("""
      4             IF OBJECT_ID('medium_loeschen') IS NOT NULL
      5                 DROP PROCEDURE medium_loeschen
NameError: name 'cur' is not defined

In [0]: