Newbie question: SMTP -> SQL Server

[hg]

I have an externally-written piece of software that spits out emails
using SMTP (a few hundred per hour) and I would like to dump the
content of them in an MS SQL Server database.

I have barely used Python before but it looks as if it could do the
job. I have no experience with mail agents. My starting point is this
recipe:

http://aspn.activestate.com/ASPN/Cookbook/Python/Recipe/440690

I know that there are various libraries that can connect to MS SQL
server and I am going to research these. http://sourceforge.net/projects/mysql-python

I have two questions:

i) Does this sound like an efficient way of transferring data to the
database?

Not certain I understand

ii) Looking through the Python documentation, I cannot see a way to
set the password on the SMTP server. I would like to have a password -
does anyone know how to use the recipe above to set one?

s = smtplib.SMTP()
.....
s.login(...,'password')

Any other useful nudges in the right direction appreciated.

Many thanks

Jon

hg

 

[jrpfinch]

I have an externally-written piece of software that spits out emails
using SMTP (a few hundred per hour) and I would like to dump the
content of them in an MS SQL Server database.

I have barely used Python before but it looks as if it could do the
job. I have no experience with mail agents. My starting point is this
recipe:

http://aspn.activestate.com/ASPN/Cookbook/Python/Recipe/440690

I know that there are various libraries that can connect to MS SQL
server and I am going to research these.

I have two questions:

i) Does this sound like an efficient way of transferring data to the
database?
ii) Looking through the Python documentation, I cannot see a way to
set the password on the SMTP server. I would like to have a password -
does anyone know how to use the recipe above to set one?

Any other useful nudges in the right direction appreciated.

Many thanks

Jon

 

[jrpfinch]

Thank you. I have just realised I completely misunderstand how SMTP
servers work. From what I can tell, when you run the cookbook script
it listens locally on port 8025.

You then have to configure a Linux (in my case) account with a username
and password so my external piece of software (on another Windows
machine) can log in and use the SMTP server.

Then I write the some code in the cookbook example to redirect any mail
content to the MS SQL server database.

Note that the external piece of software can only talk SMTP - this is
why I am having to develop this script.

Does this sound sensible? Any tips on how to configure my Linux box (I
don't have much experience with Linux either)?

Many thanks

Jon

 

[Steve Holden]

Thank you. I have just realised I completely misunderstand how SMTP
servers work. From what I can tell, when you run the cookbook script
it listens locally on port 8025.

You then have to configure a Linux (in my case) account with a username
and password so my external piece of software (on another Windows
machine) can log in and use the SMTP server.

Then I write the some code in the cookbook example to redirect any mail
content to the MS SQL server database.

Note that the external piece of software can only talk SMTP - this is
why I am having to develop this script.

Does this sound sensible? Any tips on how to configure my Linux box (I
don't have much experience with Linux either)?

Many thanks

Jon

Presumably you can configure the external piece of software at least to
the extent of setting the email address it delivers to?

In that case it will be *much* simpler to set up an account on a mail
server to receive these emails. That way the server will be available
whenever the SMTP-based sender wants or needs to send mail, and you can
use Python to collect the emails in batched using the POP protocol, as a
client.

Once you receive the email messages then yes, it's perfectly practical
to save the content in a SQL server database. mxODBC is one possible
driver package (though it requires you to purchase a license for
commercial use), and I seem to remember there's another one called
adodbapi that is open source. There are almost certainly others, but
it's a while since I used SQL Server with Python.

regards
Steve

 

[Larry Bates]

Presumably you can configure the external piece of software at least to
the extent of setting the email address it delivers to?

In that case it will be *much* simpler to set up an account on a mail
server to receive these emails. That way the server will be available
whenever the SMTP-based sender wants or needs to send mail, and you can
use Python to collect the emails in batched using the POP protocol, as a
client.

Once you receive the email messages then yes, it's perfectly practical
to save the content in a SQL server database. mxODBC is one possible
driver package (though it requires you to purchase a license for
commercial use), and I seem to remember there's another one called
adodbapi that is open source. There are almost certainly others, but
it's a while since I used SQL Server with Python.

regards
Steve

Good advice.

BTW-You can use dbi and odbc modules to work with SQL server without
purchasing anything.

 

[Larry Bates]

Presumably you can configure the external piece of software at least to
the extent of setting the email address it delivers to?

In that case it will be *much* simpler to set up an account on a mail
server to receive these emails. That way the server will be available
whenever the SMTP-based sender wants or needs to send mail, and you can
use Python to collect the emails in batched using the POP protocol, as a
client.

Once you receive the email messages then yes, it's perfectly practical
to save the content in a SQL server database. mxODBC is one possible
driver package (though it requires you to purchase a license for
commercial use), and I seem to remember there's another one called
adodbapi that is open source. There are almost certainly others, but
it's a while since I used SQL Server with Python.

regards
Steve

Good advice.

BTW-You can use dbi and odbc modules to work with SQL server without
purchasing anything.