Conexión a MariaDB usando Python

• Última modificación: Mayo 26, 2022

Descarga de datos

url = "https://raw.githubusercontent.com/jdvelasq/datalabs/master/datasets/drivers/drivers.csv"
!wget --quiet {url} -P /tmp/

Instalación del conector

Ya está incluido en el contenedor

pip3 install mariadb

Conexión a MariaDB

import mariadb

conn = mariadb.connect(
    user="root",
    password="",
)

cur = conn.cursor()

Creación de la base de datos y la tabla

cur.execute("DROP DATABASE IF EXISTS demo_db;")
cur.execute("CREATE DATABASE demo_db;")
cur.execute("USE demo_db;")
cur.execute("DROP TABLE IF EXISTS drivers;")

cur.execute(
    """
    CREATE TABLE drivers (
        driverId       INT,
        name           VARCHAR(20),
        ssn            VARCHAR(20),
        location       VARCHAR(40),
        certified      VARCHAR(20),
        wage_plan      VARCHAR(20)
    );
    """
)

conn.commit()

Importación de datos y cierre de la conexión

import pandas as pd

drivers = pd.read_csv("/tmp/drivers.csv")

for i, row in drivers.iterrows():
    sql = "INSERT INTO drivers VALUES (%s,%s,%s,%s,%s,%s)"
    cur.execute(sql, tuple(row))
    conn.commit()

conn.close()

Verificación

!mariadb -u root -e "USE demo_db; SELECT * FROM drivers LIMIT 5;"

+----------+-------------------+-----------+---------------------------+-----------+-----------+
| driverId | name              | ssn       | location                  | certified | wage_plan |
+----------+-------------------+-----------+---------------------------+-----------+-----------+
|       10 | George Vetticaden | 621011971 | 244-4532 Nulla Rd.        | N         | miles     |
|       11 | Jamie Engesser    | 262112338 | 366-4125 Ac Street        | N         | miles     |
|       12 | Paul Coddin       | 198041975 | Ap #622-957 Risus. Street | Y         | hours     |
|       13 | Joe Niemiec       | 139907145 | 2071 Hendrerit. Ave       | Y         | hours     |
|       14 | Adis Cesir        | 820812209 | Ap #810-1228 In St.       | Y         | hours     |
+----------+-------------------+-----------+---------------------------+-----------+-----------+