sqlite3.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "efb0772c-ec2c-4f53-9a26-b471301baf82",
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "import datetime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6313a5ae-703c-4b4b-985b-f95b6644d3ff",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 连接数据库(如果不存在则创建)\n",
    "conn = sqlite3.connect(r\".\\testfile\\db\\sql3.db\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9142dab4-8d45-4ae9-94bb-e8279e6daf5a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 创建游标:\n",
    "cursor = conn.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5b5b1f9c-8833-4e58-859c-9b28ee3d82b7",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 关闭游标:\n",
    "cursor.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bb6c97ff-9858-4417-8b15-5dff76a8c5f6",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 提交事务:\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "45345ed8-e41c-4cc0-9343-b7770dc3f797",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 关闭连接:\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b64c4c59-c5ab-4a36-820f-01b266d742b7",
   "metadata": {},
   "source": [
    "# 创建数据表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7da562ba-c45b-4a75-9c70-bcccfc0dc3e2",
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = sqlite3.connect(r\".\\SQL\\testdata\\sql3.db\")\n",
    "c = conn.cursor()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0433e55b-c6df-4a2d-9ae9-cbc5809dea0c",
   "metadata": {},
   "outputs": [],
   "source": [
    "c.execute('''create table company\n",
    "(ID INTEGER PRIMARY KEY,\n",
    "QRCode VARCHAR(60) NOT NULL,\n",
    "AGE INT NOT NULL,\n",
    "ADDRESS CHAR(50),\n",
    "SALARY REAL);\n",
    "''')\n",
    "print(\"Done\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f1eb5111-f748-4a91-8b23-ba9ac01d8bd1",
   "metadata": {},
   "outputs": [],
   "source": [
    "c.execute('''INSERT INTO company \n",
    "VALUES (null,'osun',39,'pudong',20)\n",
    "''')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c985c4a1-1ab9-496a-a3f8-0ed087a73e36",
   "metadata": {},
   "outputs": [],
   "source": [
    "c.execute(\"drop table company\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8c505020-a9ae-40b9-a1f4-6d3c13cde833",
   "metadata": {},
   "outputs": [],
   "source": [
    "# c.execute(\"show table\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "86495bff-5ef4-4ff6-ae1c-d886869fb7e1",
   "metadata": {},
   "outputs": [],
   "source": [
    "c.close()\n",
    "conn.commit()\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0231491f-6052-4d8e-8a23-e8956f7e1ee2",
   "metadata": {},
   "source": [
    "# Practice:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "769ded4d-f2ce-4470-bb2b-e56fe339f91b",
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = sqlite3.connect(r\".\\SQL\\testdata\\SENXXSGPSHT.db\")\n",
    "c = conn.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "0ceac1ab-4baa-43ec-a034-8a2a8bfc195e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# conn = sqlite3.connect(r\"\\\\10.31.100.40\\bugreport\\ReworkData\\SENXXSGPSHT.db\")\n",
    "# c = conn.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "7e122673-88a4-4673-8fe4-6f7b6722af10",
   "metadata": {},
   "outputs": [],
   "source": [
    "# c.execute(\"drop table SEN4XOct\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "a56923de-b6e7-4452-a221-e25862b15eaa",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done\n"
     ]
    }
   ],
   "source": [
    "c.execute('''create table SEN5XOct\n",
    "(ID AUTO_INCREMENT,\n",
    "DateTime datetime,\n",
    "QRCode VARCHAR(60) NOT NULL,\n",
    "DeviceId VARCHAR(40) NOT NULL,\n",
    "FailPast VARCHAR(20));\n",
    "''')\n",
    "print(\"Done\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "0a86a0af-2459-4361-b337-da9419fe0553",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done\n"
     ]
    }
   ],
   "source": [
    "# c.execute('''create table SEN4XOct\n",
    "# (ID INTEGER PRIMARY KEY,\n",
    "# DateTime datetime,\n",
    "# QRCode TEXT NOT NULL,\n",
    "# SGP VARCHAR(20) NOT NULL,\n",
    "# SHT VARCHAR(20) NOT NULL);\n",
    "# ''')\n",
    "# print(\"Done\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "81f6627f-2a6e-411e-bdee-cdffef7d93a0",
   "metadata": {},
   "outputs": [],
   "source": [
    "tm = datetime.datetime.now().strftime(\"%Y-%m-%d %H:%M:%S\")\n",
    "QRCode = \"403761010722J11198908230072999963S\"\n",
    "SGPResult = \"Good\"\n",
    "SHTResult = \"NG\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "db2d0ddf-cd14-4a5c-be72-fbf3defdd898",
   "metadata": {},
   "outputs": [],
   "source": [
    "# c.execute(\"INSERT INTO SEN4XOct VALUES (0,'2022-10-03','5044933h44354','Good','NG')\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "2375239d-05a6-4712-9092-140a2c5f7ec0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x1efd09a1730>"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c.execute(\"INSERT INTO SEN4XOct VALUES (0,'{}','{}','{}','{}')\".format(tm,QRCode,SGPResult,SHTResult))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "f2029f23-d5d8-46a7-aa96-f164a2175431",
   "metadata": {},
   "outputs": [],
   "source": [
    "c.close()\n",
    "conn.commit()\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "0066c6e3-29eb-4cc0-9744-688f5cde0834",
   "metadata": {},
   "outputs": [],
   "source": [
    "# \"'{}'\".format(SGPResult)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "a2281ee5-6d0f-4e08-b9f2-6f4fae30d369",
   "metadata": {},
   "outputs": [],
   "source": [
    "import winsound"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5416ac8a-c771-426b-a931-2e8777ce7b0d",
   "metadata": {},
   "outputs": [],
   "source": [
    "winsound.Beep()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 38",
   "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.8.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}