gpt4 book ai didi

python - sqlalchemy 有没有办法使用预先存在的数据库生成模型?

转载 作者:行者123 更新时间:2023-11-29 18:22:13 31 4
gpt4 key购买 nike

我正在分析我所在大学的能源数据。他们给了我们两个文件。

./00_schema.sql

-- MySQL dump 10.13  Distrib 5.5.40, for debian-linux-gnu (i686)
--
-- Host: ######## Database: power
-- ------------------------------------------------------
-- Server version 5.5.35-0ubuntu0.12.04.2

/*!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 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `device_description`
--

DROP TABLE IF EXISTS `device_description`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `device_description` (
`dd_id` int(11) NOT NULL AUTO_INCREMENT,
`dd_host_name` varchar(64) NOT NULL,
`dd_dev_name` varchar(64) NOT NULL,
`dd_dev_id` int(11) NOT NULL,
PRIMARY KEY (`dd_id`),
KEY `host_TO_Device_idx` (`dd_host_name`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf32 COMMENT='Device Name to device id definitions';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `host_description`
--

DROP TABLE IF EXISTS `host_description`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `host_description` (
`host_id` int(11) NOT NULL,
`host_name` varchar(64) NOT NULL,
`host_ip` varchar(15) NOT NULL,
PRIMARY KEY (`host_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf32 COMMENT='Host to host_id definitions';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `measurement_description`
--

DROP TABLE IF EXISTS `measurement_description`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `measurement_description` (
`meas_id` int(11) NOT NULL,
`meas_name` varchar(64) DEFAULT NULL,
PRIMARY KEY (`meas_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf32 COMMENT='Measurement to measurement_id definitions';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `tuc_power_measurements`
--

DROP TABLE IF EXISTS `tuc_power_measurements`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `tuc_power_measurements` (
`pm_id` int(11) NOT NULL AUTO_INCREMENT,
`pm_host_id` int(11) NOT NULL,
`pm_dev_id` int(11) NOT NULL,
`pm_meas_id` int(11) NOT NULL,
`pm_meas_value` decimal(12,6) NOT NULL,
`pm_meas_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`pm_id`),
KEY `pm_host_To_host_descr_idx` (`pm_host_id`),
KEY `pm_dev_To_dev_descr_idx` (`pm_dev_id`),
KEY `pm_meas_To_meas_descr_idx` (`pm_meas_id`),
KEY `pm_meas_timestamp` (`pm_meas_timestamp`),
CONSTRAINT `pm_host_To_host_descr` FOREIGN KEY (`pm_host_id`) REFERENCES `host_description` (`host_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `pm_meas_To_meas_descr` FOREIGN KEY (`pm_meas_id`) REFERENCES `measurement_description` (`meas_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=9409956 DEFAULT CHARSET=utf32;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!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 */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2014-12-15 10:14:10

./00_basedata.sql

-- MySQL dump 10.13  Distrib 5.5.40, for debian-linux-gnu (i686)
--
-- Host: ###### Database: power
-- ------------------------------------------------------
-- Server version 5.5.35-0ubuntu0.12.04.2

/*!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 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Dumping data for table `device_description`
--

LOCK TABLES `device_description` WRITE;
/*!40000 ALTER TABLE `device_description` DISABLE KEYS */;
INSERT INTO `device_description` VALUES (1,'powerMeter-ARXE','Transformer 1',1),(2,'powerMeter-ARXE','Transformer 2',2),(3,'powerMeter-ARXE','D1 DEH',3),(4,'powerMeter-ARXE','D1 Generator',4),(5,'powerMeter-ARXE','A1 DataCenter',5),(6,'powerMeter-RESTAURANT','Restaurant',1),(7,'powerMeter-M1A','MHXOP 1',1),(8,'powerMeter-M1A','MHXOP 2',2),(9,'powerMeter-M1A','MHPER A - DEH',3),(10,'powerMeter-M1A','MHPER A - Generator',4),(11,'powerMeter-L1A','MHPER B - DEH',1),(12,'powerMeter-L1A','MHPER B - Generator',2),(13,'powerMeter-L1A','Transformer 1',3),(14,'powerMeter-L1A','Transformer 2',4),(15,'powerMeter-H1A','ESTIA',1),(16,'powerMeter-Z1A','GYM',1),(17,'powerMeter-D4A','MPD - DEH',1),(19,'powerMeter-D4A','MPD - Generator',2);
/*!40000 ALTER TABLE `device_description` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Dumping data for table `host_description`
--

LOCK TABLES `host_description` WRITE;
/*!40000 ALTER TABLE `host_description` DISABLE KEYS */;
INSERT INTO `host_description` VALUES (1,'powerMeter-ARXE','147.27.245.11'),(2,'powerMeter-D4A','147.27.245.18'),(3,'powerMeter-H1A','147.27.245.16'),(4,'powerMeter-Z1A','147.27.245.17'),(5,'powerMeter-L1A','147.27.245.15'),(6,'powerMeter-M1A','147.27.245.13'),(7,'powerMeter-RESTAURANT','147.27.245.12');
/*!40000 ALTER TABLE `host_description` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Dumping data for table `measurement_description`
--

LOCK TABLES `measurement_description` WRITE;
/*!40000 ALTER TABLE `measurement_description` DISABLE KEYS */;
INSERT INTO `measurement_description` VALUES (1,'Power Active - total'),(2,'Power Factor - average'),(3,'Frequency - Hz'),(4,'Current phase 1 to Neutral'),(5,'Current phase 2 to Neutral'),(6,'Current phase 3 to Neutral'),(7,'Voltage phase 1 to Neutral'),(8,'Voltage phase 2 to Neutral'),(9,'Voltage phase 3 to Neutral');
/*!40000 ALTER TABLE `measurement_description` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!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 */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2014-12-15 10:15:47

然后我运行命令:

mysql -uroot -p -e "DROP DATABASE IF EXISTS power;"
mysql -uroot -p -e 'create database power;'
mysql -uroot -p power < mysql_functions_procedures_etc/00_schema.sql
mysql -uroot -p power < mysql_functions_procedures_etc/00_basedata.sql

一切都好。我还用 mysql workbench 检查了它。

  1. 如何在不定义 db.Model 的情况下将 sqlalchemy 连接到它? (我知道如何通过在 Flask 中处理 Web 应用程序来定义模型等)

  2. 我想使用 orm。

  3. 或者,如果不定义模型就不可能,是否有办法提取 结构并将模型从 mysql 加载到 sqlalchemy 中。

最佳答案

明白了。

pip install sqlacodegen

在终端中运行

sqlacodegen mysql://root:pass@localhost/power > models.py

关于python - sqlalchemy 有没有办法使用预先存在的数据库生成模型?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/46476612/

31 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com