Elixir: MariaDB のデータを作成 (Create)

Python のプログラムを、ChatGPT と Gemini に渡して得られた結果です。

Python のプログラム

maria_create.py

#! /usr/bin/python
#
#	maria_create.py
#
#					Mar/15/2024
#
# --------------------------------------------------------
import	sys
import	string
import	mysql.connector
#
#
# --------------------------------------------------------
def	table_insert_proc(cursor,dict_aa):
	for key in dict_aa:
		unit = dict_aa[key]
		try:
			sql_insert_proc(cursor,key,unit['name'], \
				unit['population'],unit['date_mod'])
		except Exception as ee:
			sys.stderr.write("*** error *** table_insert_proc ***\n")
			sys.stderr.write(str (ee) + "\n")
			sys.stderr.write(key + "\n")
#
# --------------------------------------------------------
def	sql_insert_proc(cursor_aa,id_in,name_in,ipop_in,date_mod_in):
#	print("*** sql_insert_proc ***")
#
	ft_aa="insert into cities(id,name,population,date_mod) values ("
	ft_bb ="'%s','%s',%d,'%s')" % (id_in,name_in,ipop_in,date_mod_in)
	sql_str=ft_aa + ft_bb
#	print(sql_str)
	cursor_aa.execute(sql_str)
#
# --------------------------------------------------------
def	create_table_proc(cursor_aa):
	sql_str="create table cities (id varchar(10) primary key, name varchar(20)," \
		+ " population int, date_mod date)"
#
	try:
		cursor_aa.execute(sql_str)
	except Exception as ee:
		sys.stderr.write("*** error *** create_table_proc ***\n")
		sys.stderr.write(str (ee) + "\n")
#
# --------------------------------------------------------
def	drop_table_proc(cursor_aa):
	sql_str="drop table cities"
	try:
		cursor_aa.execute(sql_str)
	except Exception as ee:
		sys.stderr.write("*** error *** drop_table_proc ***\n")
		sys.stderr.write(str (ee) + "\n")
#
# --------------------------------------------------------
def dict_append_proc(dict_aa,key,name,population,date_mod):
	dict_aa[key] = {'name':name,'population':population,'date_mod':date_mod}
#
	return dict_aa
# --------------------------------------------------------
def	data_prepare_proc():
#
	dict_aa = {} 
#
	dict_aa = dict_append_proc(dict_aa,'t3321','岡山',529176,'2003-9-20')
	dict_aa = dict_append_proc(dict_aa,'t3322','倉敷',791835,'2003-2-15')
	dict_aa = dict_append_proc(dict_aa,'t3323','津山',163754,'2003-8-18')
	dict_aa = dict_append_proc(dict_aa,'t3324','玉野',369172,'2003-1-9')
	dict_aa = dict_append_proc(dict_aa,'t3325','笠岡',237451,'2003-3-4')
	dict_aa = dict_append_proc(dict_aa,'t3326','井原',518397,'2003-5-21')
	dict_aa = dict_append_proc(dict_aa,'t3327','総社',248156,'2003-7-23')
	dict_aa = dict_append_proc(dict_aa,'t3328','高梁',478294,'2003-10-26')
	dict_aa = dict_append_proc(dict_aa,'t3329','新見',863751,'2003-12-15')
#
	return	dict_aa
#
# --------------------------------------------------------
sys.stderr.write("*** 開始 ***\n")
#
dict_aa = data_prepare_proc()
#
host_aa='localhost'
data_base = 'city'
user_aa ='scott'
password_aa = 'tiger123'
conn = mysql.connector.connect(user=user_aa, password=password_aa, \
                              host=host_aa,database=data_base)
#
cursor = conn.cursor()
#
drop_table_proc(cursor)
create_table_proc(cursor)
#
table_insert_proc(cursor,dict_aa)
#
conn.commit()
cursor.close()
conn.close()
#
sys.stderr.write("*** 終了 ***\n")
#
# --------------------------------------------------------

プロジェクトの用意

mix new maria_create

cd maria_create
mix test

ecto というライブラリーを使うのでバージョンを調べる

mix hex.info ecto

$ mix hex.info ecto
A toolkit for data mapping and language integrated query for Elixir

Config: {:ecto, "~> 3.11"}
（省略）

mix.exs に次を加える。

{:ecto, "~> 3.11"},

ライブラリーのインストール

mix deps.get

lib/maria_create.ex を置き換える

ChatGPT が作成したプログラム

lib/maria_create.ex

defmodule MariaCreate do
  use Ecto.Model

  @primary_key {:id, :string, autogenerate: false}
  schema "cities" do
    field :name, :string
    field :population, :integer
    field :date_mod, :date

    timestamps()
  end

  defp dict_append_proc(dict_aa, key, name, population, date_mod) do
    Map.put(dict_aa, key, %{name: name, population: population, date_mod: date_mod})
  end

  def data_prepare_proc() do
    dict_aa = %{}

    dict_aa
    |> dict_append_proc("t3321", "岡山", 529176, "2003-09-20")
    |> dict_append_proc("t3322", "倉敷", 791835, "2003-02-15")
    |> dict_append_proc("t3323", "津山", 163754, "2003-08-18")
    |> dict_append_proc("t3324", "玉野", 369172, "2003-01-09")
    |> dict_append_proc("t3325", "笠岡", 237451, "2003-03-04")
    |> dict_append_proc("t3326", "井原", 518397, "2003-05-21")
    |> dict_append_proc("t3327", "総社", 248156, "2003-07-23")
    |> dict_append_proc("t3328", "高梁", 478294, "2003-10-26")
    |> dict_append_proc("t3329", "新見", 863751, "2003-12-15")
  end

  def table_insert_proc(dict_aa) do
    for {key, unit} <- dict_aa do
      sql_insert_proc(key, unit.name, unit.population, unit.date_mod)
    end
  end

  def sql_insert_proc(id_in, name_in, ipop_in, date_mod_in) do
    Ecto.Adapters.SQL.query!(MariaCreate.Repo, "INSERT INTO cities (id, name, population, date_mod) VALUES ($1, $2, $3, $4)", [id_in, name_in, ipop_in, date_mod_in])
  end

  def create_table_proc() do
    Ecto.Adapters.SQL.query!(MariaCreate.Repo, """
      CREATE TABLE cities (
        id VARCHAR(10) PRIMARY KEY,
        name VARCHAR(20),
        population INT,
        date_mod DATE
      )
    """)
  end

  def drop_table_proc() do
    Ecto.Adapters.SQL.query!(MariaCreate.Repo, "DROP TABLE IF EXISTS cities")
  end
end

# Start the process
IO.puts("*** 開始 ***")
dict_aa = MariaCreate.data_prepare_proc()

# Database configuration
config = [
  username: "scott",
  password: "tiger123",
  hostname: "localhost",
  database: "city"
]

# Define a new Repo using Ecto
defmodule MariaCreate.Repo do
  use Ecto.Repo, otp_app: :mariadb
end

# Start the Repo
MariaCreate.Repo.start_link(config)

# Create or update the schema
MariaCreate.Repo.Migrations.up()

# Drop and recreate table
MariaCreate.drop_table_proc()
MariaCreate.create_table_proc()

# Insert data
MariaCreate.table_insert_proc(dict_aa)

# Commit and close connection
MariaCreate.Repo.rollback()
IO.puts("*** 終了 ***")

実行結果

$ mix run
Compiling 1 file (.ex)

== Compilation error in file lib/maria_create.ex ==
** (CompileError) lib/maria_create.ex:2: module Ecto.Model is not loaded and could not be found
    (elixir 1.14.0) expanding macro: Kernel.use/1
    lib/maria_create.ex:2: MariaCreate (module)

Gemini が作成したプログラム

lib/maria_create.ex

# Import necessary libraries
defmodule MyApp.MariaCreate do
  import Ecto.Query

  # Define a schema representing the "cities" table
  @schema_def %{
    __meta__: %{source: :cities},
    id: :string, primary_key: true,
    name: :string,
    population: :integer,
    date_mod: :date
  }

  # Function to prepare data (can be modified for different data sources)
  def prepare_data do
    [
      %{id: "t3321", name: "岡山", population: 529176, date_mod: ~D[2003-09-20]},
      %{id: "t3322", name: "倉敷", population: 791835, date_mod: ~D[2003-02-15]},
      # ... (other city data)
    ]
  end

  # Function to create the "cities" table (if it doesn't exist)
  def create_table(repo) do
    query = quote do
      CREATE TABLE IF NOT EXISTS unnest(:cities)
    end

    Ecto.execute(repo, query, @schema_def)
  end

  # Function to insert data into the "cities" table
  def insert_data(repo, data) do
    Enum.each(data, fn city ->
      changeset = %MyApp.City{} |> Ecto.Changeset.cast(city, @schema_def)
      Ecto.insert(repo, changeset)
    end)
  end

  # Main function to execute database operations
  def main do
    repo = Ecto.Repo.new(MyApp.Repo, adapter: Ecto.Adapters.MySQL)

    create_table(repo)

    data = prepare_data()
    insert_data(repo, data)

    IO.puts("*** 開始 ***")
    IO.puts("*** 終了 ***")
  end
end

実行結果

$ mix run
Compiling 1 file (.ex)

== Compilation error in file lib/maria_create.ex ==
** (SyntaxError) lib/maria_create.ex:26:14: syntax error before: 'TABLE'
    |
 26 |       CREATE TABLE IF NOT EXISTS unnest(:cities)
    |              ^
    (elixir 1.14.0) lib/kernel/parallel_compiler.ex:346: anonymous fn/5 in Kernel.ParallelCompiler.spawn_workers/7

Command R+ が作成したものをベースにして、改造したプログラム

lib/maria_create.ex

defmodule MariaCreate do
  def table_insert_proc(pid, dict_aa) do
    Enum.each(dict_aa, fn {key, %{name: name, population: population, date_mod: date_mod}} ->
      try do
        sql_insert_proc(pid, key, name, population, date_mod)
      rescue
        exception ->
          IO.puts("*** error *** table_insert_proc ***")
          IO.inspect(exception)
          IO.puts(key)
      end
    end)
  end

  def sql_insert_proc(pid, id_in, name_in, ipop_in, date_mod_in) do
    sql_str = "insert into cities(id, name, population, date_mod) values ('#{id_in}', '#{name_in}', #{ipop_in}, '#{date_mod_in}')"
    IO.puts(sql_str)
    Mariaex.query!(pid, sql_str, [])
  end

  def create_table_proc(pid) do
    sql_str = "create table cities (id varchar(10) primary key, name varchar(20), population int, date_mod date)"
    IO.puts(sql_str)
    Mariaex.query!(pid, sql_str, [])
  end

  def drop_table_proc(pid) do
    sql_str = "drop table cities"
    IO.puts(sql_str)
    Mariaex.query!(pid, sql_str, [])
  end

  def data_prepare_proc do
    dict_aa = %{
      "t3321" => %{name: "岡山", population: 529_176, date_mod: "2003-09-20"},
      "t3322" => %{name: "倉敷", population: 791_835, date_mod: "2003-02-15"},
      "t3323" => %{name: "津山", population: 163_754, date_mod: "2003-08-18"},
      "t3324" => %{name: "玉野", population: 369_172, date_mod: "2003-01-09"},
      "t3325" => %{name: "笠岡", population: 237_451, date_mod: "2003-03-04"},
      "t3326" => %{name: "井原", population: 518_397, date_mod: "2003-05-21"},
      "t3327" => %{name: "総社", population: 248_156, date_mod: "2003-07-23"},
      "t3328" => %{name: "高梁", population: 478_294, date_mod: "2003-10-26"},
      "t3329" => %{name: "新見", population: 863_751, date_mod: "2003-12-15"}
    }
    dict_aa
  end

  def main([]) do
    IO.puts("*** 開始 ***")

    dict_aa = data_prepare_proc()

    host = "localhost"
    user = "scott"
    password = "tiger123"
    database = "city"

#
   {:ok, pid} = Mariaex.start_link(hostname: host, username: user, password: password, database: database)
#
    drop_table_proc(pid)
    create_table_proc(pid)

    table_insert_proc(pid, dict_aa)

    IO.puts("*** 終了 ***")
  end
end

# MariaCreate.main()

実行結果

$ mix run -e "MariaCreate.main([])"
Compiling 1 file (.ex)
*** 開始 ***
drop table cities
create table cities (id varchar(10) primary key, name varchar(20), population int, date_mod date)
insert into cities(id, name, population, date_mod) values ('t3321', '岡山', 529176, '2003-09-20')
insert into cities(id, name, population, date_mod) values ('t3322', '倉敷', 791835, '2003-02-15')
insert into cities(id, name, population, date_mod) values ('t3323', '津山', 163754, '2003-08-18')
insert into cities(id, name, population, date_mod) values ('t3324', '玉野', 369172, '2003-01-09')
insert into cities(id, name, population, date_mod) values ('t3325', '笠岡', 237451, '2003-03-04')
insert into cities(id, name, population, date_mod) values ('t3326', '井原', 518397, '2003-05-21')
insert into cities(id, name, population, date_mod) values ('t3327', '総社', 248156, '2003-07-23')
insert into cities(id, name, population, date_mod) values ('t3328', '高梁', 478294, '2003-10-26')
insert into cities(id, name, population, date_mod) values ('t3329', '新見', 863751, '2003-12-15')
*** 終了 ***