File: init.py
import sys
import time
import importlib
create_database = importlib.import_module("create-database")
from settings import *
create_database.import_tables_structure()
time.sleep(2)
import_settings()
time.sleep(2)
File: create-database.py
import mysql.connector
import sys
import os
import traceback
def import_tables_structure():
try:
cnx = mysql.connector.connect(user='root', password='',host='localhost')
cursor = cnx.cursor()
sql_file = open("papinhio-player.sql","r",encoding="utf-8").read()
cursor.execute(sql_file,multi=True)
cnx.commit()
cursor.close()
cnx.close()
except Exception as e:
error_message = traceback.format_exc()
print(error_message)
#make folders
try:
os.mkdir(os.path.abspath("C:\\Users\\chris\\My Projects\\papinhio-player\\disket-box\\reports\\history-report"))
os.mkdir(os.path.abspath("C:\\Users\\chris\\My Projects\\papinhio-player\\disket-box\\reports\\listeners-statistics-report"))
os.mkdir(os.path.abspath("C:\\Users\\chris\\My Projects\\papinhio-player\\disket-box\\reports\\schedule-transmition-report"))
os.mkdir(os.path.abspath("C:\\Users\\chris\\My Projects\\papinhio-player\\disket-box\\reports\\week-report"))
os.mkdir(os.path.abspath("C:\\Users\\chris\\My Projects\\papinhio-player\\disket-box\\records\\ip-calls"))
except:
pass
#import_tables_structure()
File: papinhio-player.sql
-- phpMyAdmin SQL Dump
-- version 5.1.1deb5ubuntu1
-- https://www.phpmyadmin.net/
--
-- Host: localhost:3306
-- Generation Time: Aug 02, 2022 at 07:57 PM
-- Server version: 10.6.7-MariaDB-2ubuntu1.1
-- PHP Version: 8.1.2
SET SQL_MODE = "";
START TRANSACTION;
--
-- Database: `papinhio-player`
--
DROP DATABASE IF EXISTS `papinhio-player`;
CREATE DATABASE IF NOT EXISTS `papinhio-player` COLLATE utf8_general_ci;
-- --------------------------------------------------------
-- Table: settings
-- Used for saving program settings
-- such as auto_dj value (0,1)
-- Easy table (only 3 columns)
CREATE TABLE `papinhio-player`.`settings` (
`id` int(11) NOT NULL,
`setting` varchar(255) NOT NULL,
`value` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
--
-- Indexes for table `settings`
--
ALTER TABLE `papinhio-player`.`settings`
ADD PRIMARY KEY (`id`),
ADD UNIQUE KEY `setting` (`setting`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `settings`
--
ALTER TABLE `papinhio-player`.`settings`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
--
-- Constraints for dumped tables
--
COMMIT;
File: settings.py
import os
import sys
import mysql.connector
def import_settings():
system_sound_volume = 50
#default_font = "Times New Roman"
default_font = "Calibri"
default_font_size = 18
default_font_color = "#000000"
default_background_color = "#F1F1F1"
default_buttons_background = "#F5F5F5"
default_buttons_font_color = "#000000"
#default_style = "WindowsVista"
default_style = "Fusion"
#default_custome_theme = "light_blue.xml"
default_custome_theme = ""
sql = """ INSERT INTO `settings` (`keyword`, `current_value`) VALUES ( ?, ?) """
settings = []
settings.append(("input_device_sound_volume",100))
settings.append(("input_device_normalize",0))
settings.append(("input_device_pan",0))
settings.append(("input_device_low_frequency",20))
settings.append(("input_device_high_frequency",20000))
settings.append(("general_deck_sound_volume",50))
settings.append(("general_deck_normalize",0))
settings.append(("general_deck_pan",0))
settings.append(("general_deck_low_frequency",20))
settings.append(("general_deck_high_frequency",20000))
settings.append(("player_list_display","Προβολή λίστας"))
settings.append(("deck_1_relative_type",""))
settings.append(("deck_1_relative_number",0))
settings.append(("deck_1_current_duration_milliseconds",0))
settings.append(("deck_1_repeats",0))
settings.append(("deck_1_status",0))
settings.append(("deck_1_total_time_milliseconds",0))
settings.append(("deck_2_relative_type",""))
settings.append(("deck_2_relative_number",0))
settings.append(("deck_2_current_duration_milliseconds",0))
settings.append(("deck_2_repeats",0))
settings.append(("deck_2_status",0))
settings.append(("deck_2_total_time_milliseconds",0))
settings.append(("music_clip_deck_relative_type",""))
settings.append(("music_clip_deck_relative_number",0))
settings.append(("music_clip_deck_current_duration_milliseconds",0))
settings.append(("music_clip_deck_repeats",0))
settings.append(("music_clip_deck_status",0))
settings.append(("music_clip_deck_total_time_milliseconds",0))
settings.append(("default_font",default_font))
settings.append(("default_font_size",default_font_size))
settings.append(("default_font_color",default_font_color))
settings.append(("default_background_color",default_background_color))
settings.append(("default_button_background",default_buttons_background))
settings.append(("default_button_font_color",default_buttons_font_color))
settings.append(("default_style",default_style))
settings.append(("default_custome_theme",default_custome_theme))
settings.append(("player_field_change_position","1"))
settings.append(("player_field_play","1"))
settings.append(("player_field_title","1"))
settings.append(("player_field_last_play","1"))
settings.append(("player_field_next_play","1"))
settings.append(("player_field_image","1"))
settings.append(("player_field_prepare","1"))
settings.append(("player_field_play_now","1"))
settings.append(("player_field_remove","1"))
settings.append(("player_field_duration","1"))
settings.append(("player_field_artist","1"))
settings.append(("player_field_album","1"))
settings.append(("player_field_author","1"))
settings.append(("player_field_composer","1"))
settings.append(("player_field_year","1"))
settings.append(("player_field_description","1"))
settings.append(("player_field_from","1"))
settings.append(("player_field_rating","1"))
settings.append(("player_field_volume","1"))
settings.append(("player_field_normalize","1"))
settings.append(("player_field_pan","1"))
settings.append(("player_field_frequencies","1"))
settings.append(("player_field_repeat","1"))
settings.append(("player_field_open_file","1"))
settings.append(("player_fade_in","0"))
settings.append(("player_fade_out","0"))
settings.append(("program_component_tool_bar","1"))
settings.append(("program_component_time_lines","1"))
settings.append(("program_component_general_deck","1"))
settings.append(("program_component_deck_1","1"))
settings.append(("program_component_deck_2","1"))
settings.append(("program_component_music_clip_deck","1"))
settings.append(("program_component_speackers_deck","1"))
settings.append(("program_component_ip_calls","0"))
settings.append(("program_component_player_list","1"))
settings.append(("program_component_web_sites","1"))
settings.append(("program_component_scheduled_transmitions","1"))
settings.append(("repeat_player_list","1"))
settings.append(("auto_dj","1"))
settings.append(("current-working-directory",os.path.abspath(".")))
cnx = mysql.connector.connect(user='root', password='',host='localhost',database='papinhio-player')
cursor = cnx.cursor()
cursor.execute("SET GLOBAL sql_mode='';")
cnx.commit()
cursor.execute("USE `papinhio-player`;")
cnx.commit()
for setting in settings:
sql = "INSERT INTO `papinhio-player`.`settings` (`setting`,`value`) VALUES (%s, %s);"
cursor.execute(sql,(str(setting[0]),str(setting[1])))
cnx.commit()
cursor.close()
cnx.close()
If i run init.py the output is:
Traceback (most recent call last):
File "C:\Users\chris\My Projects\papinhio-player\database\create-database.py",
line 12, in import_tables_structure
cnx.commit()
File "C:\python\lib\site-packages\mysql\connector\connection_cext.py", line 42
5, in commit
self._cmysql.commit()
_mysql_connector.MySQLInterfaceError: Commands out of sync; you can't run this c
ommand now
Traceback (most recent call last):
File "C:\python\lib\site-packages\mysql\connector\connection_cext.py", line 53
8, in cmd_query
query_attrs=self._query_attrs)
_mysql_connector.MySQLInterfaceError: Table 'papinhio-player.settings' doesn't e
xist
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "init_2.py", line 10, in <module>
import_settings()
File "C:\Users\chris\My Projects\papinhio-player\database\settings.py", line 1
66, in import_settings
cursor.execute(sql,(str(setting[0]),str(setting[1])))
File "C:\python\lib\site-packages\mysql\connector\cursor_cext.py", line 271, i
n execute
raw_as_string=self._raw_as_string)
File "C:\python\lib\site-packages\mysql\connector\connection_cext.py", line 54
1, in cmd_query
sqlstate=exc.sqlstate)
mysql.connector.errors.ProgrammingError: 1146 (42S02): Table 'papinhio-player.se
ttings' doesn't exist
the database and table structure are created, but no insertion to settings table.
Then if i run python settings.py the insertions are done. What's the problem? Note: I tried multi=True in multi-query but then there is no database build in phpmyadmin.
File: create-database.py
The problem may was that with one mysql execution there is no enough time to apply the changes in mysql.
In this answer i use a sql file parser to execution one single query at one execution.
Now the settings insertions are done with no mistake.