Import Excel file from Python (register to DB)

Overview

Parse the Excel file with Python and register it in the DB. In the future, it will be used for the process of registering the Excel file uploaded to a specific channel of Slack to the DB behind the scenes.

Pre-installed packages

Install the following packages.

pip install xlrd #Excel library
sudo apt-get install mariadb-server-10.0 #Install mysqlDB
apt-get install python-mysqldb #Install the library for mysqldb connection

Mysql connection check

Initially, no password is specified, so connect with the Root account. Confirm that you can connect with the following command.

pi@raspberrypi:~ $ sudo mysql -uroot
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 44
Server version: 10.0.28-MariaDB-2+b1 Raspbian testing-staging

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> quit
Bye
pi@raspberrypi:~ $ 

Create DB and account with Mysql

Execute the following command to create a DB and an account.

pi@raspberrypi:~ $ sudo mysql -uroot
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 45
Server version: 10.0.28-MariaDB-2+b1 Raspbian testing-staging

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> create database excel;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create user 'pyxls'@'localhost' identified by 'pyxls';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> grant all on excel.* to pyxls@localhost;
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> quit

Please refer to the following site for the contents of the detailed command.

Create a database (CREATE DATABASE statement)

Create a user (CREATE USER statement)

Set authority for user (GRANT statement)

Connect to DB with the created account

Connect to the DB with the following command. Make sure you can access it without any problems. [About options] The -u option fills in the created user ID. Specify the -p option to enter the password of the created user.

pi@raspberrypi:~ $ mysql -upyxls -p excel
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 46
Server version: 10.0.28-MariaDB-2+b1 Raspbian testing-staging

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [excel]> 

Connect to DB from Python code and display DB information

Save the following code in pydb.py.

#!/usr/bin/python
# -*- coding: utf-8 -*-
import MySQLdb

#Connecting
conn = MySQLdb.connect(
user='pyxls',
passwd='pyxls',
host='localhost',
db='excel')

#Get the cursor
cur = conn.cursor()

#Execute SQL (command to operate the database)
#Extract database information
sql = "show databases"
cur.execute(sql)

#Get the execution result
rows = cur.fetchall()

#Display line by line
for row in rows:
    print(row)

cur.close
conn.close

Please refer to the following site for the contents of the sample source.

How to write to connect to MySQL with Python

Sample source to register Excel data in DB

  1. Create Excel data and save it in "excel.xlsx".

image.png

  1. Create the following table in the DB.
create table userlist(
name varchar(50),
gender varchar(10),
tel varchar(20)
)

The screen to execute the above SQL is as follows.

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 52
Server version: 10.0.28-MariaDB-2+b1 Raspbian testing-staging

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [excel]> create table userlist(
    -> name varchar(50),
    -> gender varchar(10),
    -> tel varchar(20)
    -> );
Query OK, 0 rows affected (0.05 sec)

MariaDB [excel]> 
  1. Create the following Python code and save it in pyxls.py.
#!/usr/bin/python
# -*- coding: utf-8 -*-
import xlrd
import sys
import MySQLdb

#Open the workbook and define the worksheet.
book = xlrd.open_workbook("excel.xlsx")
sheet = book.sheet_by_name("Sheet1")

#Connect to Mysql
database = MySQLdb.connect (host="localhost", user = "pyxls", passwd = "pyxls", db = "excel", charset="utf8")

#Gets the cursor used to traverse the database row by row.
cursor = database.cursor()

#Create an INSERT INTO SQL query
query = """INSERT INTO userlist (name, gender, tel) VALUES (%s, %s, %s)"""

#Create a For Loop that iterates over each line in the XLSX file, skipping the header from the second line
for r in range(1, sheet.nrows):
    name = "'" + sheet.cell(r,0).value + "'"
    gender = "'" + sheet.cell(r,1).value + "'"
    tel = "'" + sheet.cell(r,2).value + "'"

    #Assign a value from each row
    values = (name, gender, tel)

    #Execute SQL query
    cursor.execute(query, values)

#Close cursor
cursor.close()

#Commit the transaction
database.commit()

#Close the database connection
database.close()

Change the character code of Mysql

Add a line from the following configuration file.

pi@raspberrypi:~/work $ sudo vi /etc/mysql/mariadb.conf.d/50-server.cnf
...
# this is only for the mysqld standalone daemon
[mysqld]
character-set-server = utf8 #Add a line here and save.
...
#Reboot the DB with the following command:
pi@raspberrypi:~/work $ sudo /etc/init.d/mysql restart
[ ok ] Restarting mysql (via systemctl): mysql.service.

#Connect to the DB and check the character code.
pi@raspberrypi:~/work $ mysql -upyxls -p excel
Enter password: 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 32
Server version: 10.0.28-MariaDB-2+b1 Raspbian testing-staging

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [excel]> status
--------------
mysql  Ver 15.1 Distrib 10.0.28-MariaDB, for debian-linux-gnueabihf (armv7l) using readline 5.2

Connection id:		32
Current database:	excel
Current user:		pyxls@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server:			MariaDB
Server version:		10.0.28-MariaDB-2+b1 Raspbian testing-staging
Protocol version:	10
Connection:		Localhost via UNIX socket
Server characterset:	utf8mb4
Db     characterset:	utf8mb4
Client characterset:	utf8mb4
Conn.  characterset:	utf8mb4
UNIX socket:		/var/run/mysqld/mysqld.sock
Uptime:			1 min 4 sec

Threads: 1  Questions: 94  Slow queries: 0  Opens: 15  Flush tables: 1  Open tables: 78  Queries per second avg: 1.468
--------------

MariaDB [mysql]> quit
Bye

Run python code

Execute the following command.

pi@raspberrypi:~/work $ python pyxls.py

#Check the contents of the registered DB.
pi@raspberrypi:~/work $ mysql -upyxls -p -hlocalhost excel
Enter password: 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 33
Server version: 10.0.28-MariaDB-2+b1 Raspbian testing-staging

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [excel]> show tables;
+-----------------+
| Tables_in_excel |
+-----------------+
| userlist        |
+-----------------+
1 row in set (0.00 sec)

MariaDB [excel]> select * from userlist;
+----------+----------+-----------------+
| name     | gender   | tel             |
+----------+----------+-----------------+
| 'Tanaka'   | 'male'   | '111-111-1111'  |
| 'Kanazawa'   | 'male'   | '222-222-2222'  |
| 'Suzuki'   | 'male'   | '333-333-3333'  |
| 'Nagasawa'   | 'Female'   | '444-4444-4444' |
| 'Toda'   | 'Female'   | '555-555-5555'  |
+----------+----------+-----------------+
5 rows in set (0.00 sec)

MariaDB [excel]> 

At the end

I added not only the Python code but also the procedure of the environment, so the article became long and long. In the next article, I would like to write a code to transfer the registered data to an email. Thank you for reading to the end.

Recommended Posts

Import Excel file from Python (register to DB)
[Python] How to convert db file to csv
Edit Excel from Python to create a PivotTable
Changes from Python 3.0 to Python 3.5
Changes from Python 2 to Python 3.0
Python from or import
From file to graph drawing in Python. Elementary elementary
[Python] How to read excel file with pandas
Run python from excel
Backtrader How to import an indicator from another file
[Python] Change standard input from keyboard to text file
Procedure to exe python file from Ubunts environment construction
Python script to create a JSON file from a CSV file
Post from Python to Slack
Cheating from PHP to Python
Anaconda updated from 4.2.0 to 4.3.0 (python3.5 updated to python3.6)
Access Oracle DB from Python
Switch from python2.7 to python3.6 (centos7)
[Python] Another way to import
Connect to sqlite from python
Register redmine issue from Python
Try to operate an Excel file using Python (Pandas / XlsxWriter) ①
Try to operate an Excel file using Python (Pandas / XlsxWriter) ②
How to import a file anywhere you like in Python
Introduction to Python for VBA users-Calling Python from Excel with xlwings-
[Python] How to change EXCEL file saved in xlsb to xlsx
[Python] How to output a pandas table to an excel file
How to read an Excel file (.xlsx) with Pandas [Python]
(Translation) Native connection from Python to Hadoop file system (HDFS)
Convert Excel file to text in Python for diff purposes
Call Matlab from Python to optimize
Execute Python script from batch file
Easy way to customize Python import
Post from python to facebook timeline
python> link> from __future__ import absolute_import
Output to csv file with Python
[Lambda] [Python] Post to Twitter from Lambda!
Create an Excel file with Python3
About Python, from and import, as
[Python] How to import the library
Connect to utf8mb4 database from python
Python (from first time to execution)
Post images from Python to Tumblr
How to access wikipedia from python
Python to switch from another language
Load Mac Python import MySQL db
File upload to Azure Storage (Python)
Did not change from Python 2 to 3
Update Python on Mac from 2 to 3
[Python] Fluid simulation: From linear to non-linear
From Python to using MeCab (and CaboCha)
Script to generate directory from json file
How to update Google Sheets from Python
How to convert Python to an exe file
[Python] Convert csv file delimiters to tab delimiters
Send a message from Python to Slack
Convert psd file to png in Python
Private Python handbook (updated from time to time)
Quickly create an excel file with Python #python
Convert Excel data to JSON with python
I want to use jar from python