Python: Import data from local .sql file

Question

I need a russian dictionary (list of existing nouns) for my project. I found .sql file on the internet. But unfortunately I can't understand how to use it. File has this structure:

-- phpMyAdmin SQL Dump
-- version 4.0.10.10
-- http://www.phpmyadmin.net
--
-- Хост: 127.0.0.1:3306
-- Время создания: Мар 13 2017 г., 18:16
-- Версия сервера: 5.5.45
-- Версия PHP: 5.6.12

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- База данных: `morphology`
--

-- --------------------------------------------------------

--
-- Структура таблицы `nouns`
--

DROP TABLE IF EXISTS `nouns`;
CREATE TABLE IF NOT EXISTS `nouns` (
  `IID` int(11) NOT NULL AUTO_INCREMENT,
  `word` varchar(60) NOT NULL,
  `code` int(11) NOT NULL,
  `code_parent` int(11) NOT NULL,
  `gender` enum('муж','жен','ср','общ') DEFAULT NULL,
  `wcase` enum('им') DEFAULT NULL,
  `soul` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`IID`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4159395 ;

--
-- Дамп данных таблицы `nouns`
--

INSERT INTO `nouns` (`IID`, `word`, `code`, `code_parent`, `gender`, `wcase`, `soul`) VALUES
(43, 'а-ля фуршет', 4155484, 0, 'муж', 'им', 0),
(49, 'аба', 1190170, 0, 'жен', 'им', 0),
(62, 'абажур', 125723, 0, 'муж', 'им', 0),
(101, 'абажурчик', 125724, 0, 'муж', 'им', 0),
(113, 'абаз', 125725, 0, 'муж', 'им', 0),
(125, 'абазин', 4155485, 0, 'муж', 'им', 1),
(137, 'абазинец', 125727, 0, 'муж', 'им', 1),
(149, 'абазинка', 144752, 0, 'жен', 'им', 1),
(189, 'абазия', 4084603, 0, 'жен', 'им', 0),
(201, 'абак', 125728, 0, 'муж', 'им', 0),
(213, 'абака', 144753, 0, 'жен', 'им', 0),
(226, 'абандон', 4084604, 0, 'муж', 'им', 0),
(238, 'аббат', 1, 0, 'муж', 'им', 1),
(250, 'аббатиса', 144754, 0, 'жен', 'им', 1),
<...>
(4159240, 'ящерогад', 1186654, 0, 'муж', 'им', 1),
(4159252, 'ящик', 36027, 0, 'муж', 'им', 0),
(4159292, 'ящичек', 144748, 0, 'муж', 'им', 0),
(4159304, 'ящичник', 144709, 0, 'муж', 'им', 1),
(4159343, 'ящур', 144710, 0, 'муж', 'им', 0),
(4159355, 'ящурка', 161972, 0, 'жен', 'им', 1);

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

I need to grab data just from 'word' column (which contains all words) and then use it with python or save as text file. Any ideas how can I do this?

Alternatively, if you know a good large dictionary with russian nouns in simple text format, that will help too.

Answer 1

You need to host a local SQL database first, so you'll need to install MariaDB or MySQL. Here's the MariaDB Windows install guide, and on Linux it can be installed through a software repo or the site.

When it's installed, login to the SQL server hosted on your own computer by entering mysql -u root -p into the command line (assuming you're using "root" as a user), and it may ask you for the password you installed MariaDB/MySQL with; alternatively, use the method of executing commands your choice of SQL database management system provides.

Ensure you trust the SQL file; malicious code can be held in them. To create the database noun_database and import your SQL file into it, enter this:

CREATE DATABASE noun_database;
USE noun_database;
source C:/path/to/file/db.sql

As the table in your SQL file is called nouns, confirm you've imported the table by listing it:

SELECT * FROM nouns;

Then in your Python file, to import that database, and place each word into word_list:

import mariadb
import sys

if __name__ == "__main__":
    word_list = list()
    try:
        print("Connecting")
        conn = mariadb.connect(
            user="root",
            password="the_password_you_set",
            host="localhost",
            port=3306,
            database="noun_database"
        )
        query = "SELECT word FROM nouns;"
        cur = conn.cursor()
        cur.execute(query)

        for word_tuple in cur.fetchall():
            word_list.append(word_tuple[0])

        print(word_list)

    except mariadb.Error as e:
        print(f"Error connecting to MariaDB Platform: {e}")
        sys.exit(1)