Posterous
Daniele is using Posterous to post everything online. Shouldn't you?
Unknown35
 

Daniel’strae

Is cuma cá mhinice a théann tú ar strae; is é is tábhachtaí gurb áil leat do bhealach a aimsiú arís.

Postgresql: duplicate schemas automatically

After this comment to a my question, im thinking if is better using 1 database with X schemas or viceversa.

My situation: im developing a web-app where, when people do register, i create (actually) a database (no, its not a social network: everyone must have access to his own data and never see the data of the other user).

Thats the way i used for the previus verison of my application (that is still running on mysql): throught the plesk api, for every registration i do:

  1. Create a database user with limited privileges;
  2. Create a database that can be accessed just by the previous created user and the superuser (for maintenance)
  3. Populate the db

Now, i'll need to do the same with posrtgresql (the project is getting mature and mysql.. dont fulfill all the needes)

I need to have all the databases/schemas backups indipendent: pg_dump works perfectly in both ways, the same for the users that can be configured to access just 1 schema or 1 database.

So, assuming you are more experienced potsgres users than me, what do you think is the best solution for my situation, and why?

There will be performance differences using $x db instead of $x schemas? And what solution will be better to mantein in future (reliability)?

Every help and suggestion is really appreciated.

Edit: i almost forgot: all of my databases/schemas will allways have the same structure!

Edit2: For the backups issue (using pg_dump), is maybe better using 1 db and many schemas, dumping all the schemas at once: recovering will be quite simple loading the main dump in a dev machine and then dump and restore just the schema needed: there is 1 additional step, but dumping all the schema seem faster then dumpin them one by one.

p.s: sorry if i forgot some 'W' char in the text, my keyboard suffer that button ;)

edit|close|delete|flag
 
 
"all of my databases/schemas will ever have the same structure!" do you mean they all have the same structure? Or never? – Osama ALASSIRY 17 hours ago
Sorry, yes, they all have the same structure forever: if i change one, i'll change all of them ;) – DaNieL 16 hours ago 

4 Answers

vote up 1 vote down
check

A PostgreSQL "schema" is roughly the same as a MySQL "database". Having many databases on a PostgreSQL installation can get problematic; having many schemas will work with no trouble. So you definitely want to go with one database and multiple schemas within that database.

link

1
 
This. Postgres doesn't allow you to query across databases, which can be pretty annoying. – matt b 3 hours ago

Great, i havent thought the possibility to query another schema without changing database or changing the connection. that's all, i'll go for 1 db and many schemas. Thanks guys! – DaNieL 25 mins ago 
add comment

vote up 0 vote down
check

I would say, go with multiple databases AND multiple schemas :)

Schemas in postgres are a lot like packages in Oracle, in case you are familiar with those. Databases are meant to differentiate between entire sets of data, while schemas are more like data entities.

For instance, you could have one database for an entire application with the schemas "UserManagement", "LongTermStorage" and so on. "UserManagement" would then contain the "User" table, as well as all stored procedures, triggers, sequences etc. that are needed for the user management.

Databases are entire programs, schemas are components.

link|flag


... and so i'll have 1 database, with inside the schemas: $customer1_user_schema, $customer2_user_schema, $customer3_user_schema, $customer1_documents_schema, $customer2_documents_schema, $customer3_documents_schema? Mh... dont seem a reliable way... and what about performance? And what about the code of my application (will be php and python)? so many schemas.. – DaNieL 15 hours ago 
add comment

vote up 0 vote down
check

A number of schemas should be more lightweight than a number of databases, although I cannot find a reference which confirms this.

But if you really want to keep things very separate (instead of refactoring the web application so that a "costomer" column is added to your tables), you may still want to use separate databases: I assert that you can more easily make restores of a particular customer's database this way -- without disturbing the other customers.

link|flag

add comment

vote up 0 vote down
check

Definitely, i'll go for the 1-db-many-schemas approach. This let me to dump all the database but restore just 1 very easly, in many ways:

  1. Dump the db (all the schema), load the dump in a new db, dump just the schema i need, and restore back in main db
  2. Dump the schema separately, one by one (but i think the machine ill suffer more this way.. and im aspecting like 500 schemas!)

Otherwise, googlin around iv'se seen that there is no auto-procedure to duplicate a schema (using one as a template), but many suggest this way:

  1. Create a template-schema
  2. When need to duplicate, rename it with new name
  3. Dump it
  4. Rename it back
  5. Restore the dump
  6. The magis is done.

I've written 2 rows in python to do that, hope thay can help someone (in-2-seconds-written-code, dont use it in production):

import os
import sys
import pg

#Take the ne shcema name from the second cmd arguments (the first is the filename)

newSchema = sys.argv[1]

#Temp folder for the dumps
dumpFile
= '/test/dumps/' + str(newSchema) + '.sql'

#Settings
db_name
= 'db_name'
db_user
= 'db_user'

db_pass = 'db_pass'
schema_as_template
= 'schema_name'


#Connection
pgConnect
= pg.connect(dbname= db_name, host='localhost', user= db_user, passwd= db_pass)

#Rename schema with the new name
pgConnect
.query("ALTER SCHEMA " + schema_as_template + " RENAME TO " + str(newSchema))

#Dump it
command
= 'export PGPASSWORD="' + db_pass + '" && pg_dump -U ' + db_user + ' -n ' + str(newSchema) + ' ' + db_name + ' > ' + dumpFile

os.system(command)
#Rename back with its default name

pgConnect.query("ALTER SCHEMA " + str(newSchema) + " RENAME TO " + schema_as_template)

#Restore the previus dump to create the new schema
restore
= 'export PGPASSWORD="' + db_pass + '" && psql -U ' + db_user + ' -d ' + db_name + ' < ' + dumpFile

os.system(restore)
#Want to delete the dump file?

os.remove(dumpFile)
#Close connection

pgConnect.close()

Loading mentions Retweet
Filed under  //   Database   Postgresql   Python  
Posted July 20, 2009
// 0 Comments

Where is the phpMailer php class equivalent for Python?

vote up 2 vote down
star
1

Hi guys, i'm new with python.. Actually, i'm trying to send featured email with python: html body, text alternative body, and attachment.

So, i've found this tutorial and adapted it with the gmail authentication (tutorial found here)

The code i have atm, is that:

def createhtmlmail (html, text, subject):
"""Create a mime-message that will render HTML in popular
  MUAs, text in better ones"""

import MimeWriter
import mimetools
import cStringIO
from email.MIMEMultipart import MIMEMultipart
from email.MIMEBase import MIMEBase
from email.MIMEText import MIMEText
from email.Utils import COMMASPACE, formatdate
from email import Encoders
import os

out = cStringIO.StringIO() # output buffer for our message
htmlin
= cStringIO.StringIO(html)
txtin
= cStringIO.StringIO(text)

writer
= MimeWriter.MimeWriter(out)
#
# set up some basic headers... we put subject here
# because smtplib.sendmail expects it to be in the
# message body
#
writer
.addheader("Subject", subject)
writer
.addheader("MIME-Version", "1.0")
#
# start the multipart section of the message
# multipart/alternative seems to work better
# on some MUAs than multipart/mixed
#
writer
.startmultipartbody("alternative")
writer
.flushheaders()
#
# the plain text section
#
subpart
= writer.nextpart()
subpart
.addheader("Content-Transfer-Encoding", "quoted-printable")
pout
= subpart.startbody("text/plain", [("charset", 'us-ascii')])
mimetools
.encode(txtin, pout, 'quoted-printable')
txtin
.close()
#
# start the html subpart of the message
#
subpart
= writer.nextpart()
subpart
.addheader("Content-Transfer-Encoding", "quoted-printable")
#
# returns us a file-ish object we can write to
#
pout
= subpart.startbody("text/html", [("charset", 'us-ascii')])
mimetools
.encode(htmlin, pout, 'quoted-printable')
htmlin
.close()


#
# Now that we're done, close our writer and
# return the message body
#
writer
.lastpart()
msg
= out.getvalue()
out.close()
return msg

import smtplib
f
= open("/path/to/html/version.html", 'r')
html
= f.read()
f
.close()
f
= open("/path/to/txt/version.txt", 'r')
text
= f.read()
subject
= "Prova email html da python, con allegato!"
message
= createhtmlmail(html, text, subject)
gmail_user
= "thegmailaccount@gmail.com"
gmail_pwd
= "thegmailpassword"
server
= smtplib.SMTP("smtp.gmail.com", 587)
server
.ehlo()
server
.starttls()
server
.ehlo()
server
.login(gmail_user, gmail_pwd)
server
.sendmail(gmail_user, "example@example.com", message)
server
.close()

and that works.. now only miss the attachment.. And i am not able to add the attachment (from this post)

So, why there is not a python class like phpMailer for php? Is it because, for a medium-able python programmer sending a html email with attachment and alt text body is so easy that a class is not needed? Or is because i just didn't find it?

If i'll be able to wrote a class like that, when i'll be enough good with python, would that be useful for someone?

S.Lott
41.7k328104
asked Apr 30 at 14:48
DaNieL
4189

4 Answers

vote up 4 vote down
check

If you can excuse some blatant self promotion, I wrote a mailer module that makes sending email with Python fairly simple. No dependencies other than the Python smtplib and email libraries.

Here's a simple example for sending an email with an attachment:

from mailer import Mailer
from mailer import Message

message
= Message(From="me@example.com",
                 
To=["you@example.com", "him@example.com"])
message
.Subject = "Kitty with dynamite"
message
.Body = """Kitty go boom!"""
message
.attach("kitty.jpg")

sender
= Mailer('smtp.example.com')
sender
.login("username", "password")
sender
.send(message)

Edit: Here's an example of sending an HTML email with alternate text. :)

from mailer import Mailer
from mailer import Message

message
= Message(From="me@example.com",
                 
To="you@example.com",
                  charset
="utf-8")
message
.Subject = "An HTML Email"
message
.Html = """This email uses <strong>HTML</strong>!"""
message
.Body = """This is alternate text."""

sender
= Mailer('smtp.example.com')
sender
.send(message)

Edit 2: Thanks to one of the comments, I've added a new version of mailer to pypi that lets you specify the port in the Mailer class.

answered Apr 30 at 16:56
Ryan Ginstrom
99019

 
 
add in an example of alternate text and you've got a winner! – YHVH Apr 30 at 18:32

Wow, that's great! Does your module allow send mail througt the Gmail smtp? Where should i specify the smtp port (such as server = smtplib.SMTP("smtp.gmail.com", 587) with smtplib) – DaNieL May 4 at 7:50 
 
 
@DaNiel: Very good point, thanks. I've added an updated version that lets you specify the port in the Mailer class (`sender = Mailer('localhost', port=587)') pypi.python.org/pypi/mailer/0.4 – Ryan Ginstrom May 7 at 10:47

Hi Ryan, i tried the 0.4 version with gmail but it raise an error: File "C:\Python26\lib\smtplib.py", line 522, in login raise SMTPException("SMTP AUTH extension not supported by the server") Maybe its becose gmail requires the TLS? – DaNieL May 11 at 7:00 
add comment

vote up 1 vote down
check

Django includes the class you need in core, docs here

from django.core.mail import EmailMultiAlternatives

subject
, from_email, to = 'hello', 'from@example.com', 'to@example.com'
text_content
= 'This is an important message.'
html_content
= '<p>This is an <strong>important</strong> message.</p>'
msg
= EmailMultiAlternatives(subject, text_content, from_email, [to])
msg
.attach_alternative(html_content, "text/html")
msg
.attach_file('/path/to/file.jpg')
msg
.send()

In my settings I have:

#GMAIL STUFF
EMAIL_USE_TLS
= True
EMAIL_HOST
= 'smtp.gmail.com'
EMAIL_HOST_USER
= 'name@gmail.com'
EMAIL_HOST_PASSWORD
= 'password'
EMAIL_PORT
= 587
answered Apr 30 at 15:22
YHVH
16817


That's what i was looking for! Thanks mate. Last thing: to use that class we must download and install django, obvisiuly.. Cold be fine a class that run without the django framework, but just running the class file (and the natural python modules)) – DaNieL Apr 30 at 15:30 

Fom Stackoverflow.com

Loading mentions Retweet
Filed under  //   Email   Python  
Posted June 5, 2009
// 0 Comments

Best way to retrieve variable values from a text file - Python - Json

vote up 1 vote down
star

Referring on this question, i have a similar -but not the same- problem..

On my way, i'll have some text file, structured like:

var_a: 'home'
var_b
: 'car'
var_c
: 15.5

And i need that python read the file and then create a variable named var_a with value 'home', and so on.

Example:

#python stuff over here
getVarFromFile
(filename) #this is the function that im lookin for
print var_b
#output: car, as string
print var_c
#output 15.5, as number.

Is this possible, i mean, even keep the var type?

Notice that i have the full freedom to the text file structure, i can use the format i like if the one i proposed isn't the best.

EDIT: the ConfigParser can be a solution, but i dont like it so much, becose in my script i'll have then to refer to the variables in the file with

config.get("set", "var_name")

But what i'll love is to refer to the variable direclty, as i declared it in the python script..

There is a way to impoer the file as a python dictionary?

Oh, last thing, keep in mind that i dont know exactly how many variables would i have in the text file

Edit 2: i'm very interessing to the stephan json solution, becose in that way the text file could be readed simply with others languages (php, then via ajax javascript, for example), but i fail in something while acting that solution:

#for the example, i dont load the file but create a var with the supposed file content
file_content
= "'var_a': 4, 'var_b': 'a string'"
mydict
= dict(file_content)
#Error: ValueError: dictionary update sequence element #0 has length 1; 2 is required
file_content_2
= "{'var_a': 4, 'var_b': 'a string'}"
mydict_2
= dict(json.dump(file_content_2, True))
#Error:
#Traceback (most recent call last):
#File "<pyshell#5>", line 1, in <module>
#mydict_2 = dict(json.dump(file_content_2, True))
#File "C:\Python26\lib\json\__init__.py", line 181, in dump
#fp.write(chunk)
#AttributeError: 'bool' object has no attribute 'write'

In what kind of issues can i fall with the Json format? And, how can i read a json array in a text file, and transform it in a python dict?

p.s: i dont like the solution using .py files, i'll prefer .txt, .inc, .whatever is not restrictive to one language

asked May 29 at 6:48
DaNieL
4189

 
 
You can't manage without any import modules... you could manage with just the sys module, but it wouldn't be as nice as the other solutions suggested :) – workmad3 May 29 at 7:12

ok, its not a big problem – DaNieL May 29 at 7:16 
 
 
regarding your Edit2: you want the_dict = json.loads('{"var_a": 4, "var_b": "a string"}'). Pls note that I have switched " and '. – stephan May 29 at 9:13

And that's exactly what i want. Thanks man! – DaNieL May 29 at 9:33 
add comment

6 Answers

vote up 2 vote down
check

Load your file with JSON or PyYAML into a dictionary the_dict (see doc for JSON or PyYAML for this step, both can store data type) and add the dictionary to your globals dictionary, e.g. using globals().update(the_dict).

If you want it in a local dictionary instead (e.g. inside a function), you can do it like this:

for x in the_dict.items():
   
exec('%s=%s' % x)

as long as it is safe to use exec. If not, you can use the dictionary directly.

answered May 29 at 7:42
stephan
5007


Can you please show a basilar example? – DaNieL May 29 at 7:58 
1
 
A shorter idiom is globals().update(the_dict) or locals().update(the_dict). – Jouni K. Seppänen May 29 at 10:12
 
 
Thanks for pointing this out for globals(). Amended my answer. locals() is however read-only (see docs.python.org/library/functions.html#locals/…). – stephan May 29 at 10:29
 
 
It's not really read-only (I have used it myself) but apparently there are circumstances in which writing to it doesn't work. Thanks for pointing this out. – Jouni K. Seppänen May 31 at 9:59
add comment

vote up 5 vote down
check

But what i'll love is to refer to the variable direclty, as i declared it in the python script..

Assuming you're happy to change your syntax slightly, just use python and import the "config" module.

# myconfig.py:

var_a
= 'home'
var_b
= 'car'
var_c
= 15.5

Then do

from myconfig import *

And you can reference them by name in your current context.

answered May 29 at 7:54
yangyang
963

 
 
+1, This is absolutely THE best way to do config files. No need to grow your own config syntax, config parsers, config loaders etc. when you can just re-use python's core parts! – Simon May 29 at 8:13
 
 
+1 yes, as long as you can trust your config file and don't need portability – stephan May 29 at 8:26
 
 
Convenience and flexibility ++ vs. security -- : in some situations it's great, in others... less so. – mavnn May 29 at 8:28

I have to discard this solution becose i dont want to restric the file to python only, i think is better to use a generic file format (.txt, .inc, .whatever) that can be accessed by others languages too (with all the safe measure that this solution will mean). Anyway i'll keep in mind this way for the future – DaNieL May 29 at 9:37 
add comment

vote up 5 vote down
check

Use ConfigParser.

Your config:

[myvars]
var_a
: 'home'
var_b
: 'car'
var_c
: 15.5

Your python code:

import ConfigParser

config
= ConfigParser.ConfigParser()
config
.read("config.ini")
var_a
= config.get("myvars", "var_a")
var_b
= config.get("myvars", "var_b")
var_c
= config.get("myvars", "var_c")
answered May 29 at 6:57
Igor Krivokon
1,3939


Just edited - this way is fine, but if possible im lookin for another one who would let me using the variables in a usefull way – DaNieL May 29 at 7:17 
 
 
I've updated the answer to show you how to use these as variables. – Igor Krivokon May 29 at 8:26
 
 
Or you could take the Bunch class from code.activestate.com/recipes/52308/ and hack it to be recursive, so you could refer to config.myvars.var_a directly. – Jouni K. Seppänen May 29 at 10:16
add comment

vote up 2 vote down
check

How reliable is your format? If the seperator is always exactly ': ', the following works. If not, a comparatively simple regex should do the job.

As long as you're working with fairly simple variable types, Python's eval function makes persisting variables to files surprisingly easy.

(The below gives you a dictionary, btw, which you mentioned was one of your prefered solutions).

def read_config(filename):
    f
= open(filename)
    config_dict
= {}
   
for lines in f:
        items
= lines.split(': ', 1)
        config_dict
[items[0]] = eval(items[1])
   
return config_dict
answered May 29 at 8:27
mavnn
3129


I can wrote the text file exaclty as i want, the format var: value is just for example, if others formats are better, fell free to suggest – DaNieL May 29 at 8:37 
 
 
As long as it's consistant, and the value is of the form generated by repr(variable) it doesn't really matter for this method. Just pick a seperator that will never be part of the variable name and use that in the split method. – mavnn May 29 at 10:47
add comment

vote up 1 vote down
check

What you want appear to want is the following, but this is NOT RECOMMENDED:

>>> for line in open('dangerous.txt'):
...     exec('%s = %s' % tuple(line.split(':', 1)))
...
>>> var_a
'home'

This creates somewhat similar behavior to PHP's register_globals and hence has the same security issues. Additionally, the use of exec that I showed allows arbitrary code execution. Only use this if you are absolutely sure that the contents of the text file can be trusted under all circumstances.

You should really consider binding the variables not to the local scope, but to an object, and use a library that parses the file contents such that no code is executed. So: go with any of the other solutions provided here.

(Please note: I added this answer not as a solution, but as an explicit non-solution.)

answered May 29 at 7:55
community wiki



Yes, thanks for the raccomandation, i've banned the exec function both from my php and python scripts time ago ;) – DaNieL May 29 at 7:59 
add comment

vote up 0 vote down
check

You can treat your text file as a python module and load it dynamically:

import

Loading mentions Retweet
Filed under  //   PHP   Python  
Posted June 5, 2009
// 0 Comments