only_full_group_by エラー: 1 2 3 4 5 6 OperationalError: ( 1055, "Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'server.sales_sales.sales_status' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by") 対処: sql_mod に traditional を指定する ...
pandas: MySQL sqlalchemy で mysqlclient を使うには、まず mysqlclient をインストールする必要があります。次に、SQLAlchemy のエンジン構成で mysqlclient を使用するように設定します。以下は、Qiita の記事 ¹ からの例です。 1 2 3 from sqlalchemy import create_engine engine = create_engine('mysql+mysqldb://user:password@host/dbname', echo=True) また、CData Python Connector for MySQL³ を使用することもできます。これは、SQLAlchemy ORM を使用して Python で MySQL データに連携するためのドライバです。 1 2 from sqlalchemy import create_engine engine = create_engine("mysql+mysqlconnector://user:password@host/dbname") どちらの方法でも、SQLAlchemy で MySQL に接続することができます。 ソース: Bing との会話 2023/6/7 (1) 【Python】SQLAlchemy で MySQL に接続する - Qiita. https://qiita.com/curry__30/items/432a21426c02a68e77e8. (2) SQLAlchemy ORM を使って、Python で MySQL データに連携 …. https://www.cdata.com/jp/kb/tech/mysql-python-sqlalchemy.rst. (3) 【Python】SQLAlchemy を試してみる - Qiita. https://qiita.com/ktamido/items/ebdbe5a85dbc3e6004ae. django .env に django の DATABASE_URL が定義されているとする ...
utf8mb4_0900_ai_ci 「utf8mb4_0900_ai_ci」という照合順序は、MySQL 8.0 から標準になった照合順序で、Unicode 9.0 の文字セットをサポートしています。¹ この照合順序は、文字列の比較において、アクセントや大小文字を区別せずに比較することができます。¹ また、日本語のようなマルチバイト文字を含む文字列を扱う場合にも適しています。¹¹: MySQL 8.0 の照合順序で標準になった「utf8mb4_0900_ai_ci」とは - Qiita ソース: Bing との会話 2023/5/31 (1) MySQL 8.0 の照合順序で標準になった「utf8mb4_0900_ai_ci …. https://qiita.com/seltzer/items/8b5d8a61591e72715d5b. (2) MySQL :: MySQL 8.0 リファレンスマニュアル :: 10.5 …. https://dev.mysql.com/doc/refman/8.0/ja/charset-applications.html. (3) MySQL :: MySQL 8.0 リファレンスマニュアル :: 10.4 接続文字 …. https://dev.mysql.com/doc/refman/8.0/ja/charset-connection.html. 半角カナの濁点ありは照合順序の設定では解決できない utf8mb4_0900_ai_ci での検索: where 結果 ベル ヘル, ベル, ﾍﾙ (つまり、 ﾍﾞﾙ は一致しない) ヘル ヘル, ベル, ﾍﾙ (つまり、 ﾍﾞﾙ は一致しない) ﾍﾙ ヘル, ベル, ﾍﾙ (つまり、 ﾍﾞﾙ は一致しない) ﾍﾞﾙ ﾍﾞﾙ (のみ) django-filter: method フィルターで対応 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 from functools import reduce from operator import or_ import django_filters as DF import jaconv def filter_icontains_mix(queryset, name, value): """全角半角を区別しないで検索(濁点対応)""" if not value: return queryset values = set([jaconv.h2z(value), jaconv.z2h(value)]) def _query(v): return Q(**{f"{name}__icontains": v}) query = reduce(or_, map(_query, values)) return queryset.filter(query) class OrderFilter(DF.FilterSet): ... customer_name__contains = DF.CharFilter( field_name="customer_name", lookup_expr="icontains", method=filter_icontains_mix ) ...
MySQL: ERROR 1553 (HY000): Cannot drop index ‘uniq_profit’: needed in a foreign key constraint MySQL で一意制約が削除できない 1 2 3 UNIQUE KEY `uniq_profit` (`sales_id`,`accounting_status`), CONSTRAINT `costs_profit_sales_id_d56dec15_fk_sales_sales_id` FOREIGN KEY (`sales_id`) REFERENCES `sales_sales` (`id`), CONSTRAINT `costs_profit_chk_1` CHECK ((`fiscal_year` >= 0)) 1 2 ALTER TABLE `costs_profit` DROP FOREIGN KEY `costs_profit_sales_id_d56dec15_fk_sales_sales_id`, DROP INDEX `uniq_profit`; ALTER TABLE `costs_profit` ADD CONSTRAINT `costs_profit_sales_id_d56dec15_fk_sales_sales_id` FOREIGN KEY (`sales_id`) REFERENCES `sales_sales` (`id`); 実行: 1 2 3 4 5 6 7 mysql> ALTER TABLE `costs_profit` DROP FOREIGN KEY `costs_profit_sales_id_d56dec15_fk_sales_sales_id`, DROP INDEX `uniq_profit`; Query OK, 0 rows affected (0.20 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE `costs_profit` ADD CONSTRAINT `costs_profit_sales_id_d56dec15_fk_sales_sales_id` FOREIGN KEY (`sales_id`) REFERENCES `sales_sales` (`id`); Query OK, 16 rows affected (0.17 sec) Records: 16 Duplicates: 0 Warnings: 0 Django Migration の失敗 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 from django.db import migrations, models class Migration(migrations.Migration): dependencies = [ ('partners', '0003_auto_20230426_0908'), ] operations = [ migrations.RemoveConstraint( model_name='partneruserrole', name='uniq_partneruserrole', ), migrations.AddConstraint( model_name='partneruserrole', constraint=models.UniqueConstraint(fields=('partner', 'partneruser', 'permission'), name='uniq_partneruserrole'), ), ] 制約 検索 1 2 3 4 5 6 7 8 9 SELECT DISTINCT A.CONSTRAINT_NAME, A.TABLE_NAME, A.COLUMN_NAME, B.CONSTRAINT_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE as A JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE as B ON A.TABLE_NAME = B.TABLE_NAME WHERE A.TABLE_NAME = 'partners_partneruserrole' AND B.CONSTRAINT_NAME like 'uniq_%' AND A.CONSTRAINT_NAME not like 'uniq_%' AND A.CONSTRAINT_NAME != 'PRIMARY'; +-----------------------------------------------------------+--------------------------+----------------+----------------------+ | CONSTRAINT_NAME | TABLE_NAME | COLUMN_NAME | CONSTRAINT_NAME | +-----------------------------------------------------------+--------------------------+----------------+----------------------+ | partners_partneruser_partneruser_id_d59f67cc_fk_partners_ | partners_partneruserrole | partneruser_id | uniq_partneruserrole | | partners_partneruser_partner_id_852ab185_fk_partners_ | partners_partneruserrole | partner_id | uniq_partneruserrole | | partners_partneruser_permission_id_031e3996_fk_auth_perm | partners_partneruserrole | permission_id | uniq_partneruserrole | +-----------------------------------------------------------+--------------------------+----------------+----------------------+ 再設定 1 2 3 4 5 6 7 ALTER TABLE `partners_partneruserrole` DROP FOREIGN KEY `partners_partneruser_partneruser_id_d59f67cc_fk_partners_`; ALTER TABLE `partners_partneruserrole` DROP FOREIGN KEY `partners_partneruser_partner_id_852ab185_fk_partners_`; ALTER TABLE `partners_partneruserrole` DROP FOREIGN KEY `partners_partneruser_partner_id_852ab185_fk_partners_`, DROP INDEX `uniq_profit`; ALTER TABLE `partners_partneruserrole` ADD CONSTRAINT `partners_partneruser_partneruser_id_d59f67cc_fk_partners_` FOREIGN KEY (`partneruser_id`) REFERENCES `partners_partneruser` (`id`); ALTER TABLE `partners_partneruserrole` ADD CONSTRAINT `partners_partneruser_partner_id_852ab185_fk_partners_` FOREIGN KEY (`partner_id`) REFERENCES `partners_partner` (`id`); ALTER TABLE `partners_partneruserrole` ADD CONSTRAINT `partners_partneruser_permission_id_031e3996_fk_auth_perm` FOREIGN KEY (`permission_id`) REFERENCES `auth_permission` (`user_ptr_id`); 最終スキーマ 1 2 3 4 5 6 7 8 9 10 11 12 DROP TABLE IF EXISTS `partners_partneruserrole`; CREATE TABLE `partners_partneruserrole` ( `id` bigint NOT NULL AUTO_INCREMENT, .... PRIMARY KEY (`id`), UNIQUE KEY `uniq_partneruserrole` (`partner_id`,`partneruser_id`,`permission_id`), KEY `partners_partneruser_partneruser_id_d59f67cc_fk_partners_` (`partneruser_id`), KEY `partners_partneruser_permission_id_031e3996_fk_auth_perm` (`permission_id`), CONSTRAINT `partners_partneruser_partner_id_852ab185_fk_partners_` FOREIGN KEY (`partner_id`) REFERENCES `partners_partner` (`id`), CONSTRAINT `partners_partneruser_partneruser_id_d59f67cc_fk_partners_` FOREIGN KEY (`partneruser_id`) REFERENCES `partners_partneruser` (`user_ptr_id`), CONSTRAINT `partners_partneruser_permission_id_031e3996_fk_auth_perm` FOREIGN KEY (`permission_id`) REFERENCES `auth_permission` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Celery: タスクの結果をMySQLで確認する PYPI: django-celery-results = "^2.5.0" settings: 1 2 3 CELERY_RESULT_BACKEND = "django-db" CELERY_RESULT_EXTENDED = True INSTALLED_APPS += ["django_celery_results"] Database Result Backend It seems that you are using Django and Celery to run asynchronous tasks and store the results in a database. One possible reason why the result data is always null is that you are not returning anything from your task function². For example, if your task function looks like this: ...
ERROR 3780 (HY000) at line 1406622: Referencing column 'os_branch_id' and referenced column 'id' in foreign key constraint '***' are incompatible. 1 2 3 4 5 6 7 8 9 10 11 CREATE TABLE `gas_bombehouse` ( `id` int NOT NULL AUTO_INCREMENT, `code` varchar(50) NOT NULL, .... `os_branch_id` bigint DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `code` (`code`), KEY `gas_bombehouse_os_branch_id_d35d17d3_fk_outsource` (`os_branch_id`), CONSTRAINT `gas_bombehouse_os_branch_id_d35d17d3_fk` FOREIGN KEY (`os_branch_id`) REFERENCES `outsources_outsourcebranch` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb3; /*!40101 SET character_set_client = @saved_cs_client */; 1 2 3 4 5 6 7 CREATE TABLE `outsources_outsourcebranch` ( `id` bigint NOT NULL AUTO_INCREMENT, ... PRIMARY KEY (`id`), ) ENGINE=InnoDB AUTO_INCREMENT=9751 DEFAULT CHARSET=utf8mb3; /*!40101 SET character_set_client = @saved_cs_client */; 原因 リモートの mysqldump を ローカルにそのまま取り込もうとしていた 古いスキーマが残っていて、そのidがint であったため。 DROP TABLE IF EXISTS table CREATE TABLE table が実行されるまで、古いスキーマが残っている、ということ。 1 2 3 4 5 DROP TABLE IF EXISTS `outsources_outsourcebranch`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `outsources_outsourcebranch` .... 解決 DROP DATABASE & CREATE DATABASE して、更地にmysqldumpをロードする 資料 How to fix MySQL error 1215 Cannot add foreign key constraint ...
how to replace double back slash character to single one from MySQL JSON_OBJECT function results for column including JSON string You can use the MySQL function REPLACE() to replace double backslashes with single backslashes in a JSON object. Here’s an example: 1 SELECT REPLACE(JSON_OBJECT(column_name), '\\\\\\\\', '\\\\') FROM table_name; This will return the column values with double backslashes replaced with single backslashes. If you want to insert a JSON object literal with single backslashes instead of double backslashes, you can set the NO_BACKSLASH_ESCAPES SQL mode⁴. When this mode is set, a single backslash instead of double backslashes can be used to insert the JSON object literal, and the backslashes are preserved⁴. If you use the JSON_OBJECT() function when performing the insert and this mode is set, you must alternate single and double quotes⁴. ...
MySQL -> Snowflake データ移行後の比較 対象フィールド型(Python) 集計 MySQL Snowflake 補足 Decimal 単純合計 SUM(field) SUM(field) float 単純合計 SUM(field) SUM(field) int 単純合計 SUM(field) SUM(field) str MD5の合計 SUM(CONV(SUBSTRING(MD5(field)), 1, 8), 16, 10) SUM(TO_NUMBER(SUBSTR(MD5(field), 1, 8), ‘XXXXXXXX’)) MySQLの整数が小さいので先頭8バイトだけ datetime Unixエポック合計 SUM(UNIX_TIMESTAMP(field)) SUM(ZEROIFNULL(DATE_PART(EPOCH_SECOND, field)) - 32400) Snowflakeはローカルタイムゾーン date Unixエポック合計 SUM(UNIX_TIMESTAMP(field)) SUM(ZEROIFNULL(DATE_PART(EPOCH_SECOND, field)) - 32400) Snowflakeはローカルタイムゾーン time 秒数の合計 SUM(TIME_TO_SEC(field)) SUM(TIMEDIFF(SECOND, ‘00:00:00’::TIME, field::TIME))
Laravel マイグレーション Laravel マイグレーションを1つずつ戻す 1 $ php artisan migrate:rollback --step=1 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 MySQL [mysite]> select * from migrations; +----+--------------------------------------------------------------+-------+ | id | migration | batch | +----+--------------------------------------------------------------+-------+ | 1 | 2014_10_12_000000_create_users_table | 1 | | 2 | 2014_10_12_100000_create_password_resets_table | 1 | | 3 | 2019_08_19_000000_create_failed_jobs_table | 1 | | 4 | 2019_12_14_000001_create_personal_access_tokens_table | 1 | | 5 | 2021_01_27_074301_create_sessions_table | 1 | | 6 | 2021_02_03_055949_create_products_table | 1 | | 7 | 2021_02_03_060047_create_news_table | 1 | | 8 | 2021_02_08_024947_create_credit_cards_table | 1 | | 9 | 2021_02_08_025236_create_licenses_table | 1 | | 10 | 2021_02_08_060434_create_orders_table | 1 | | 11 | 2021_02_18_124506_create_options_table | 1 | | 12 | 2021_03_10_093455_update_string_fields_to_text | 2 | | 13 | 2021_04_09_123358_add_users_deleted_at_field | 3 | | 14 | 2021_04_09_123459_add_licenses_subscription_cancelled_fields | 3 | +----+--------------------------------------------------------------+-------+ 14 rows in set (0.002 sec) 1 % php artisan migrate:rollback --step=1 ************************************** * Application In Production! * ************************************** Do you really wish to run this command? (yes/no) [no]: > yes Rolling back: 2021_04_09_123459_add_licenses_subscription_cancelled_fields Rolled back: 2021_04_09_123459_add_licenses_subscription_cancelled_fields (165.69ms) 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 MySQL [mysite]> select * from migrations; +----+-------------------------------------------------------+-------+ | id | migration | batch | +----+-------------------------------------------------------+-------+ | 1 | 2014_10_12_000000_create_users_table | 1 | | 2 | 2014_10_12_100000_create_password_resets_table | 1 | | 3 | 2019_08_19_000000_create_failed_jobs_table | 1 | | 4 | 2019_12_14_000001_create_personal_access_tokens_table | 1 | | 5 | 2021_01_27_074301_create_sessions_table | 1 | | 6 | 2021_02_03_055949_create_products_table | 1 | | 7 | 2021_02_03_060047_create_news_table | 1 | | 8 | 2021_02_08_024947_create_credit_cards_table | 1 | | 9 | 2021_02_08_025236_create_licenses_table | 1 | | 10 | 2021_02_08_060434_create_orders_table | 1 | | 11 | 2021_02_18_124506_create_options_table | 1 | | 12 | 2021_03_10_093455_update_string_fields_to_text | 2 | | 13 | 2021_04_09_123358_add_users_deleted_at_field | 3 | +----+-------------------------------------------------------+-------+ 13 rows in set (0.002 sec)
[intrinsic column flags] - PK: primary key (column is part of a pk) - NN: not null (column is nullable) - UQ: unique (column is part of a unique key) - AI: auto increment (the column is auto incremented when rows are inserted) [additional data type flags, depend on used data type] - BIN: binary (if dt is a blob or similar, this indicates that is binary data, rather than text) - UN: unsigned (for integer types, see docs: "10.2. Numeric Types") - ZF: zero fill (rather a display related flag, see docs: "10.2. Numeric Types") MySQLでログを出力してみる ERROR 2049 (HY000): Connection using old (pre-4.1.1) authentication protocol refused (client option ‘secure_auth’ enabled) MySQLのバージョンがちがうとパスワードハッシュが違うのでログインできなくなる