====== SQLite ======


==== Datenbank per Python erzeugen ====

<code python>
# Quelle: https://pynative.com/python-sqlite/

import sqlite3

try:
    sqliteConnection = sqlite3.connect('SQLite_Python.db')
    cursor = sqliteConnection.cursor()
    print("Database created and Successfully Connected to SQLite")

    sqlite_select_Query = "select sqlite_version();"
    cursor.execute(sqlite_select_Query)
    record = cursor.fetchall()
    print("SQLite Database Version is: ", record)
    cursor.close()

except sqlite3.Error as error:
    print("Error while connecting to sqlite", error)
finally:
    if (sqliteConnection):
        sqliteConnection.close()
        print("The SQLite connection is closed")
</code>

Ergebnis: 

<code>
Database created and Successfully Connected to SQLite
SQLite Database Version is:  [('3.31.1',)]
The SQLite connection is closed
</code>



==== Struktur (nicht Inhalt) einer Tabelle anzeigen ====

<code python>
# Beispiel-Datenbank von https://www.sqlitetutorial.net/sqlite-sample-database/

import sqlite3

sqliteConnection = sqlite3.connect('chinook.db')
cursor = sqliteConnection.cursor()
print("Database created and Successfully Connected to SQLite")

cursor.execute("PRAGMA table_info(`albums`)")
for column in cursor:
    print(column)
cursor.close()

sqliteConnection.close()
print("The SQLite connection is closed")
</code>

Ergebnis: 

<code>
Database created and Successfully Connected to SQLite
(0, 'AlbumId', 'INTEGER', 1, None, 1)
(1, 'Title', 'NVARCHAR(160)', 1, None, 0)
(2, 'ArtistId', 'INTEGER', 1, None, 0)
The SQLite connection is closed
</code>


==== Daten einer Tabelle ausgeben ====

<code python>
import sqlite3


sqliteConnection = sqlite3.connect('chinook.db')
cursor = sqliteConnection.cursor()
print("Database created and Successfully Connected to SQLite")

# cursor.execute("PRAGMA table_info(`albums`)")
# for column in cursor:
#     print(column)
# cursor.close()

with sqliteConnection:
	cur = sqliteConnection.cursor()
	cur.execute("SELECT * FROM albums")

	rows = cur.fetchall()

	for row in rows:
		print(row)

sqliteConnection.close()
print("The SQLite connection is closed")
</code>

Ergebnis: 

<code>

Database created and Successfully Connected to SQLite
(1, 'For Those About To Rock We Salute You', 1)
(2, 'Balls to the Wall', 2).
.
.
(346, 'Mozart: Chamber Music', 274)
(347, 'Koyaanisqatsi (Soundtrack from the Motion Picture)', 275)
The SQLite connection is closed
[Finished in 0.1s]
</code>