Data and rendering rules must operate together. If you use Mapnik styles which are written for osm2pgsql with other OSM data conversion tools you have too options: configure that other tool to produce a database that is similar to the one that osm2pgsql creates, or you must edit the Mapnik styles to match with the database that the other tool creates.
It is not possible to configure spatialite_osm_map in any way. GDAL OSM driver https://gdal.org/drivers/vector/osm.html has better configuration options with osmconf.ini but I am not sure if it is possible to create identical tables to what osm2pgsql creates. I fear it would not be easy.
The other option to edit the Mapnik styles to work with different database tables would be a huge work. See how much rendering rules there are in https://github.com/gravitystorm/openstreetmap-carto.
If you want to use the default Mapnik styles and keep data in SpatiaLite I would suggest to use osm2pgsql and convert data first into PostGIS. The you can copy the PostGIS tables into SpatiaLite with ogr2ogr. Mapnik styles should be usable then if you just edit the datasource.