help with getting some sql alchemy binding parameters to work?
Rick Harding
I'm trying to work on a script to auto add users to my proftp install on the server. I want to use bound parameters and in the SqlAlchemy docs I found an example that had a single bound paramter I'm trying to build off of. Of course, I need more than one bound parameter and I can't figure out how to get them to work.
The exception I am getting is:
Error caught: <type 'dict'>
Any ideas how I can use the pre-build sql query and pass in my list of bound parameter values?
Current code:
db_engine = sql.create_engine ("mysql://%s:%s@%s:3389/%s" %
(CONFIG["DBUSER"],
CONFIG["DBPASSWORD"],
CONFIG["DBSERVER"],
CONFIG["DBDATABASE"]))
add_user_query = """
INSERT INTO `ftpuser` ( `id` , `userid` , `passwd` , `uid` , `gid` ,
`homedir` , `shell` , `count` , `accessed` , `modified` )
VALUES (
NULL , :username, ENCRYPT( :password ) , :uid, :gid, :home,
'/sbin/nologin', '0', '', NOW( )
);
"""
db_egine.text = add_user_query
result = db_engine.execute({'username': username,
'password': password,
'uid': CONFIG['UID'],
'gid': CONFIG['GID'],
'home': '/home/' + username + '/www/'})
Thanks
Rick
Jim C. McDonald
On Oct 23, 1:00 pm, "Rick Harding" <deuce...@gmail.com> wrote:
Snip...
> db_egine.text = add_user_query
> result = db_engine.execute({'username': username,
> 'password': password,
> 'uid': CONFIG['UID'],
> 'gid': CONFIG['GID'],
> 'home': '/home/' + username + '/www/'})
>
Rick,
I'm not a SQLAchemy user but is the
problem as simple as a typo?
Should
"db_egine.text = add_user_query"
be
"db_engine.text = add_user_query" ?
- Jim McDonald
Rick Harding
Sorry, that's a typo in my case of trying to simplify the code. This whole thing takes place over a couple of functions and I was trying to break it down into just the required info. Ignore that.
Thanks for taking at look at it though.
Rick
Jim C. McDonald
> Thanks for taking at look at it though.
>
No problem.
If you're open to something other than
SQLAlchemy and your posted code maps
almost exactly to the Python DB-API.
To use just the straight Python DB-API
I'd try something like the following.
(Note: I didn't actually try to run the code
since I don't have the proper tables or your
config files, etc.)
- Jim C.
# ###############################
import MySQLdb
# create a connection
mydb = MySQLdb.Connect( db=CONFIG["DBDATABASE"],
user=CONFIG["DBDATABASE"],
passwd=CONFIG["DBPASSWORD"]
)
# create a cursor
cursor = mydb.cursor()
# fixed SQL text
add_user_query = """
INSERT INTO `ftpuser` (
`id` ,
`userid` ,
`passwd` ,
`uid` ,
`gid` ,
`homedir` ,
`shell` , `count` ,
`accessed` ,
`modified`
)
VALUES (
NULL ,
%(username)s,
ENCRYPT( %(password)s ),
%(uid)s,
%(gid)s,
%(home)s,
'/sbin/nologin', '0', '', NOW( )
);
"""
# put parameters in a dictionary
parameter_dict = { 'username': username,
'password': password,
'uid': CONFIG['UID'],
'gid': CONFIG['GID'],
'home': '/home/' + username + '/www/'
}
# execute the SQL using parms
cursor.execute(add_user_query,parameter_dict)
# fetch a result set
result_set = cursor.fetchall()
# call a commit
mydb.commit()
# #############################
Rick Harding
> Thanks for taking at look at it though.
>
No problem.
If you're open to something other than
SQLAlchemy and your posted code maps
almost exactly to the Python DB-API.
To use just the straight Python DB-API
I'd try something like the following.
(Note: I didn't actually try to run the code
since I don't have the proper tables or your
config files, etc.)
Thanks for the help. I know that SqlAlchemy uses mysqldb and I got looking to make sure that mysqldb will properly escape things when used that way as if they are bound parameters. It looks like it does so I changed my code to instead just use %s in the query and then execute the query like so and it's working:
INSERT INTO `ftpuser` ( `id` , `userid` , `passwd` , `uid` , `gid` ,
`homedir` , `shell` , `count` , `accessed` , `modified` )
VALUES (
);
"""
result = users_table.execute(add_user_query,
(username,
password,
CONFIG['UID'],
CONFIG['GID'],
'/home/' + username + '/www/'))
print 'Inserted ftp user: ' + username + ' into the ftpuser table'
Rick
Jim C. McDonald
On Oct 23, 4:20 pm, "Rick Harding" <deuce...@gmail.com> wrote:
> Thanks for the help. I know that SqlAlchemy uses mysqldb and I got looking
> to make sure that mysqldb will properly escape things when used that way as
> if they are bound parameters. It looks like it does so I changed my code to
> instead just use %s in the query and then execute the query like so and it's
> working:
Rick,
That makes sense.
Now that you mention it, I believe that
MySQLdb only supports the "format" and
"pyformat" paramstyle (and not the "named"
paramstyle used in your first post) .
- Jim McDonald