[FIXED] MySQLdb._exceptions.OperationalError: (1048, "..........' cannot be null")

Issue

I am trying to push my excel.xlsm file data to sql php myadmin and this is my code as I created a module that dict between the database sql and the excel.xlsm database as the columns name in xlsm is different from columns name in database sql here's my simple code

 from sqlalchemy import create_engine
 import pandas as pd
 import os
 import MySQLdb

 engine = create_engine('mysql+mysqldb://root:@localhost/myDB? 
 charset=utf8mb4&binary_prefix=true', echo=False)

 mydir = (os.getcwd()).replace('\\', '/') + '/'
 all_data = pd.read_excel(r'' + mydir + 'Governance_Tracker - Copy - 
 Copy.xlsm'
 ,header = 1).drop(['#'], axis=1)
 all_data.replace('\n','', regex=True)
 df = all_data.where((pd.notnull(all_data)), None)
 print(df)
 for i in range(len(df)):
 data_dict ={}
 data_dict['site_name'] = df['Site Name'][i]
 data_dict['region'] = df['Region'][i]
 data_dict['site_type'] = df['Site Type'][i]
 data_dict['site_code'] = df['SiteCode'][i]
 data_dict['tac_name'] = df['TAC Name'][i]
 data_dict['dt_readiness'] = df['DT\nReadiness'][i]
 data_dict['rfs'] = df['RFS'][i]
 data_dict['rfs_date'] = df['RFS Date'][i]
 data_dict['huawei_1st_submission_date'] = df['Huawei 1st submission date 
 '][i]
 data_dict['te_1st_submission_date'] = df['TE 1st Response date '][i]
 data_dict['huawei_2nd_submission_date'] = df['Huawei 2nd submission date 
 '][i]
 data_dict['te_2nd_submission_date'] = df['TE 2nd Response date '][i]
 data_dict['huawei_3rd_submission_date'] = df['Huawei 3rd submission date 
 '][i]
 data_dict['te_3rd_submission_date'] = df['TE 3rd Response date '][i]
 data_dict['acceptance_date_opt'] = df['Acceptance Date(Optimization)'][i]
 data_dict['acceptance_date_plan'] = df['Acceptance Date(Planning)'][i]
 data_dict['signed_sites'] = df['signed sites'][i]
 data_dict['as_built_date'] = df['As Built Date'][i]
 data_dict['as_built_status'] = df['AS built status'][i]
 data_dict['date_dt'] = df['Date DT'][i]
 data_dict['dt_status'] = df['DT Status'][i]
 data_dict['shr_status'] = df['SHR Status'][i]
 data_dict['dt_planned'] = df['DT Planned'][i]
 data_dict['integeration_status'] = df['Integeration Status'][i]
 data_dict['comments_snags'] = df['Comments/snags'][i]
 data_dict['cluster_name'] = df['Cluster name'][i]
 data_dict['type_standalone_colocated'] = df['Type(Standalone/colocated)'] 
 [i]
 data_dict['installed_type_standalone_colocated'] = df['Installed type 
 (Standalone/colocated)'][i]
 data_dict['status'] = df[' Status '][i]
 data_dict['pending'] = df['Pending  '][i]
 data_dict['pending_status'] = df['Pending Status'][i]
 data_dict['problematic_details'] = df['problematic details'][i]
 df1 = pd.DataFrame(data_dict,index=[0])
 df1.to_sql('govtracker', con=engine,if_exists='append',index=False)

 print("Success")
 rows = engine.execute("SELECT * FROM govtracker").fetchall()
 print(rows)

but I find these error in terminal traceback error

 Traceback (most recent call last):
 File "C:\Users\DELL\PycharmProjects\GUIRef\venv\lib\site- 
 packages\sqlalchemy\engine\base.py", line 1244, in _execute_context
 cursor, statement, parameters, context
 File "C:\Users\DELL\PycharmProjects\GUIRef\venv\lib\site- 
 packages\sqlalchemy\engine\default.py", line 552, in do_execute
 cursor.execute(statement, parameters)
 File "C:\Users\DELL\PycharmProjects\GUIRef\venv\lib\site- 
 packages\MySQLdb\cursors.py", line 206, in execute
 res = self._query(query)
 File "C:\Users\DELL\PycharmProjects\GUIRef\venv\lib\site- 
 packages\MySQLdb\cursors.py", line 312, in _query
 db.query(q)
 File "C:\Users\DELL\PycharmProjects\GUIRef\venv\lib\site- 
 packages\MySQLdb\connections.py", line 224, in query
_mysql.connection.query(self, query)
 MySQLdb._exceptions.OperationalError: (1048, "Column 
 'te_1st_submission_date' cannot be null")
 The above exception was the direct cause of the following exception:
 Traceback (most recent call last):
 File "<input>", line 1, in <module>
 File "C:\Program Files\JetBrains\PyCharm 
 2019.1.2\helpers\pydev\_pydev_bundle\pydev_umd.py", line 197, in runfile
 pydev_imports.execfile(filename, global_vars, local_vars)  # execute the 
 script
 File "C:\Program Files\JetBrains\PyCharm 
 2019.1.2\helpers\pydev\_pydev_imps\_pydev_execfile.py", line 18, in 
 execfile
 exec(compile(contents+"\n", file, 'exec'), glob, loc)
 File "C:/Users/DELL/PycharmProjects/GUIRef/PyGUI.py", line 49, in 
 <module>
 df1.to_sql('govtracker', con=engine,if_exists='append',index=False)
 File "C:\Users\DELL\PycharmProjects\GUIRef\venv\lib\site- 
 packages\pandas\core\generic.py", line 2531, in to_sql
 dtype=dtype, method=method)
 File "C:\Users\DELL\PycharmProjects\GUIRef\venv\lib\site- 
 packages\pandas\io\sql.py", line 460, in to_sql
 chunksize=chunksize, dtype=dtype, method=method)
 File "C:\Users\DELL\PycharmProjects\GUIRef\venv\lib\site- 
 packages\pandas\io\sql.py", line 1174, in to_sql
 table.insert(chunksize, method=method)
 File "C:\Users\DELL\PycharmProjects\GUIRef\venv\lib\site- 
 packages\pandas\io\sql.py", line 686, in insert
 exec_insert(conn, keys, chunk_iter)
 File "C:\Users\DELL\PycharmProjects\GUIRef\venv\lib\site- 
 packages\pandas\io\sql.py", line 599, in _execute_insert
 conn.execute(self.table.insert(), data)
 File "C:\Users\DELL\PycharmProjects\GUIRef\venv\lib\site- 
 packages\sqlalchemy\engine\base.py", line 988, in execute
 return meth(self, multiparams, params)
 File "C:\Users\DELL\PycharmProjects\GUIRef\venv\lib\site- 
 packages\sqlalchemy\sql\elements.py", line 287, in _execute_on_connection
 return connection._execute_clauseelement(self, multiparams, params)
 File "C:\Users\DELL\PycharmProjects\GUIRef\venv\lib\site- 
 packages\sqlalchemy\engine\base.py", line 1107, in _execute_clauseelement
 distilled_params,
 File "C:\Users\DELL\PycharmProjects\GUIRef\venv\lib\site- 
 packages\sqlalchemy\engine\base.py", line 1248, in _execute_context
 e, statement, parameters, cursor, context
 File "C:\Users\DELL\PycharmProjects\GUIRef\venv\lib\site- 
 packages\sqlalchemy\engine\base.py", line 1466, in 
 _handle_dbapi_exception
 util.raise_from_cause(sqlalchemy_exception, exc_info)
 File "C:\Users\DELL\PycharmProjects\GUIRef\venv\lib\site- 
 packages\sqlalchemy\util\compat.py", line 383, in raise_from_cause
 reraise(type(exception), exception, tb=exc_tb, cause=cause)
 File "C:\Users\DELL\PycharmProjects\GUIRef\venv\lib\site- 
 packages\sqlalchemy\util\compat.py", line 128, in reraise
 raise value.with_traceback(tb)
 File "C:\Users\DELL\PycharmProjects\GUIRef\venv\lib\site- 
 packages\sqlalchemy\engine\base.py", line 1244, in _execute_context
 cursor, statement, parameters, context
 File "C:\Users\DELL\PycharmProjects\GUIRef\venv\lib\site- 
 packages\sqlalchemy\engine\default.py", line 552, in do_execute
 cursor.execute(statement, parameters)
 File "C:\Users\DELL\PycharmProjects\GUIRef\venv\lib\site- 
 packages\MySQLdb\cursors.py", line 206, in execute
 res = self._query(query)
 File "C:\Users\DELL\PycharmProjects\GUIRef\venv\lib\site- 
 packages\MySQLdb\cursors.py", line 312, in _query
 db.query(q)
 File "C:\Users\DELL\PycharmProjects\GUIRef\venv\lib\site- 
 packages\MySQLdb\connections.py", line 224, in query
_mysql.connection.query(self, query)
sqlalchemy.exc.OperationalError: (MySQLdb._exceptions.OperationalError) 
(1048, "Column 'te_1st_submission_date' cannot be null")
[SQL: INSERT INTO govtracker (site_name, region, site_type, site_code, 
tac_name, dt_readiness, rfs, rfs_date, huawei_1st_submission_date, 
te_1st_submission_date, huawei_2nd_submission_date, 
te_2nd_submission_date, huawei_3rd_submission_date, 
te_3rd_submission_date, acceptance_date_opt, acceptance_date_plan, 
signed_sites, as_built_date, as_built_status, date_dt, dt_status, 
shr_status, dt_planned, integeration_status, comments_snags, cluster_name, 
type_standalone_colocated, installed_type_standalone_colocated, status, 
pending, pending_status, problematic_details) VALUES (%s, %s, %s, %s, %s, 
%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, 
%s, %s, %s, %s, %s, %s, %s, %s, %s)]
[parameters: ('Manshyet Naser', 'Cairo', 'Existing', 'LCAIE10002', 
'Manshyet nasser', datetime.datetime(2017, 7, 20, 0, 0), 'yes', 
datetime.datetime(2017, 7, 23, 0, 0), datetime.datetime(2017, 9, 12, 0, 
0), None, None, None, None, None, datetime.datetime(2017, 9, 17, 0, 0), 
None, 'PAC Signed', None, 'Done', None, None, None, None, None, None, 'Mid 
Cairo', 'L700+L1800', None, 'PAC Signed', None, None, None)]
(Background on this error at: http://sqlalche.me/e/e3q8)

and this is my sql query in php myadmin

 CREATE TABLE `mydb`.`govtracker` (
 `id` DOUBLE(255, 0) NOT NULL,
 `site_name` VARCHAR(255) NOT NULL,
 `region` VARCHAR(255) NOT NULL,
 `site_type` VARCHAR(255) NOT NULL,
 `site_code` VARCHAR(255) NOT NULL,
 `tac_name` VARCHAR(255) NOT NULL,
 `dt_readiness` DATE NOT NULL,
 `rfs` BOOLEAN NOT NULL,
 `rfs_date` DATE NOT NULL,
 `huawei_1st_submission_date` DATE NOT NULL,
 `te_1st_submission_date` DATE NOT NULL,
 `huawei_2nd_submission_date` DATE NOT NULL,
 `te_2nd_submission_date` DATE NOT NULL,
 `huawei_3rd_submission_date` DATE NOT NULL,
 `te_3rd_submission_date` DATE NOT NULL,
 `acceptance_date_opt` DATE NOT NULL,
 `acceptance_date_plan` DATE NOT NULL,
 `signed_sites` VARCHAR(255) NOT NULL,
 `as_built_date` DATE NOT NULL,
 `as_built_status` VARCHAR(255) NOT NULL,
 `date_dt` DATE NOT NULL,
 `dt_status` VARCHAR(255) NOT NULL,
 `shr_status` VARCHAR(255) NOT NULL,
 `dt_planned` INT(255) NOT NULL,
 `integeration_status` VARCHAR(255) NOT NULL,
 `comments_snags` LONGTEXT NOT NULL,
 `cluster_name` LONGTEXT NOT NULL,
 `type_standalone_colocated` VARCHAR(255) NOT NULL,
 `installed_type_standalone_colocated` VARCHAR(255) NOT NULL,
 `status` VARCHAR(255) NOT NULL,
 `pending` VARCHAR(255) NOT NULL,
 `pending_status` LONGTEXT NOT NULL,
 `problematic_details` LONGTEXT NOT NULL,
 `ets_tac` INT(255) NOT NULL,
 `region_r` VARCHAR(255) NOT NULL,
 `sf6_signed_date` DATE NOT NULL,
 `sf6_signed_comment` LONGTEXT NOT NULL,
 `comment_history` LONGTEXT NOT NULL,
 `on_air_owner` VARCHAR(255) NOT NULL,
 `pp_owner` VARCHAR(255) NOT NULL,
 `report_comment` LONGTEXT NOT NULL,
 `hu_opt_area_owner` VARCHAR(255) NOT NULL,
 `planning_owner` VARCHAR(255) NOT NULL,
 `po_number` VARCHAR(255) NOT NULL,
 `trigger_date` DATE NOT NULL,
 `as_built_status_tr` VARCHAR(255) NOT NULL
 ) ENGINE = InnoDB;

Solution

the error is clear you have a column in your table named te_1st_submission_date declared as

`te_1st_submission_date` DATE NOT NULL,

but you are trying to insert a null value for this column

1048, "Column 'te_1st_submission_date' cannot be null")

Probably your assignment

data_dict['te_1st_submission_date'] = df['TE 1st Response date '][i]

contain a null value

try add a valid value for this (and eventually other null values)

Answered By - ScaisEdge