manual_sql.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "5758d82d",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import re\n",
    "import linecache"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "592ba190",
   "metadata": {},
   "outputs": [],
   "source": [
    "db_path = \"/Users/oscarbalcells/Desktop/AI/task4/datasets/cepdb_2013-06-21.sql\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "611c4874",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SET AUTOCOMMIT = 0;\n",
      "SET FOREIGN_KEY_CHECKS=0;\n",
      "SET unique_checks=0;\n",
      "-- MySQL dump 10.11\n",
      "--\n",
      "-- Host: localhost    Database: cepdb\n",
      "-- ------------------------------------------------------\n",
      "-- Server version\t5.5.25a\n",
      "\n",
      "/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;\n",
      "/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;\n",
      "/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;\n",
      "/*!40101 SET NAMES utf8 */;\n",
      "/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;\n",
      "/*!40103 SET TIME_ZONE='+00:00' */;\n",
      "/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;\n",
      "/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;\n",
      "/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;\n",
      "/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;\n",
      "\n",
      "--\n",
      "-- Table structure for table `auth_group`\n",
      "--\n",
      "\n",
      "DROP TABLE IF EXISTS `auth_group`;\n",
      "SET @saved_cs_client     = @@character_set_client;\n",
      "SET character_set_client = utf8;\n",
      "CREATE TABLE `auth_group` (\n",
      "  `id` int(11) NOT NULL AUTO_INCREMENT,\n",
      "  `name` varchar(80) COLLATE utf8_bin NOT NULL,\n",
      "  PRIMARY KEY (`id`),\n",
      "  UNIQUE KEY `name` (`name`)\n",
      ") ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;\n",
      "SET character_set_client = @saved_cs_client;\n",
      "\n",
      "--\n",
      "-- Dumping data for table `auth_group`\n",
      "--\n",
      "-- ORDER BY:  `id`\n",
      "\n",
      "LOCK TABLES `auth_group` WRITE;\n",
      "/*!40000 ALTER TABLE `auth_group` DISABLE KEYS */;\n",
      "/*!40000 ALTER TABLE `auth_group` ENABLE KEYS */;\n",
      "UNLOCK TABLES;\n",
      "\n",
      "--\n",
      "-- Table structure for table `auth_group_permissions`\n",
      "--\n",
      "\n",
      "DROP TABLE IF EXISTS `auth_group_permissions`;\n",
      "SET @saved_cs_client     = @@character_set_client;\n",
      "SET character_set_client = utf8;\n",
      "CREATE TABLE `auth_group_permissions` (\n",
      "  `id` int(11) NOT NULL AUTO_INCREMENT,\n",
      "  `group_id` int(11) NOT NULL,\n",
      "  `permission_id` int(11) NOT NULL,\n",
      "  PRIMARY KEY (`id`),\n",
      "  UNIQUE KEY `group_id` (`group_id`,`permission_id`),\n",
      "  KEY `auth_group_permissions_bda51c3c` (`group_id`),\n",
      "  KEY `auth_group_permissions_1e014c8f` (`permission_id`),\n",
      "  CONSTRAINT `group_id_refs_id_3cea63fe` FOREIGN KEY (`group_id`) REFERENCES `auth_group` (`id`),\n",
      "  CONSTRAINT `permission_id_refs_id_a7792de1` FOREIGN KEY (`permission_id`) REFERENCES `auth_permission` (`id`)\n",
      ") ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;\n",
      "SET character_set_client = @saved_cs_client;\n",
      "\n",
      "--\n",
      "-- Dumping data for table `auth_group_permissions`\n",
      "--\n",
      "-- ORDER BY:  `id`\n",
      "\n",
      "LOCK TABLES `auth_group_permissions` WRITE;\n",
      "/*!40000 ALTER TABLE `auth_group_permissions` DISABLE KEYS */;\n",
      "/*!40000 ALTER TABLE `auth_group_permissions` ENABLE KEYS */;\n",
      "UNLOCK TABLES;\n",
      "\n",
      "--\n",
      "-- Table structure for table `auth_permission`\n",
      "--\n",
      "\n",
      "DROP TABLE IF EXISTS `auth_permission`;\n",
      "SET @saved_cs_client     = @@character_set_client;\n",
      "SET character_set_client = utf8;\n",
      "CREATE TABLE `auth_permission` (\n",
      "  `id` int(11) NOT NULL AUTO_INCREMENT,\n",
      "  `name` varchar(50) COLLATE utf8_bin NOT NULL,\n",
      "  `content_type_id` int(11) NOT NULL,\n",
      "  `codename` varchar(100) COLLATE utf8_bin NOT NULL,\n",
      "  PRIMARY KEY (`id`),\n",
      "  UNIQUE KEY `content_type_id` (`content_type_id`,`codename`),\n",
      "  KEY `auth_permission_e4470c6e` (`content_type_id`),\n",
      "  CONSTRAINT `content_type_id_refs_id_728de91f` FOREIGN KEY (`content_type_id`) REFERENCES `django_content_type` (`id`)\n",
      ") ENGINE=InnoDB AUTO_INCREMENT=49 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;\n",
      "SET character_set_client = @saved_cs_client;\n",
      "\n",
      "--\n",
      "-- Dumping data for table `auth_permission`\n",
      "--\n",
      "-- ORDER BY:  `id`\n",
      "\n",
      "LOCK TABLES `auth_permission` WRITE;\n",
      "/*!40000 ALTER TABLE `auth_permission` DISABLE KEYS */;\n",
      "INSERT INTO `auth_permission` VALUES (1,'Can add permission',1,'add_permission'),(2,'Can change permission',1,'change_permission'),(3,'Can delete permission',1,'delete_permission'),(4,'Can add group',2,'add_group'),(5,'Can change group',2,'change_group'),(6,'Can delete group',2,'delete_group'),(7,'Can add user',3,'add_user'),(8,'Can change user',3,'change_user'),(9,'Can delete user',3,'delete_user'),(10,'Can add content type',4,'add_contenttype'),(11,'Can change content type',4,'change_contenttype'),(12,'Can delete content type',4,'delete_contenttype'),(13,'Can add session',5,'add_session'),(14,'Can change session',5,'change_session'),(15,'Can delete session',5,'delete_session'),(16,'Can add site',6,'add_site'),(17,'Can change site',6,'change_site'),(18,'Can delete site',6,'delete_site'),(19,'Can add log entry',7,'add_logentry'),(20,'Can change log entry',7,'change_logentry'),(21,'Can delete log entry',7,'delete_logentry'),(22,'Can add mol graph',8,'add_molgraph'),(23,'Can change mol graph',8,'change_molgraph'),(24,'Can delete mol graph',8,'delete_molgraph'),(25,'Can add mol geom',9,'add_molgeom'),(26,'Can change mol geom',9,'change_molgeom'),(27,'Can delete mol geom',9,'delete_molgeom'),(28,'Can add calc qc set1',10,'add_calcqcset1'),(29,'Can change calc qc set1',10,'change_calcqcset1'),(30,'Can delete calc qc set1',10,'delete_calcqcset1'),(31,'Can add calib qc set1',11,'add_calibqcset1'),(32,'Can change calib qc set1',11,'change_calibqcset1'),(33,'Can delete calib qc set1',11,'delete_calibqcset1'),(34,'Can add scharber',12,'add_scharber'),(35,'Can change scharber',12,'change_scharber'),(36,'Can delete scharber',12,'delete_scharber'),(37,'Can add graph stat',13,'add_graphstat'),(38,'Can change graph stat',13,'change_graphstat'),(39,'Can delete graph stat',13,'delete_graphstat'),(40,'Can add geom stat',14,'add_geomstat'),(41,'Can change geom stat',14,'change_geomstat'),(42,'Can delete geom stat',14,'delete_geomstat'),(43,'Can add graph score',15,'add_graphscore'),(44,'Can change graph score',15,'change_graphscore'),(45,'Can delete graph score',15,'delete_graphscore'),(46,'Can add geom score',16,'add_geomscore'),(47,'Can change geom score',16,'change_geomscore'),(48,'Can delete geom score',16,'delete_geomscore');\n",
      "/*!40000 ALTER TABLE `auth_permission` ENABLE KEYS */;\n",
      "UNLOCK TABLES;\n",
      "\n",
      "--\n",
      "-- Table structure for table `auth_user`\n",
      "--\n",
      "\n",
      "DROP TABLE IF EXISTS `auth_user`;\n",
      "SET @saved_cs_client     = @@character_set_client;\n",
      "SET character_set_client = utf8;\n",
      "CREATE TABLE `auth_user` (\n",
      "  `id` int(11) NOT NULL AUTO_INCREMENT,\n",
      "  `username` varchar(30) COLLATE utf8_bin NOT NULL,\n",
      "  `first_name` varchar(30) COLLATE utf8_bin NOT NULL,\n",
      "  `last_name` varchar(30) COLLATE utf8_bin NOT NULL,\n",
      "  `email` varchar(75) COLLATE utf8_bin NOT NULL,\n",
      "  `password` varchar(128) COLLATE utf8_bin NOT NULL,\n",
      "  `is_staff` tinyint(1) NOT NULL,\n",
      "  `is_active` tinyint(1) NOT NULL,\n",
      "  `is_superuser` tinyint(1) NOT NULL,\n",
      "  `last_login` datetime NOT NULL,\n",
      "  `date_joined` datetime NOT NULL,\n",
      "  PRIMARY KEY (`id`),\n",
      "  UNIQUE KEY `username` (`username`)\n",
      ") ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;\n",
      "SET character_set_client = @saved_cs_client;\n",
      "\n",
      "--\n",
      "-- Dumping data for table `auth_user`\n",
      "--\n",
      "-- ORDER BY:  `id`\n",
      "\n",
      "LOCK TABLES `auth_user` WRITE;\n",
      "/*!40000 ALTER TABLE `auth_user` DISABLE KEYS */;\n",
      "INSERT INTO `auth_user` VALUES (1,'jhachman','','','jh@chemistry.harvard.edu','pbkdf2_sha256$10000$QqBUpgJE6q53$5QxuAqSQ6Ah2PC5zpuAPLqOwgZeOsWjEO0iAVECugW0=',1,1,1,'2012-10-10 16:36:50','2012-10-10 16:36:50');\n",
      "/*!40000 ALTER TABLE `auth_user` ENABLE KEYS */;\n",
      "UNLOCK TABLES;\n",
      "\n",
      "--\n",
      "-- Table structure for table `auth_user_groups`\n",
      "--\n",
      "\n",
      "DROP TABLE IF EXISTS `auth_user_groups`;\n",
      "SET @saved_cs_client     = @@character_set_client;\n",
      "SET character_set_client = utf8;\n",
      "CREATE TABLE `auth_user_groups` (\n",
      "  `id` int(11) NOT NULL AUTO_INCREMENT,\n",
      "  `user_id` int(11) NOT NULL,\n",
      "  `group_id` int(11) NOT NULL,\n",
      "  PRIMARY KEY (`id`),\n",
      "  UNIQUE KEY `user_id` (`user_id`,`group_id`),\n",
      "  KEY `auth_user_groups_fbfc09f1` (`user_id`),\n",
      "  KEY `auth_user_groups_bda51c3c` (`group_id`),\n",
      "  CONSTRAINT `group_id_refs_id_f0ee9890` FOREIGN KEY (`group_id`) REFERENCES `auth_group` (`id`),\n",
      "  CONSTRAINT `user_id_refs_id_831107f1` FOREIGN KEY (`user_id`) REFERENCES `auth_user` (`id`)\n",
      ") ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;\n",
      "SET character_set_client = @saved_cs_client;\n",
      "\n",
      "--\n",
      "-- Dumping data for table `auth_user_groups`\n",
      "--\n",
      "-- ORDER BY:  `id`\n",
      "\n",
      "LOCK TABLES `auth_user_groups` WRITE;\n",
      "/*!40000 ALTER TABLE `auth_user_groups` DISABLE KEYS */;\n",
      "/*!40000 ALTER TABLE `auth_user_groups` ENABLE KEYS */;\n",
      "UNLOCK TABLES;\n",
      "\n",
      "--\n",
      "-- Table structure for table `auth_user_user_permissions`\n",
      "--\n",
      "\n",
      "DROP TABLE IF EXISTS `auth_user_user_permissions`;\n",
      "SET @saved_cs_client     = @@character_set_client;\n",
      "SET character_set_client = utf8;\n",
      "CREATE TABLE `auth_user_user_permissions` (\n",
      "  `id` int(11) NOT NULL AUTO_INCREMENT,\n",
      "  `user_id` int(11) NOT NULL,\n",
      "  `permission_id` int(11) NOT NULL,\n",
      "  PRIMARY KEY (`id`),\n",
      "  UNIQUE KEY `user_id` (`user_id`,`permission_id`),\n",
      "  KEY `auth_user_user_permissions_fbfc09f1` (`user_id`),\n",
      "  KEY `auth_user_user_permissions_1e014c8f` (`permission_id`),\n",
      "  CONSTRAINT `permission_id_refs_id_67e79cb` FOREIGN KEY (`permission_id`) REFERENCES `auth_permission` (`id`),\n",
      "  CONSTRAINT `user_id_refs_id_f2045483` FOREIGN KEY (`user_id`) REFERENCES `auth_user` (`id`)\n",
      ") ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;\n",
      "SET character_set_client = @saved_cs_client;\n",
      "\n",
      "--\n",
      "-- Dumping data for table `auth_user_user_permissions`\n",
      "--\n",
      "-- ORDER BY:  `id`\n",
      "\n",
      "LOCK TABLES `auth_user_user_permissions` WRITE;\n",
      "/*!40000 ALTER TABLE `auth_user_user_permissions` DISABLE KEYS */;\n",
      "/*!40000 ALTER TABLE `auth_user_user_permissions` ENABLE KEYS */;\n",
      "UNLOCK TABLES;\n",
      "\n",
      "--\n",
      "-- Table structure for table `data_calcqcset1`\n",
      "--\n",
      "\n",
      "DROP TABLE IF EXISTS `data_calcqcset1`;\n",
      "SET @saved_cs_client     = @@character_set_client;\n",
      "SET character_set_client = utf8;\n",
      "CREATE TABLE `data_calcqcset1` (\n",
      "  `id` int(11) NOT NULL AUTO_INCREMENT,\n",
      "  `mol_graph_id` int(11) NOT NULL,\n",
      "  `mol_geom_id` int(11) NOT NULL,\n",
      "  `calc_id_str` varchar(250) COLLATE utf8_bin NOT NULL,\n",
      "  `calc_tbz_str` varchar(250) COLLATE utf8_bin NOT NULL,\n",
      "  `calc_archive_subdir_path` varchar(250) COLLATE utf8_bin NOT NULL,\n",
      "  `modelchem_str` varchar(100) COLLATE utf8_bin NOT NULL,\n",
      "  `e_total` double DEFAULT NULL,\n",
      "  `e_homo_alpha` double DEFAULT NULL,\n",
      "  `e_lumo_alpha` double DEFAULT NULL,\n",
      "  `e_gap_alpha` double DEFAULT NULL,\n",
      "  `e_homo_beta` double DEFAULT NULL,\n",
      "  `e_lumo_beta` double DEFAULT NULL,\n",
      "  `e_gap_beta` double DEFAULT NULL,\n",
      "  `e_gap_min` double DEFAULT NULL,\n",
      "  `dipmom_total` double DEFAULT NULL,\n",
      "  `s2_val` double DEFAULT NULL,\n",
      "  PRIMARY KEY (`id`),\n",
      "  UNIQUE KEY `calc_id_str` (`calc_id_str`),\n",
      "  KEY `data_calcqcset1_88fb5108` (`mol_graph_id`),\n",
      "  KEY `data_calcqcset1_2174315b` (`mol_geom_id`),\n",
      "  CONSTRAINT `mol_geom_id_refs_id_9f7beeea` FOREIGN KEY (`mol_geom_id`) REFERENCES `data_molgeom` (`id`),\n",
      "  CONSTRAINT `mol_graph_id_refs_id_9b795eb5` FOREIGN KEY (`mol_graph_id`) REFERENCES `data_molgraph` (`id`)\n",
      ") ENGINE=InnoDB AUTO_INCREMENT=147902359 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;\n",
      "SET character_set_client = @saved_cs_client;\n",
      "\n",
      "--\n",
      "-- Dumping data for table `data_calcqcset1`\n",
      "--\n",
      "-- ORDER BY:  `id`\n",
      "\n",
      "LOCK TABLES `data_calcqcset1` WRITE;\n",
      "/*!40000 ALTER TABLE `data_calcqcset1` DISABLE KEYS */;\n",
      "INSERT INTO `data_calcqcset1` VALUES (1,386226,16855,'A.16.C13H8N2S.49.noopt.bp86.sto6g.n.sp','','','MM//BP86/STO-6G',-1004.9549135703,-0.101,0.019,0.12,-0.101,0.019,0.12,0.12,3.9718,NULL),(2,386226,16855,'A.16.C13H8N2S.49.noopt.bp86.svp.n.sp','','','MM//BP86/SVP',-1007.3925734934,-0.189,-0.073,0.116,-0.189,-0.073,0.116,0.116,2.9927,NULL),(3,386226,15011810,'A.16.C13H8N2S.49.bp86.svp.n.bp86.svp.n.sp','','','BP86/SVP//BP86/SVP',-1007.4106491085,-0.189,-0.076,0.113,-0.189,-0.076,0.113,0.113,3.3101,NULL),(4,386226,15011810,'A.16.C13H8N2S.49.bp86.svp.n.b3lyp.svp.n.sp','','','BP86/SVP//B3LYP/SVP',-1007.3715017888,-0.21,-0.049,0.161,-0.21,-0.049,0.161,0.161,3.2717,NULL),(5,386226,15011810,'A.16.C13H8N2S.49.bp86.svp.n.pbe0.svp.n.sp','','','BP86/SVP//PBE0/SVP',-1006.5262975477,-0.219,-0.047,0.172,-0.219,-0.047,0.172,0.172,3.2038,NULL),(6,386226,15011810,'A.16.C13H8N2S.49.bp86.svp.n.bhandhlyp.svp.n.sp','','','BP86/SVP//BH&HLYP/SVP',-1006.9750584092,-0.243,-0.011,0.232,-0.243,-0.011,0.232,0.232,3.1471,NULL),(7,386226,15011810,'A.16.C13H8N2S.49.bp86.svp.n.m062x.svp.n.sp','','','BP86/SVP//M06-2X/SVP',-1007.0912602232,-0.254,-0.019,0.235,-0.254,-0.019,0.235,0.235,3.1508,NULL),(8,38622\n"
     ]
    }
   ],
   "source": [
    "with open(db_path, 'r') as file:\n",
    "    print(file.read(12000))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "83ec5504",
   "metadata": {},
   "outputs": [
    {
     "ename": "KeyboardInterrupt",
     "evalue": "",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mKeyboardInterrupt\u001b[0m                         Traceback (most recent call last)",
      "Cell \u001b[0;32mIn[16], line 4\u001b[0m\n\u001b[1;32m      1\u001b[0m insert_stmt \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;01mNone\u001b[39;00m\n\u001b[1;32m      3\u001b[0m \u001b[38;5;28;01mwith\u001b[39;00m \u001b[38;5;28mopen\u001b[39m(db_path, \u001b[38;5;124m'\u001b[39m\u001b[38;5;124mr\u001b[39m\u001b[38;5;124m'\u001b[39m) \u001b[38;5;28;01mas\u001b[39;00m file:\n\u001b[0;32m----> 4\u001b[0m     calc_line \u001b[38;5;241m=\u001b[39m \u001b[43mfile\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mreadlines\u001b[49m\u001b[43m(\u001b[49m\u001b[43m)\u001b[49m[\u001b[38;5;241m102\u001b[39m]\n",
      "File \u001b[0;32m<frozen codecs>:319\u001b[0m, in \u001b[0;36mdecode\u001b[0;34m(self, input, final)\u001b[0m\n",
      "\u001b[0;31mKeyboardInterrupt\u001b[0m: "
     ]
    }
   ],
   "source": [
    "insert_stmt = None\n",
    "\n",
    "with open(db_path, 'r') as file:\n",
    "    calc_line = file.readlines()[102]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "2105bcae",
   "metadata": {},
   "outputs": [],
   "source": [
    "insert_stmt = linecache.getline(db_path, 242)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "77e847aa",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "INSERT INTO `data_calcqcset1` VALUES (1,386226,16855,'A.16.C13H8N2S.49.noopt.bp86.sto6g.n.sp','','',\n"
     ]
    }
   ],
   "source": [
    "print(insert_stmt[:100])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "e31ffe3d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "        id mol_graph_id mol_geom_id   \n",
      "0        1       386226       16855  \\\n",
      "1        2       386226       16855   \n",
      "2        3       386226    15011810   \n",
      "3        4       386226    15011810   \n",
      "4        5       386226    15011810   \n",
      "...    ...          ...         ...   \n",
      "6095  6096       225056    15012921   \n",
      "6096  6097       225056    15012921   \n",
      "6097  6098       225056    15012921   \n",
      "6098  6099       225056    15012921   \n",
      "6099  6100       225056    15012921   \n",
      "\n",
      "                                            calc_id_str calc_tbz_str   \n",
      "0              'A.16.C13H8N2S.49.noopt.bp86.sto6g.n.sp'           ''  \\\n",
      "1                'A.16.C13H8N2S.49.noopt.bp86.svp.n.sp'           ''   \n",
      "2           'A.16.C13H8N2S.49.bp86.svp.n.bp86.svp.n.sp'           ''   \n",
      "3          'A.16.C13H8N2S.49.bp86.svp.n.b3lyp.svp.n.sp'           ''   \n",
      "4           'A.16.C13H8N2S.49.bp86.svp.n.pbe0.svp.n.sp'           ''   \n",
      "...                                                 ...          ...   \n",
      "6095  'A.18.C15H10S2Si.62.bp86.svp.n.bhandhlyp.svp.n...           ''   \n",
      "6096     'A.18.C15H10S2Si.62.bp86.svp.n.m062x.svp.n.sp'           ''   \n",
      "6097        'A.18.C15H10S2Si.62.bp86.svp.n.hf.svp.n.sp'           ''   \n",
      "6098       'A.18.C15H10S2Si.62.bp86.svp.n.uhf.svp.n.sp'           ''   \n",
      "6099    'A.18.C15H10S2Si.62.bp86.svp.n.um062x.svp.n.sp'           ''   \n",
      "\n",
      "     calc_archive_subdir_path            modelchem_str           e_total   \n",
      "0                          ''        'MM//BP86/STO-6G'  -1004.9549135703  \\\n",
      "1                          ''           'MM//BP86/SVP'  -1007.3925734934   \n",
      "2                          ''     'BP86/SVP//BP86/SVP'  -1007.4106491085   \n",
      "3                          ''    'BP86/SVP//B3LYP/SVP'  -1007.3715017888   \n",
      "4                          ''     'BP86/SVP//PBE0/SVP'  -1006.5262975477   \n",
      "...                       ...                      ...               ...   \n",
      "6095                       ''  'BP86/SVP//BH&HLYP/SVP'   -1662.327731068   \n",
      "6096                       ''   'BP86/SVP//M06-2X/SVP'   -1662.414366167   \n",
      "6097                       ''       'BP86/SVP//HF/SVP'  -1657.0049612131   \n",
      "6098                       ''      'BP86/SVP//UHF/SVP'  -1657.0078951857   \n",
      "6099                       ''  'BP86/SVP//UM06-2X/SVP'  -1662.4139679565   \n",
      "\n",
      "     e_homo_alpha e_lumo_alpha e_gap_alpha e_homo_beta e_lumo_beta e_gap_beta   \n",
      "0          -0.101        0.019        0.12      -0.101       0.019       0.12  \\\n",
      "1          -0.189       -0.073       0.116      -0.189      -0.073      0.116   \n",
      "2          -0.189       -0.076       0.113      -0.189      -0.076      0.113   \n",
      "3           -0.21       -0.049       0.161       -0.21      -0.049      0.161   \n",
      "4          -0.219       -0.047       0.172      -0.219      -0.047      0.172   \n",
      "...           ...          ...         ...         ...         ...        ...   \n",
      "6095       -0.215       -0.045        0.17      -0.215      -0.045       0.17   \n",
      "6096       -0.228       -0.053       0.175      -0.228      -0.053      0.175   \n",
      "6097       -0.244        0.034       0.278      -0.244       0.034      0.278   \n",
      "6098       -0.249        0.035       0.284      -0.246       0.038      0.284   \n",
      "6099       -0.228       -0.053       0.175      -0.228      -0.052      0.176   \n",
      "\n",
      "     e_gap_min dipmom_total s2_val  \n",
      "0         0.12       3.9718   NULL  \n",
      "1        0.116       2.9927   NULL  \n",
      "2        0.113       3.3101   NULL  \n",
      "3        0.161       3.2717   NULL  \n",
      "4        0.172       3.2038   NULL  \n",
      "...        ...          ...    ...  \n",
      "6095      0.17       1.5734   NULL  \n",
      "6096     0.175       1.5726   NULL  \n",
      "6097     0.278       1.5114   NULL  \n",
      "6098     0.281        1.507   NULL  \n",
      "6099     0.175       1.5718   NULL  \n",
      "\n",
      "[6100 rows x 17 columns]\n"
     ]
    }
   ],
   "source": [
    "# Extract data values using regex\n",
    "values_str = re.findall(r\"\\((.*?)\\)\", insert_stmt)\n",
    "values = [v.split(\",\") for v in values_str]\n",
    "\n",
    "# Create DataFrame\n",
    "df = pd.DataFrame(values, columns=['id', 'mol_graph_id', 'mol_geom_id', 'calc_id_str', 'calc_tbz_str', 'calc_archive_subdir_path', 'modelchem_str', 'e_total', 'e_homo_alpha', 'e_lumo_alpha', 'e_gap_alpha', 'e_homo_beta', 'e_lumo_beta', 'e_gap_beta', 'e_gap_min', 'dipmom_total', 's2_val'])\n",
    "\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "e0d78a3c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "id                                                                 1\n",
       "mol_graph_id                                                  386226\n",
       "mol_geom_id                                                    16855\n",
       "calc_id_str                 'A.16.C13H8N2S.49.noopt.bp86.sto6g.n.sp'\n",
       "calc_tbz_str                                                      ''\n",
       "calc_archive_subdir_path                                          ''\n",
       "modelchem_str                                      'MM//BP86/STO-6G'\n",
       "e_total                                             -1004.9549135703\n",
       "e_homo_alpha                                                  -0.101\n",
       "e_lumo_alpha                                                   0.019\n",
       "e_gap_alpha                                                     0.12\n",
       "e_homo_beta                                                   -0.101\n",
       "e_lumo_beta                                                    0.019\n",
       "e_gap_beta                                                      0.12\n",
       "e_gap_min                                                       0.12\n",
       "dipmom_total                                                  3.9718\n",
       "s2_val                                                          NULL\n",
       "Name: 0, dtype: object"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.iloc[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "d0afa404",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "INSERT INTO `data_calcqcset1` VALUES (50873296,1015212,18844432,'C.29.C25H14N2SSe.00203831.1.bp86.sv\n"
     ]
    }
   ],
   "source": [
    "line = linecache.getline(db_path, 9045)\n",
    "print(line[:100])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c3cc28d2",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}