0

I am trying to create a locally DataBase using SQLAlchemy and MySQL Connector/Python .... As I have a huge data I want to work with....

And here's my Connection looks like....

# connect db
engine = sa.create_engine('mysql+mysqlconnector://' + MYSQL_USER + ':' + MYSQL_PASSWORD + '@' + MYSQL_HOST_IP + ':' + str(
    MYSQL_PORT) + '/' + MYSQL_DATABASE, echo=False)
engine.connect()

as I am using phpMyAdmin with xampp So I tried to import my data in to the Data-Base but it failed because the Size of the data I push is too large...

So I am glad for that if any one could send me a docs or tutorial that Explain how to create this connection in brief...

I am sorry if there's any thing is not clear enough..

Note that I am using pandas + to_sql to push data from excel to database, as I have more than one table as every table have more that 5000 rows and more than 100 columns.....

This error I found looks like

Traceback (most recent call last):
  File "C:\Users\DELL\PycharmProjects\MyALLRefProf\venv\lib\site-packages\mysql\connector\network.py", line 241, in recv_plain
    chunk = self.sock.recv(4 - packet_len)
ConnectionAbortedError: [WinError 10053] An established connection was aborted by the software in your host machine

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "C:\Users\DELL\PycharmProjects\MyALLRefProf\venv\lib\site-packages\sqlalchemy\engine\base.py", line 1170, in _execute_context
    context)
  File "C:\Users\DELL\PycharmProjects\MyALLRefProf\venv\lib\site-packages\sqlalchemy\engine\default.py", line 504, in do_executemany
    cursor.executemany(statement, parameters)
  File "C:\Users\DELL\PycharmProjects\MyALLRefProf\venv\lib\site-packages\mysql\connector\cursor.py", line 654, in executemany
    return self.execute(stmt)
  File "C:\Users\DELL\PycharmProjects\MyALLRefProf\venv\lib\site-packages\mysql\connector\cursor.py", line 551, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File "C:\Users\DELL\PycharmProjects\MyALLRefProf\venv\lib\site-packages\mysql\connector\connection.py", line 490, in cmd_query
    result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
  File "C:\Users\DELL\PycharmProjects\MyALLRefProf\venv\lib\site-packages\mysql\connector\connection.py", line 267, in _send_cmd
    return self._socket.recv()
  File "C:\Users\DELL\PycharmProjects\MyALLRefProf\venv\lib\site-packages\mysql\connector\network.py", line 270, in recv_plain
    errno=2055, values=(self.get_address(), _strioerror(err)))
mysql.connector.errors.OperationalError: 2055: Lost connection to MySQL server at '127.0.0.1:3306', system error: 10053 An established connection was aborted by the software in your host machine

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "C:/Users/DELL/PycharmProjects/automateDB/swap.py", line 42, in <module>
    lte_details.to_sql(file_basename.lower(), con=engine, if_exists='replace', index = False)
  File "C:\Users\DELL\PycharmProjects\MyALLRefProf\venv\lib\site-packages\pandas\core\generic.py", line 2532, in to_sql
    dtype=dtype, method=method)
  File "C:\Users\DELL\PycharmProjects\MyALLRefProf\venv\lib\site-packages\pandas\io\sql.py", line 460, in to_sql
    chunksize=chunksize, dtype=dtype, method=method)
  File "C:\Users\DELL\PycharmProjects\MyALLRefProf\venv\lib\site-packages\pandas\io\sql.py", line 1174, in to_sql
    table.insert(chunksize, method=method)
  File "C:\Users\DELL\PycharmProjects\MyALLRefProf\venv\lib\site-packages\pandas\io\sql.py", line 686, in insert
    exec_insert(conn, keys, chunk_iter)
  File "C:\Users\DELL\PycharmProjects\MyALLRefProf\venv\lib\site-packages\pandas\io\sql.py", line 599, in _execute_insert
    conn.execute(self.table.insert(), data)
  File "C:\Users\DELL\PycharmProjects\MyALLRefProf\venv\lib\site-packages\sqlalchemy\engine\base.py", line 948, in execute
    return meth(self, multiparams, params)
  File "C:\Users\DELL\PycharmProjects\MyALLRefProf\venv\lib\site-packages\sqlalchemy\sql\elements.py", line 269, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "C:\Users\DELL\PycharmProjects\MyALLRefProf\venv\lib\site-packages\sqlalchemy\engine\base.py", line 1060, in _execute_clauseelement
    compiled_sql, distilled_params
  File "C:\Users\DELL\PycharmProjects\MyALLRefProf\venv\lib\site-packages\sqlalchemy\engine\base.py", line 1200, in _execute_context
    context)
  File "C:\Users\DELL\PycharmProjects\MyALLRefProf\venv\lib\site-packages\sqlalchemy\engine\base.py", line 1413, in _handle_dbapi_exception
    exc_info
  File "C:\Users\DELL\PycharmProjects\MyALLRefProf\venv\lib\site-packages\sqlalchemy\util\compat.py", line 203, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "C:\Users\DELL\PycharmProjects\MyALLRefProf\venv\lib\site-packages\sqlalchemy\util\compat.py", line 186, in reraise
    raise value.with_traceback(tb)
  File "C:\Users\DELL\PycharmProjects\MyALLRefProf\venv\lib\site-packages\sqlalchemy\engine\base.py", line 1170, in _execute_context
    context)
  File "C:\Users\DELL\PycharmProjects\MyALLRefProf\venv\lib\site-packages\sqlalchemy\engine\default.py", line 504, in do_executemany
    cursor.executemany(statement, parameters)
  File "C:\Users\DELL\PycharmProjects\MyALLRefProf\venv\lib\site-packages\mysql\connector\cursor.py", line 654, in executemany
    return self.execute(stmt)
  File "C:\Users\DELL\PycharmProjects\MyALLRefProf\venv\lib\site-packages\mysql\connector\cursor.py", line 551, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File "C:\Users\DELL\PycharmProjects\MyALLRefProf\venv\lib\site-packages\mysql\connector\connection.py", line 490, in cmd_query
    result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
  File "C:\Users\DELL\PycharmProjects\MyALLRefProf\venv\lib\site-packages\mysql\connector\connection.py", line 267, in _send_cmd
    return self._socket.recv()
  File "C:\Users\DELL\PycharmProjects\MyALLRefProf\venv\lib\site-packages\mysql\connector\network.py", line 270, in recv_plain
    errno=2055, values=(self.get_address(), _strioerror(err)))
sqlalchemy.exc.OperationalError: (mysql.connector.errors.OperationalError) 2055: Lost connection to MySQL server at '127.0.0.1:3306', system error: 10053 An established connection was aborted by the software in your host machine [SQL: 'INSERT INTO `mnm_rotterdam_5_daily_details-20191216081027` (`Date`, `eNodeB Name`, `Downlink bandwidth`, `Downlink EARFCN`, `Cell Name`, `LocalCell Id`, `Physical cell ID`, `L.RRC.ConnReq.Att`, `L.RRC.ConnReq.Succ`, `RRC Setup Success Rate(%)`, `RRC Setup Success Rate (Service:mo-Data)(%)`, `RRC Setup Success Rate (Service:mt-Access)(%)`, `L.RRC.ReEstFail.Disc.FlowCtrl`, `L.RRC.SetupFail.NoReply`, `L.RRC.SetupFail.Rej`, `L.RRC.SetupFail.ResFail`, `RRC Setup Failure Num (Other Cause)`, `L.RRC.ReEst.Att`, `L.RRC.ReEst.Succ`, ................'Availability_4G_Cell(%)': 100, 'L.Cell.Avail.Dur(s)': 86400, 'Cell Unavail Duration(s)': 0})] (Background on this error at: http://sqlalche.me/e/e3q8)

and here's my full code

import pandas as pd
import os
import sqlalchemy as sa
import pyodbc

# MySQL Connection
MYSQL_USER = 'root'
MYSQL_PASSWORD = 'xxxxxxxxxxx'
MYSQL_HOST_IP = '127.0.0.1'
MYSQL_PORT = 3306
MYSQL_DATABASE = 'xlsx_test_db'

# connect db
engine = sa.create_engine('mysql+mysqlconnector://' + MYSQL_USER + ':' + MYSQL_PASSWORD + '@' + MYSQL_HOST_IP + ':' + str(
    MYSQL_PORT) + '/' + MYSQL_DATABASE, echo=False)
engine.connect()


mydir = (os.getcwd()).replace('\\', '/') + '/'

lte_details = pd.read_excel(r'' + mydir + 'MNM_Rotterdam_5_Daily_Details-20191216081027.xlsx', sheet_name='LTE Details', encoding='latin-1')


# reading and insert one file at a time
for file in os.listdir('.'):
    # only process excels files
    file_basename, extension = file.split('.')
    if extension == 'xlsx':
        lte_details.to_sql(file_basename.lower(), con=engine, if_exists='replace', index = False)



print(lte_details)
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
Mahmoud Al-Haroon
  • 2,239
  • 7
  • 36
  • 72
  • Can you show the error? – Simon Osipov Dec 24 '19 at 15:09
  • @Simon Osipov check edit please I post all code and error – Mahmoud Al-Haroon Dec 24 '19 at 15:22
  • Is seems like a problem is not in the amount of the data `An established connection was aborted by the software in your host machine` – Simon Osipov Dec 24 '19 at 16:00
  • `mysql+mysqlconnector://...` does not use pyodbc. – Gord Thompson Dec 24 '19 at 16:49
  • See also https://docs.sqlalchemy.org/en/13/dialects/mysql.html#module-sqlalchemy.dialects.mysql.mysqlconnector – Gord Thompson Dec 24 '19 at 16:55
  • @Simon Osipov So how can I solve this..... I just need to create a server that's accepts a huge data... that's all – Mahmoud Al-Haroon Dec 25 '19 at 10:08
  • On what ground do you base you feeling about that your server is not handling you data? – Simon Osipov Dec 25 '19 at 13:15
  • @Simon Osipov look Simon I am trying to create a project that's handle any update in excel file or any action in a certain path or server this server everyday get that excel file updated from another host... as I need any update happen in the excel just do the same update in the database... so I have to create database first and push these data to the sql with pandas and sql, also using watch dog library as to observe any update in this folder or directory happen.... So supposed that excel file have a very large data, as I need to create database Using ODBC local network.. – Mahmoud Al-Haroon Dec 26 '19 at 15:08

0 Answers0