MySQL in Python-tutorial: aan de slag

Een nieuwe database maken in MySQL met Python

Vanaf hier heb je SQL-vaardigheden nodig; DataCamps Introduction to SQL-cursus is een goed startpunt om snel op niveau te komen.

De gebruikelijke manier om een database in MySQL te maken is als volgt:

CREATE DATABASE book_ratings;

Om dezelfde query in Python uit te voeren, moet je een instantie van het cursor()-object maken.

Vanuit het cursor()-object kun je SQL-commando’s als string doorgeven aan de execute()-methode, die de code zal uitvoeren.

Laten we een database maken genaamd “book_ratings…”

create_db = "CREATE DATABASE book_ratings"
with database.cursor() as cursor:
    cursor.execute(create_db)

Let op: deze code meerdere keren uitvoeren levert een fout op die aangeeft dat de database al bestaat.

Je kunt de databases die op een server bestaan als volgt controleren:

show_existing_db = "SHOW DATABASES"

with database.cursor() as cursor: 
    cursor.execute(show_existing_db)
    for db in cursor:
        print(db)

"""
('book_ratings',)
('information_schema',)
('mysql',)
('performance_schema',)
('sakila',)
('sys',)
('world',)
"""

De bovenstaande code geeft de namen weer van alle databases die momenteel in onze MySQL-server staan. Het zijn er meer dan één omdat de MySQL-server automatisch databases genereert om toegang te bieden tot allerlei database-metadata en serverinstellingen.

Verbinden met een bestaande database

Stel dat je al een database hebt gemaakt, maar nog geen verbinding met de server hebt; hoe zou je dan direct met de database verbinden?

Simpel.

Geef de naam van de database als argument door aan de connect()-methode:

try:
    with connector.connect(
        host = "localhost",
        user = "root",
        password = PASSWORD,
        database = "book_ratings"
    ) as database:
       
        print(database})

except connector.Error as e:
    print(e)

"""
<mysql.connector.connection_cext.CMySQLConnection object at 0x0000016DD8C24970>
"""

Tabellen maken

Informatie in een database wordt opgeslagen in een tabel. Een tabel is simpelweg een formaat om een verzameling gerelateerde data op te slaan, bestaande uit kolommen en rijen.

Er kunnen meerdere tabellen binnen een database staan, ook als ze niet aan elkaar gerelateerd zijn. Je kunt ook een relationele database hebben met één enkele tabel, zoals we in deze sectie laten zien – breid je kennis over relationele databases uit met de Introduction to Relational Databases in SQL-cursus.

Tabellen maken volgt een vergelijkbare aanpak als je al gezien hebt:

• Schrijf een SQL-commando als string
• Geef het door aan de execute()-methode van het cursor-object.

De SQL-instructie in dit geval is CREATE TABLE.

Laten we hiermee een tabel voor boeken maken:

create_books_table = """
CREATE TABLE books(
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(100),
    author VARCHAR(100),
    genre VARCHAR(100),
    release_year YEAR(4)
)
"""
# Create cursor object
with existing_database.cursor() as cursor:
    cursor.execute(create_books_table)
    existing_database.commit()

In de laatste regel zie je dat we de commit()-methode op ons existing_database-object hebben gebruikt. Dat moesten we doen omdat de standaardinstelling van de MySQL Python-connector is om een transactie niet automatisch te committen.

Telkens wanneer je data in een tabel wijzigt, moet je de commit()-methode aanroepen – lees meer over de commit()-methode in de MySQL-docs.

Nog een paar belangrijke zaken om op te merken in de code:

• NOT NULL: Een constraint om af te dwingen dat een kolom geen NULL-waarden accepteert.
• PRIMARY KEY: Een unieke waarde om specifieke rijen in een tabel te identificeren.
• AUTO_INCREMENT: Genereert automatisch een uniek nummer voor nieuwe records die in de tabel worden ingevoegd.

We kunnen controleren of ons schema goed is aangemaakt met de DESCRIBE-instructie en vervolgens alle elementen printen met de fetchall()-methode op ons cursor-object…

describe_books = "DESCRIBE books"
with existing_database.cursor() as cursor:
    cursor.execute(describe_books)
    books_schema = cursor.fetchall()
    for column in books_schema:
        print(column)

"""
('id', b'int', 'NO', 'PRI', None, 'auto_increment')
('title', b'varchar(100)', 'YES', '', None, '')
('author', b'varchar(100)', 'YES', '', None, '')
('genre', b'varchar(100)', 'YES', '', None, '')
('release_year', b'year', 'YES', '', None, '')
"""

Data invoegen

In de vorige secties hebben we een tabel genaamd books gemaakt in onze book_ratings-database. Op dit moment is deze tabel leeg. De volgende sectie behandelt twee manieren waarop je een tabel kunt vullen.

Eén record invoegen

Er kan een situatie zijn waarin je één record in een tabel moet invoegen. Hoewel dit in de praktijk zelden voorkomt, kun je het als volgt doen:

# INSERT SINGLE RECORD
insert_single_record = "INSERT INTO books (id, title, author, genre, release_year)\
    VALUES (%s, %s, %s, %s, %s)"
single_record = (
    "1", "Choose Yourself! Be Happy, Make Millions, Live the Dream", "James Altucher", "self-help", "2013"
    )

# Create cursor object
with existing_database.cursor() as cursor:
    cursor.execute(insert_single_record, single_record)
    existing_database.commit()

Meerdere records invoegen

De gebruikelijkere manier om data in een tabel in te voegen, is met de executemany(operation_to_perform, seq_of_params)-methode van het cursor-object; hiermee kun je meerdere records in één keer invoegen.

De structuur van de bewerking die we willen uitvoeren blijft hetzelfde; we werken alleen de reeks parameters bij.

Zo ziet dat er in code uit:

multiple_records = [
    (
        "2",
        "Skip the Line: The 10,000 Experiments Rule and Other Surprising Advice for Reaching Your Goals",
        "James Altucher",
        "self-help",
        "2021"        
    ),
    (
        "3",
        "The Power of No: Because One Little Word Can Bring Health, Abundance, and Happiness",
        "James Altucher",
        "self-help",
        "2014"
    ),
    ]

with existing_database.cursor() as cursor:
    cursor.executemany(insert_multiple_records, multiple_records)
    existing_database.commit()

Data selecteren

De SELECT-instructie wordt gebruikt om rijen op te halen die zijn geselecteerd uit een of meer tabellen in een database. We kunnen SELECT op twee manieren gebruiken:

1. Specifieke kolommen uit een tabel selecteren.

select_specific_cols = "SELECT author, release_year FROM books"
with existing_database.cursor() as cursor:
    cursor.execute(select_specific_cols)

1. Alle kolommen retourneren met een asterisk “*”

# Create cursor object
select_all_cols = "SELECT * FROM books"
with existing_database.cursor() as cursor:
    cursor.execute(select_all_cols)

Data sorteren en filteren

Data sorteren en filteren stelt je in staat resultaten terug te krijgen waar je om geeft, door specifieke criteria op te geven waaraan records moeten voldoen.

Hier zijn een paar voorbeelden:

WHERE

De WHERE-instructie laat je data filteren op basis van een opgegeven voorwaarde.

Stel bijvoorbeeld dat we de naam van de auteur en de boektitel willen ophalen voor elk boek dat vóór en inclusief 2010 is uitgebracht:

condition = "SELECT author, title FROM books WHERE release_year <= 2010"
with existing_database.cursor() as cursor:
    cursor.execute(condition)

ORDER BY

We kunnen ook de ORDER BY-instructie gebruiken om resultaten oplopend (ASC) of aflopend (DESC) te sorteren. Standaard is de volgorde oplopend, tenzij je expliciet anders aangeeft.

Zo zouden we onze data op aflopende volgorde van releasejaar sorteren:

order_by_year = "SELECT * FROM books ORDER BY release_year DESC"
with existing_database.cursor() as cursor:
    cursor.execute(order_by_year)

LIMIT

Met de LIMIT-instructie kun je het aantal records opgeven dat in het resultaat moet worden teruggegeven.

# Create cursor object
define_limit = "SELECT * FROM books LIMIT 3"
with existing_database.cursor() as cursor:
    cursor.execute(define_limit)

Bovenstaande code beperkt het aantal terug te geven records tot drie.

Data updaten en verwijderen

Er zullen momenten zijn waarop data verandert, niet langer nuttig is of onjuist blijkt. In deze sectie leer je hoe je deze situaties beheert met updates en verwijderingen.

UPDATE

Met de UPDATE-query kun je bestaande waarden binnen een tabel wijzigen zonder de structuur ervan te beïnvloeden.

Je moet bijvoorbeeld een record bijwerken als iemand zijn naam verandert; zo ziet dat er in code uit:

update_query = """
UPDATE
    books
SET
    author = "Big J"
WHERE
    author = "James Altucher"
"""
with existing_database.cursor() as cursor:
    cursor.execute(update_query)
    existing_database.commit()

DELETE

Met de DELETE-query kun je een record uit een tabel verwijderen:

drop_record = "DELETE FROM books WHERE release_year <= 2000"
with existing_database.cursor() as cursor:
    cursor.execute(drop_record)
    existing_database.commit()

In deze code hebben we alle records verwijderd met een releasejaar kleiner dan of gelijk aan 2000.

DROP TABLE

Met de DROP TABLE-instructie kun je een tabel permanent uit een database verwijderen. IF EXISTS toevoegen helpt in situaties waarin je niet zeker weet of de te droppen tabel bestaat, maar het is volledig optioneel.

Laten we de tabel die we in onze book_ratings-database hebben gemaakt, droppen:

drop_table = "DROP TABLE IF EXISTS books"
with existing_database.cursor() as cursor:
    cursor.execute(drop_table)
    existing_database.commit()

Conclusie

In deze tutorial heb je geleerd hoe je in Python een verbinding met een MySQL-server tot stand brengt, een nieuwe database maakt, met een bestaande database verbindt, een tabel maakt en verschillende queryoperaties op een MySQL-tabel vanuit Python uitvoert.

Bekijk de volgende resources om verder te leren:

• Introduction to Databases in Python
• SQL Server, PostgreSQL, MySQL… Wat is het verschil? Waar begin ik?
• SQL-cursussen voor beginners
• PostgreSQL vs. MySQL