Perhaps you already know most of this, but here are some things to consider.
There is only one definition of Riemann integrability that must be very restrictive for it to work. I am not talking about inproper integrals here. On the other hand, an effective notion of Lebesgue integrability can be defined hierarchically as these restrictive conditions are weakened.
Start with sets of finite measure $E \subset \mathbb{R}$ and bounded functions $f:E \to \mathbb{R}$.
(1) Strictly speaking the Riemann integral is defined for functions on a closed and bounded interval $[a,b]$. Also, it is necessary for the function to be bounded to meet the requirement that there exists $I \in \mathbb{R}$ such that for any $\epsilon > 0$ there exists a partition $P_\epsilon$ of $[a,b]$ such that for any partition $P$ that is a refinement of $P_\epsilon$ and any Riemann sum $S(P,f)$,we have $|S(P,f) - I| < \epsilon$. That $f$ must be bounded is not just an arbitrary part of the definition.
It is, of course, possible to extend the definition to open intervals or even general subsets $E$ of finite measure with $\int_E f$ defined as $\int_a^b f(x) \chi_E(x) \, dx$. Nevetheless, the definition of Riemann integrability can only be met when the measure of the boundary $\partial E$ is $0$, and this is related to the notion of Jordan measurability.
Clearly, there are bounded functions defined on sets of finite measure that are not Riemann integrable -- as with the Dirichlet function you mention -- and this is entirely due to "too much" discontinuity.
(2) Again for bounded functions on sets of finite measure, there always exist lower and upper Lebesgue integrals
$$\underline{\int}_E f = \sup_{\phi \leqslant f} \int_E \phi, \quad \overline{\int_E} f = \inf_{\psi \geqslant f} \int_E \psi,$$
where $\phi$ and $\psi$ are simple functions, and we must have
$$\underline{\int}_E f\leqslant \overline{\int_E} f $$
The most basic definition in this restrictive case is that $f$ is "Lebesgue integrable" on E if
$$\underline{\int}_E f = \overline{\int_E} f$$
There are two important theorems for bounded functions on finite measure sets.
Theorem 1: If a function is Riemann integrable then it is Lebesgue integrable.
Theorem 2: A function is Lebesgue integrable if and only if it is measurable.
An important consequence of Theorem 1 is that the class of Lebesgue integrable functions includes the class of Riemann integrable functions.
An important consequence of Theorem 2 is that, similar to the Riemann integral, there exist bounded functions defined on a set of finite measure that are not Lebesgue integrable. To see this take $E$ as a non-measurable set and consider the function $\chi_E$.
You do raise an interesting question of why the Lebesgue integral is less impacted by the extent of discontinuity as long as we have measrability.
Next consider sets of infinite measure $E \subset \mathbb{R}$ and/or unbounded functions $f:E \to \mathbb{R}$.
Here we cannot even speak of Riemann integrals, yet the Lebesgue integral can be extended. First, we extend to nonnegative functions where the Lebesgue integral can be defined using the previous definition as the supremum of $\int_E g$ over all nonnegative, bounded, measurable functions $g$ with compact support in $E$. In this case the integral may take the value $+\infty$, so satisfaction of this definition alone does not mean that $F$ is Lebesgue integrable. For nonnegative $f$ to be Lebesgue integrable we must have $\int_E f < +\infty$.
The reason for this definition of Lebesgue integrability is to make it possible to extend the definition of the integral further to include general functions. In this case, we consider positive and negative parts $f^+$ and $f^-$ (which are themselves nonnegative functions) and define the Lebesgue integral as
$$\tag{*}\int_E f = \int_Ef^+ - \int_E f^-$$
Since $+\infty - +\infty$ cannot be defined in a meaningful way, this explains why Lebesgue integrability of a nonnegative functions stipulates that the Lebesgue integral is finite. Otherwise, (*) is not well defined. In this way, Lebesgue integrability of a general function $f$ implies that we also have
$$\int_E|f| = \int_Ef^+ + \int_E f^- < +\infty$$
Improper Riemann Integrals
In your question, you cite functions like $x \mapsto 1/x$ on $(0,1]$ and $x \mapsto 1/\sqrt{x}$ on $[1, \infty)$ as examples where the Lebesgue integral "fails". Needless to say, these functions are not Riemann integrable , but we can say that we have defined Lebesgue integrals
$$\int_{(0,1]} \frac{1}{x} = +\infty , \quad \int_{[1,\infty)} \frac{1}{\sqrt{x}} = +\infty$$
We just cannot say these functions are Lebesgue integrable as explained above.
Some of the deficiencies of the Riemann integral can be corrected by introducing the improper Riemann integral. We can even find examples where a function is improperly Riemann integrable but not Lebesgue integrable. Perhaps that should be considered as well in assessing the relative merits of Riemann and Lebesgue integration.
Best Answer
Let $E = (0,1)$ and $f(x) = \frac{1}{\sqrt{x}}$. Then $\int f < \infty$ but if $E = \sqcup_{k=1}^n E_k$ is a finite partition, then for some $k$, we have $\sup_{E_k} f = \infty$ and $\lambda(E_k) > 0$.