One detail that catches people setting this up for scripts: if you gave the key a passphrase, you will still be prompted — for the passphrase rather than the password.
That is usually what you want interactively, and you handle it with an agent that holds the key unlocked for your session. For an unattended script running with no human present, you need either a key with no passphrase, restricted to exactly what it needs, or an agent already running with the key loaded.
If you use a passphraseless key, restrict it. On the server, the authorised keys file lets you pin a key to a single command, to specific source addresses, and to disable port forwarding and terminal allocation. A key that can only run one script from one address is a very different risk from one that grants a shell.
That pairing — passphraseless key, tightly restricted — is the standard way to automate this properly.