{"id":63,"date":"2022-03-07T17:46:38","date_gmt":"2022-03-07T17:46:38","guid":{"rendered":"https:\/\/pratimeshtiwari.com\/blogs\/?p=63"},"modified":"2022-07-07T16:21:25","modified_gmt":"2022-07-07T15:21:25","slug":"store-hangman-user-records-in-a-mysql-database-from-user-record-text-file","status":"publish","type":"post","link":"https:\/\/pratimeshtiwari.com\/blogs\/?p=63","title":{"rendered":"Store Hangman User Records in a MYSQL Database from user-record text file"},"content":{"rendered":"\n

Storing Data from Hangman in a MYSQL database is usually done by converting the text file(where the user records are stored into csv using pandas module and then using the csv file to write data into MYSQL server .<\/p>\n\n\n\n

To learn how to create Hangman and save user records in a text file ! Click Here<\/a><\/p>\n\n\n\n

<\/p>\n\n\n\n

Modules Used :<\/strong><\/h1>\n\n\n\n
  • csv<\/strong> : pre-installed<\/li>
  • pandas<\/strong> : pip install pandas<\/li>
  • mysql-connector<\/strong> : pip install mysql-connector-python<\/li><\/ul>\n\n\n\n

    <\/p>\n\n\n\n

    Additional Information about Modules :<\/strong><\/h1>\n\n\n\n

    The csv module implements classes to read and write tabular data in CSV format. It allows programmers to say, \u201cwrite this data in the format preferred by Excel,\u201d or \u201cread data from this file which was generated by Excel,\u201d without knowing the precise details of the CSV format used by Excel.<\/p>\n\n\n\n

    Pandas is an open source library in Python. It provides ready to use high-performance data structures and data analysis tools. Pandas module runs on top of NumPy and it is popularly used for data science and data analytics.<\/p>\n\n\n\n

    MySQL Connector\/Python enables Python programs to access MySQL databases, using an API that is compliant with the Python Database API Specification v2.0 (PEP 249). It is written in pure Python and does not have any dependencies except for the Python Standard Library.<\/p>\n\n\n\n

    <\/p>\n\n\n\n

    Source Code : <\/h1>\n\n\n\n

    Creating a csv file using the data of record.txt to write data to MYSQL database :<\/h2>\n\n\n\n

    Data.py :<\/strong><\/h3>\n\n\n\n
    import csv\nimport pandas as pd\n\nf=pd.read_csv('record.txt')\nf.columns = ['Name','Status']\nf.to_csv('RECORDCSV.csv')\n\nimport mysql.connector\n\ndb = mysql.connector.connect(host=\"localhost\", user=\"root\", passwd=\"root\",db=\"#\")\ncursor = db.cursor()\n\nf=open('RECORDCSV.csv','r')\nreader=csv.reader(f)\nfor row in reader:\n    cursor.execute(\"INSERT INTO RECORD(SNO,USER,STATUS) VALUE(%s,%s,%s)\",row)\n\ndb.commit()\ncursor.close()\nprint(\"Written successfully ! \")\n\n<\/pre>\n\n\n\n
    Note :<\/strong> Running this more than once would cause errors as the data is already written once after executing this code and would try to override in MYSQL database <\/p>\n\n\n\n
    <\/p>\n\n\n\n
    Creating a csv file using the data of record.txt to write data to MYSQL database :<\/h2>\n\n\n\n
    ViewMYSQL.py :<\/h3>\n\n\n\n
    \n
    \n
    import mysql.connector<\/code><\/pre>\n\n\n\n
    try:
    connection = mysql.connector.connect(host='localhost',
    database='#',
    user='root',
    password='root')<\/code><\/pre>\n\n\n\n
    sql_select_Query = \"select distinct * from Record\"\ncursor = connection.cursor()\ncursor.execute(sql_select_Query)\n\nrecords = cursor.fetchall()\nprint(\"Total number of rows in table: \", cursor.rowcount)\n\nprint(\"\\nPrinting each row\")\nfor row in records:\n    print(\"SNO = \", row[0], )\n    print(\"USER = \", row[1])\n    print(\"STATUS  = \", row[2])\n    print()<\/code><\/pre>\n\n\n\n
    except mysql.connector.Error as e:
    print(\"Error reading data from MySQL table\", e)
    finally:
    if connection.is_connected():
    connection.close()
    cursor.close()
    print(\"MySQL connection is closed\")<\/code><\/pre>\n<\/div><\/div>\n<\/div><\/div>\n","protected":false},"excerpt":{"rendered":"
    Storing Data from Hangman in a MYSQL database is usually done by […]<\/p>\n","protected":false},"author":1,"featured_media":147,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6],"tags":[5,8,13,7],"class_list":["post-63","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-python","tag-coding","tag-hangman","tag-mysql","tag-python"],"_links":{"self":[{"href":"https:\/\/pratimeshtiwari.com\/blogs\/index.php?rest_route=\/wp\/v2\/posts\/63","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/pratimeshtiwari.com\/blogs\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/pratimeshtiwari.com\/blogs\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/pratimeshtiwari.com\/blogs\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/pratimeshtiwari.com\/blogs\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=63"}],"version-history":[{"count":14,"href":"https:\/\/pratimeshtiwari.com\/blogs\/index.php?rest_route=\/wp\/v2\/posts\/63\/revisions"}],"predecessor-version":[{"id":178,"href":"https:\/\/pratimeshtiwari.com\/blogs\/index.php?rest_route=\/wp\/v2\/posts\/63\/revisions\/178"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/pratimeshtiwari.com\/blogs\/index.php?rest_route=\/wp\/v2\/media\/147"}],"wp:attachment":[{"href":"https:\/\/pratimeshtiwari.com\/blogs\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=63"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/pratimeshtiwari.com\/blogs\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=63"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/pratimeshtiwari.com\/blogs\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=63"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}