insert some tuples into table in web2py

Question

I want to import csv file into auth_user table without duplicate username，so I create table user

db.define_table('user',
                Field('username',unique=True,length=255),
                Field('password'))

import csv file into user then compare table user and auth_user, insert some records that in user table while not in auth_user table.

db.executesql('insert into auth_user(username,password) select username,password from user where not exists(select * from auth_user where (auth_user.username=user.username))')

it doesn't work, so I don't know how to insert selected tuples into auth_user table. thanks

Answer 1

By default, passwords are hashed when inserted into the auth_user table (via a form validator associated with the password field). So, you don't want to do a standard SQL insert of the plain text passwords into the table (not only is that insecure, but subsequent attempts at login will fail because Auth is expecting hashed passwords).

The easiest way to have the hashing done when doing bulk inserts is to loop through the records and insert each one using the .validate_and_insert method. This will run all of the field validators (which will result in the passwords being hashed), and any records that fail validation will simply not be inserted (so, for example, a duplicate username will not be inserted because it will fail validation).

for user in db(db.user).select():
    db.auth_user.validate_and_insert(username=user.username, password=user.password)

Although the validation process will automatically reject any duplicate usernames, if you expect a lot of duplicates and want to improve efficiency, you can first select only the non-duplicates from the user table:

users = db(~db.user.username.belongs(db()._select(db.auth_user.username))).select()
for user in users:
    db.auth_user.validate_and_insert(username=user.username, password=user.password)

Also, note that by default, the auth_user table does require values in the first_name, last_name, and email fields (and a valid email address is needed for some of the Auth functionality, such as resetting the password). So, you should either plan to fill in those fields as well, or otherwise set their requires attributes to None so validation does not fail. For example:

db.auth_user.first_name.requires = None

Another option is to define a custom auth_user table.

Answer 2

Try to cghange your use of subselect:

insert into auth_user(username,password) select username,password from user 
where UserName not in (select username from auth_user where   
(auth_user.username=user.username))'