💡 Problem Formulation: Frequently, developers and data analysts are faced with the task of migrating data stored in CSV files into a MariaDB database for more complex queries and data manipulation. The input in this scenario is a CSV file containing structured data, and the desired output is the successful insertion of this data into specific tables within a MariaDB database.
Method 1: Using Python’s csv and mariadb Connector
This method involves reading the CSV file using Python’s built-in csv module and inserting data into MariaDB using the mariadb connector. It’s suitable for those who seek a straightforward approach without third-party dependencies besides the connector.
Here’s an example:
import csv
import mariadb
# Connect to MariaDB
conn = mariadb.connect(user='username', password='password', database='mydatabase')
cursor = conn.cursor()
# Open CSV file
with open('data.csv', 'r') as csvfile:
csv_reader = csv.reader(csvfile)
next(csv_reader) # Skip header row
for row in csv_reader:
cursor.execute("INSERT INTO mytable (column1, column2) VALUES (?, ?)", row)
# Commit changes
conn.commit()
conn.close()
The output is the data from ‘data.csv’ being inserted into the ‘mytable’ table in the MariaDB database.
This code snippet establishes a connection to a MariaDB database, reads a CSV file row by row (excluding the header), and executes an INSERT query for each row of the CSV. To prevent SQL injection, it uses placeholders (?, ?) which are filled with the respective data from the CSV. The transaction is then committed to the database and the connection closed.
Method 2: Using pandas and SQLAlchemy
For those familiar with data science in Python, pandas combined with SQLAlchemy provides a powerful way to import CSV data into MariaDB. SQLAlchemy handles database connections whilst pandas manage data manipulation before the import.
Here’s an example:
import pandas as pd
from sqlalchemy import create_engine
# Create MariaDB connection
engine = create_engine('mariadb+mariadbconnector://username:password@localhost/mydatabase')
# Read CSV using pandas
df = pd.read_csv('data.csv')
# Import data into MariaDB
df.to_sql('mytable', con=engine, if_exists='append', index=False)
The output is the dataframe ‘df’ being inserted into the ‘mytable’ table within the MariaDB database.
This code reads the CSV file into a pandas dataframe and uses the to_sql() function to export the dataframe to the MariaDB table. By using if_exists='append', it ensures that the existing table data is preserved, and new data is added. The index=False flag prevents the dataframe’s index from being inserted as a column into the database table.
Method 3: Using MySQLdb and CSV DictReader
In this method, we use MySQLdb, a library designed for working with MySQL, which is compatible with MariaDB as well. The csv.DictReader allows for easy mapping of CSV columns to database fields.
Here’s an example:
import csv
import MySQLdb
# Connect to MariaDB
conn = MySQLdb.connect(passwd='password', db='mydatabase')
cursor = conn.cursor()
# Open CSV file
with open('data.csv', 'r') as csvfile:
dict_reader = csv.DictReader(csvfile)
for row in dict_reader:
cursor.execute("INSERT INTO mytable (column1, column2) VALUES (%(column1)s, %(column2)s)", row)
# Commit changes
conn.commit()
conn.close()
The output is each row from the ‘data.csv’ inserted into the ‘mytable’ table in the MariaDB database.
This script connects to a MariaDB database and iterates through the CSV file using a DictReader, which provides a dictionary per CSV row. The INSERT query uses the column names as keys to fetch corresponding values for the placeholders in the SQL statement. After executing all INSERT queries, the changes are committed and the connection closed.
Method 4: Using csvkit
csvkit is a suite of command-line tools for converting to and working with CSV. The tool ‘csvsql’ enables direct import of CSV files into SQL databases, and this method can be particularly useful for scripting and can be run directly from a shell or within a Python environment.
Here’s an example:
import subprocess # Execute csvsql command subprocess.run(["csvsql", "--db", "mariadb+mariadbconnector://username:password@localhost/mydatabase", "--insert", "data.csv"])
No explicit output in Python, but the CSV data is inserted into the MariaDB database.
The subprocess module runs the csvsql command from csvkit. The command includes the database connection string and the CSV file to be imported. The --insert flag tells csvsql to insert data into the database. This method streamlines the import process into a single command.
Bonus One-Liner Method 5: Using a Python one-liner and the MariaDB Command-Line Tool
This bonus method is a one-liner that executes a Python command to pipe a CSV file directly into the MariaDB command-line tool. It’s quick and efficient for small datasets and requires no additional code files.
Here’s an example:
python -c "import sys; import csv; sys.stdout.write('\n'.join([','.join(row) for row in csv.reader(open('data.csv'))]))" | mysql -u username -p mydatabase -e "LOAD DATA LOCAL INFILE 'stdin' INTO TABLE mytable FIELDS TERMINATED BY ','"
The output is immediate insertion of the CSV data into the MariaDB table ‘mytable’.
The command reads a CSV file, processes it into a format suitable for the MariaDB LOAD DATA command, and pipes it to the MariaDB client. The use of stdin with LOAD DATA allows the CSV content to be read from standard input. This method is quick but less flexible and implies a secure environment setup ahead of time.
Summary/Discussion
- Method 1: Python csv and mariadb Connector. Straightforward. Great for simple scripts. Requires manual query construction which can be error-prone.
- Method 2: pandas and SQLAlchemy. Very powerful and flexible. Ideal for complex data manipulation before import. Heavier dependency.
- Method 3: MySQLdb and CSV DictReader. Good for when column names in CSV match the database fields. Requires caution with MySQL/MariaDB compatibility.
- Method 4: Using csvkit. Excellent for command-line enthusiasts. May require additional installation and less control over the import process.
- Bonus Method 5: Python one-liner. Quick and concise. Best for small tasks with well-prepared data and secure environments.