Write a python code
haripriyastudyx
assignment5priya.ipynb{ "cells": [ { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [], "source": [ "import mysql\n", "import mysql.connector" ] }, { "cell_type": "code", "execution_count": 3, "metadata": {}, "outputs": [], "source": [ "import csv" ] }, { "cell_type": "code", "execution_count": 4, "metadata": {}, "outputs": [], "source": [ "database = mysql.connector.connect(host=\"localhost\",user=\"root\",password=\"priya143\", database=\"class\")\n", "cursor = database.cursor()" ] }, { "cell_type": "code", "execution_count": 5, "metadata": {}, "outputs": [ { "ename": "ProgrammingError", "evalue": "1050 (42S01): Table 'firmsreports' already exists", "output_type": "error", "traceback": [ "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m", "\u001b[0;31mMySQLInterfaceError\u001b[0m Traceback (most recent call last)", "\u001b[0;32m~/.local/lib/python3.7/site-packages/mysql/connector/connection_cext.py\u001b[0m in \u001b[0;36mcmd_query\u001b[0;34m(self, query, raw, buffered, raw_as_string)\u001b[0m\n\u001b[1;32m 488\u001b[0m \u001b[0mraw\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mraw\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mbuffered\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mbuffered\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 489\u001b[0;31m raw_as_string=raw_as_string)\n\u001b[0m\u001b[1;32m 490\u001b[0m \u001b[0;32mexcept\u001b[0m \u001b[0mMySQLInterfaceError\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0mexc\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n", "\u001b[0;31mMySQLInterfaceError\u001b[0m: Table 'firmsreports' already exists", "\nDuring handling of the above exception, another exception occurred:\n", "\u001b[0;31mProgrammingError\u001b[0m Traceback (most recent call last)", "\u001b[0;32m<ipython-input-5-13d5adca586c>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m 1\u001b[0m \u001b[0mquery\u001b[0m \u001b[0;34m=\u001b[0m\u001b[0;34m\"CREATE TABLE class.firmsreports (id INT NOT NULL AUTO_INCREMENT,cik INT NULL,companyname VARCHAR(500) NULL,formtype VARCHAR(45) NULL,datafile DATE NULL,filename VARCHAR(500) NULL,PRIMARY KEY (id));\"\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 2\u001b[0;31m \u001b[0mcursor\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m \u001b[0;34m(\u001b[0m\u001b[0mquery\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m", "\u001b[0;32m~/.local/lib/python3.7/site-packages/mysql/connector/cursor_cext.py\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, operation, params, multi)\u001b[0m\n\u001b[1;32m 264\u001b[0m result = self._cnx.cmd_query(stmt, raw=self._raw,\n\u001b[1;32m 265\u001b[0m \u001b[0mbuffered\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_buffered\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 266\u001b[0;31m raw_as_string=self._raw_as_string)\n\u001b[0m\u001b[1;32m 267\u001b[0m \u001b[0;32mexcept\u001b[0m \u001b[0mMySQLInterfaceError\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0mexc\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 268\u001b[0m raise errors.get_mysql_exception(msg=exc.msg, errno=exc.errno,\n", "\u001b[0;32m~/.local/lib/python3.7/site-packages/mysql/connector/connection_cext.py\u001b[0m in \u001b[0;36mcmd_query\u001b[0;34m(self, query, raw, buffered, raw_as_string)\u001b[0m\n\u001b[1;32m 490\u001b[0m \u001b[0;32mexcept\u001b[0m \u001b[0mMySQLInterfaceError\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0mexc\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 491\u001b[0m raise errors.get_mysql_exception(exc.errno, msg=exc.msg,\n\u001b[0;32m--> 492\u001b[0;31m sqlstate=exc.sqlstate)\n\u001b[0m\u001b[1;32m 493\u001b[0m \u001b[0;32mexcept\u001b[0m \u001b[0mAttributeError\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 494\u001b[0m \u001b[0;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_unix_socket\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n", "\u001b[0;31mProgrammingError\u001b[0m: 1050 (42S01): Table 'firmsreports' already exists" ] } ], "source": [ "query =\"CREATE TABLE class.firmsreports (id INT NOT NULL AUTO_INCREMENT,cik INT NULL,companyname VARCHAR(500) NULL,formtype VARCHAR(45) NULL,datafile DATE NULL,filename VARCHAR(500) NULL,PRIMARY KEY (id));\"\n", "cursor.execute (query)" ] }, { "cell_type": "code", "execution_count": 6, "metadata": {}, "outputs": [], "source": [ "idxfile = \"Mod05_master-2018Q1-v2.idx\"" ] }, { "cell_type": "code", "execution_count": 16, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Description: Master Index of EDGAR Dissemination Feed\n", "\n", "Last Data Received: March 31, 2018\n", "\n", "Comments: [email protected]\n", "\n", "Anonymous FTP: ftp://ftp.sec.gov/edgar/\n", "\n", "Cloud HTTP: https://www.sec.gov/Archives/\n", "\n", "\n", "\n", " \n", "\n", " \n", "\n", " \n", "\n", "CIK|Company Name|Form Type|Date Filed|Filename\n", "\n", "--------------------------------------------------------------------------------\n", "\n", "1000032|BINCH JAMES G|4|2018-02-16|edgar/data/1000032/0000913165-18-000034.txt\n", "\n", "1000045|NICHOLAS FINANCIAL INC|10-Q|2018-02-09|edgar/data/1000045/0001193125-18-037381.txt\n", "\n", "1000045|NICHOLAS FINANCIAL INC|4|2018-02-15|edgar/data/1000045/0001000045-18-000004.txt\n", "\n", "1000045|NICHOLAS FINANCIAL INC|4|2018-03-08|edgar/data/1000045/0001000045-18-000005.txt\n", "\n", "1000045|NICHOLAS FINANCIAL INC|4|2018-03-20|edgar/data/1000045/0001609591-18-000001.txt\n", "\n", "1000045|NICHOLAS FINANCIAL INC|8-K|2018-01-09|edgar/data/1000045/0001193125-18-007253.txt\n", "\n", "1000045|NICHOLAS FINANCIAL INC|8-K|2018-02-05|edgar/data/1000045/0001193125-18-032199.txt\n", "\n", "1000045|NICHOLAS FINANCIAL INC|8-K|2018-02-07|edgar/data/1000045/0001193125-18-034693.txt\n", "\n", "1000045|NICHOLAS FINANCIAL INC|8-K|2018-02-20|edgar/data/1000045/0001193125-18-049706.txt\n", "\n", "1000045|NICHOLAS FINANCIAL INC|SC 13G/A|2018-02-12|edgar/data/1000045/0001104659-18-008485.txt\n", "\n", "1000045|NICHOLAS FINANCIAL INC|SC 13G/A|2018-02-14|edgar/data/1000045/0001037389-18-000160.txt\n", "\n", "1000045|NICHOLAS FINANCIAL INC|SC 13G|2018-02-09|edgar/data/1000045/0001258897-18-001316.txt\n", "\n", "1000045|NICHOLAS FINANCIAL INC|SC 13G|2018-02-13|edgar/data/1000045/0000315066-18-001444.txt\n", "\n", "1000097|KINGDON CAPITAL MANAGEMENT, L.L.C.|13F-HR|2018-02-14|edgar/data/1000097/0000919574-18-001804.txt\n", "\n", "1000097|KINGDON CAPITAL MANAGEMENT, L.L.C.|SC 13G/A|2018-01-02|edgar/data/1000097/0000919574-18-000008.txt\n", "\n", "1000097|KINGDON CAPITAL MANAGEMENT, L.L.C.|SC 13G/A|2018-02-14|edgar/data/1000097/0000919574-18-001760.txt\n", "\n", "1000097|KINGDON CAPITAL MANAGEMENT, L.L.C.|SC 13G/A|2018-02-14|edgar/data/1000097/0000919574-18-001765.txt\n", "\n", "1000097|KINGDON CAPITAL MANAGEMENT, L.L.C.|SC 13G/A|2018-02-14|edgar/data/1000097/0000919574-18-001773.txt\n", "\n", "1000097|KINGDON CAPITAL MANAGEMENT, L.L.C.|SC 13G/A|2018-02-14|edgar/data/1000097/0000919574-18-001777.txt\n", "\n", "1000097|KINGDON CAPITAL MANAGEMENT, L.L.C.|SC 13G/A|2018-02-14|edgar/data/1000097/0000919574-18-001785.txt\n", "\n", "1000097|KINGDON CAPITAL MANAGEMENT, L.L.C.|SC 13G|2018-02-14|edgar/data/1000097/0000919574-18-001790.txt\n", "\n", "1000177|NORDIC AMERICAN TANKERS Ltd|6-K|2018-02-28|edgar/data/1000177/0000919574-18-002148.txt\n", "\n", "1000177|NORDIC AMERICAN TANKERS Ltd|CORRESP|2018-01-19|edgar/data/1000177/0000919574-18-000510.txt\n", "\n", "1000177|NORDIC AMERICAN TANKERS Ltd|CORRESP|2018-02-08|edgar/data/1000177/0000919574-18-000929.txt\n", "\n", "1000177|NORDIC AMERICAN TANKERS Ltd|CORRESP|2018-02-22|edgar/data/1000177/0000919574-18-001989.txt\n", "\n", "1000177|NORDIC AMERICAN TANKERS Ltd|CORRESP|2018-03-02|edgar/data/1000177/0000919574-18-002224.txt\n", "\n", "1000177|NORDIC AMERICAN TANKERS Ltd|SC 13G|2018-01-12|edgar/data/1000177/0000895421-18-000006.txt\n", "\n", "1000177|NORDIC AMERICAN TANKERS Ltd|UPLOAD|2018-02-05|edgar/data/1000177/0000000000-18-004057.txt\n", "\n", "1000177|NORDIC AMERICAN TANKERS Ltd|UPLOAD|2018-03-19|edgar/data/1000177/0000000000-18-008366.txt\n", "\n", "1000184|SAP SE|20-F|2018-02-28|edgar/data/1000184/0001104659-18-013050.txt\n", "\n", "1000184|SAP SE|6-K|2018-01-30|edgar/data/1000184/0001104659-18-005109.txt\n", "\n", "1000184|SAP SE|6-K|2018-01-31|edgar/data/1000184/0001104659-18-005283.txt\n", "\n", "1000184|SAP SE|6-K|2018-02-22|edgar/data/1000184/0001104659-18-011197.txt\n", "\n", "1000184|SAP SE|6-K|2018-03-01|edgar/data/1000184/0001104659-18-013742.txt\n", "\n", "1000184|SAP SE|6-K|2018-03-06|edgar/data/1000184/0001104659-18-015111.txt\n", "\n", "1000184|SAP SE|DFAN14A|2018-01-30|edgar/data/1000184/0001104659-18-005123.txt\n", "\n", "1000184|SAP SE|DFAN14A|2018-01-30|edgar/data/1000184/0001104659-18-005124.txt\n", "\n", "1000184|SAP SE|DFAN14A|2018-01-30|edgar/data/1000184/0001104659-18-005125.txt\n", "\n", "1000184|SAP SE|DFAN14A|2018-01-30|edgar/data/1000184/0001104659-18-005126.txt\n", "Mod05_master-2018Q1-v2.idx\n" ] } ], "source": [ "with open(idxfile,'r') as mastertext:\n", " content=mastertext.readlines()\n", "\n", " for row in content:\n", " print (row)\n", " if len(row)!= 0:\n", " row = row.strip('\\n')\n", " if str(row).endswith(\".txt\"):\n", " columns = row.split(\"|\")\n", " #print(columns)\n", " cik = columns[0]\n", " companyname = columns[1]\n", " formtype = columns[2]\n", " datefield = columns[3]\n", " filenames = columns[4]\n", " print(idxfile)\n", " \n", " \n", " " ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ " \n", " query= \"INSERT class.grades (cik, companyname, formtype, datefield, filenames) VALUES ('%(1)s', '%(2)s', %(3)s, '%(4)s', '%(4)s')\" %{\"1\": companyname1, \"2\": formtype1, \"3\": datefield1, \"4\":filenames1 }\n", " cursor.execute(query)\n", " database.commit()" ] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "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.7.4" } }, "nbformat": 4, "nbformat_minor": 2 }
