creating DAL from SQL file

[Ron Chatterjee]

I have the following SQL file:

BEGIN TRANSACTION;

CREATE TABLE topic(

    id INTEGER PRIMARY KEY AUTOINCREMENT,

    title CHAR(512) NOT NULL,

    is_active CHAR(1),

    created_on TIMESTAMP,

    created_by INTEGER REFERENCES auth_user (id) ON DELETE CASCADE,

    modified_on TIMESTAMP,

    modified_by INTEGER REFERENCES auth_user (id) ON DELETE CASCADE

);

INSERT INTO `topic` VALUES(1,'regergerg','T','2015-04-14 14:32:52',1,'2015-04-14 14:32:52',1);

INSERT INTO `topic` VALUES(2,'egergerger','T','2015-04-14 14:33:27',1,'2015-04-14 14:33:27',1);

Is there a script that will convert this to db.py in other words, spit out the following code?

db.define_table('topic',

    Field('title', label=T('Title'), notnull=True, requires=IS_NOT_EMPTY()),

    auth.signature,    

    format='%(title)s',

)

I know its reverse engineering or going the other way sought of speak, but I asked because if I have a large SQL file from an existing site then creating the table by hand or looking at the schema may take some time. I realize accessing an existing database into web2py (or any framework) is a tricky tasks and csv import/export doesn't work all the time due to format issue (e.g., using csvstudio.py), but nevertheless I am wondering if there is a script that can automatically does this conversion. Like we have "extract_mysql_models.py" in the script folder but that only takes an existing mysql file and does the conversion. I don't think it does SQL to DAL directly. Am I correct? 

[Richard Vézina]

https://github.com/web2py/web2py/blob/master/scripts/extract_sqlite_models.py

Have you check gluon/scripts/ before ask?

Richard

On Sun, Apr 19, 2015 at 5:07 PM, Ron Chatterjee <achatte...@gmail.com> wrote:

I have the following SQL file:

BEGIN TRANSACTION;

CREATE TABLE topic(

    id INTEGER PRIMARY KEY AUTOINCREMENT,

    title CHAR(512) NOT NULL,

    is_active CHAR(1),

    created_on TIMESTAMP,

    created_by INTEGER REFERENCES auth_user (id) ON DELETE CASCADE,

    modified_on TIMESTAMP,

    modified_by INTEGER REFERENCES auth_user (id) ON DELETE CASCADE

);

INSERT INTO `topic` VALUES(1,'regergerg','T','2015-04-14 14:32:52',1,'2015-04-14 14:32:52',1);

INSERT INTO `topic` VALUES(2,'egergerger','T','2015-04-14 14:33:27',1,'2015-04-14 14:33:27',1);

Is there a script that will convert this to db.py in other words, spit out the following code?

db.define_table('topic',

    Field('title', label=T('Title'), notnull=True, requires=IS_NOT_EMPTY()),

    auth.signature,    

    format='%(title)s',

)

I asked because if I have a large SQL file then creating the table by hand or looking at the schema may take some time. Wondering if there is a script in the script folder that automoatically does this conversion. I know "extract_mysql_models.py" in the script folder but that only take an existing mysql file and does the conversion. I am not sure if it does SQL to DAL directly. Am I correct? 

--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups "web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to web2py+un...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Ron Chatterjee]

Yes I did. I forgot to add. The extract_sqlite_models.py didn't work as expect. And that may be because I am not implementing it right. 

>>>python extract_sqlite_models.py C:\Users\...\applications\my_application_name\databases\storage.sqlite

Which generates the following output.

# -*- coding: utf-8 -*-

legacy_db = DAL('sqlite://C:/Users/.../applications/my_application_name/databases/storage.sqlite')

legacy_db.define_table('auth_user',

    migrate=False)

#--------

legacy_db.define_table('auth_group',

    migrate=False)

#--------

legacy_db.define_table('auth_membership',

    migrate=False)

#--------

legacy_db.define_table('auth_permission',

    migrate=False)

#--------

legacy_db.define_table('auth_event',

    migrate=False)

#--------

legacy_db.define_table('auth_cas',

    migrate=False)

#--------

legacy_db.define_table('topic',

    migrate=False)

#--------

legacy_db.define_table('comment',

    migrate=False)

But these generates empty fields and may be its due to the new version (2.10.3) I am not sure. Probably have to open a ticket. Unless I am doing something wrong in calling that function.

[Richard Vézina]

Does the script complete correctly... These scripts are old, and I don't know many person who have use them... Anyway, 20 tables 50 tables is not much...

You can write your own which can be basic parser that convert table and field only and you have to set constrains yourself...

Richard

On Mon, Apr 20, 2015 at 7:55 PM, Ron Chatterjee <achatte...@gmail.com> wrote:

Yes I did. I forgot to add. The extract_sqlite_models.py didn't work as expect. And that may be because I am not doing it right. Thats why I didn't mention.

[Ron Chatterjee]

I need to stop being lazy I guess. lol. Thanks Richard. Will do.

[Richard Vézina]

:)

You may also consider fixing the scripts which may just need a little tweak if it where working before...

Richard

[Ron Chatterjee]

Can you confirm if it was working before? I just don't know if it was. I asked because I can take a stab at fixing it even though I am not best when it comes to python.But I just want to make sure that I ran it correctly.

[Richard Vézina]

I can't I remember having read that mysql script worked or have been used... You may consider write a dummy web2py model generate the DB (SQLite) export the SQLite Schema and attach here or to a ticket you open on github it will help to troobleshoot and fix it...

Richard

On Tue, Apr 21, 2015 at 2:29 PM, Ron Chatterjee <achatte...@gmail.com> wrote:

Can you confirm if it was working before? I just don't know if it was.

[Ron Chatterjee]

I can write it. But it will take some time to go through the syntax. I will let you know. 

[Ron Chatterjee]

I tried this:

db = DAL('mysql://ro...@127.0.0.1:8000/my_database_name',migrate_enabled=False, pool_size=20)

It didn't work either. I guess someone needs to look at how to connect to legacy database. 

[Niphlod]

On Tuesday, April 21, 2015 at 9:39:14 PM UTC+2, Ron Chatterjee wrote:

I tried this:

db = DAL('mysql://ro...@127.0.0.1:8000/my_database_name',migrate_enabled=False, pool_size=20)

It didn't work either. I guess someone needs to look at how to connect to legacy database. 

where is the password ?!

BTW: I think there's a bit of misunderstandings going on in this thread.
There are two separate concept at play: schema (structure) AND data.
Every script/extract_***_models.py can "inspect" an existing database and figure out (with limitations) the model you should write to access that database --> schema (or structure) translated to nifty "db.define_table(....)"

Exporting and importing a csv (compatible with what web2py generates) instead - again, with limitations - is the way to transfer data around.

If you have a long list of SQL statements in a file, those are NOT going to work.
There's virtually nothing that reverse-engineers table definitions such as "CREATE TABLE ....." to a model file, nor something that turns "INSERT INTO TABLE..." to a db.table.insert(), although it can be fun to create one (with lots of headaches).

[Richard Vézina]

And what would be the utility since you already have INSERT INTO TABLE... Someone can just use something like SQLite Manager (https://addons.mozilla.org/en-us/firefox/addon/sqlite-manager/) to import it... Once in SQLite DB (which anyway it should) he can use web2py csv export import if he want to migrate from SQLite to Postgres for instance...

Richard

[Ron Chatterjee]

I have sqlite browser. I am guessing its the same like SQlite Manager where someone can import, export csv or sql file. I don't have password in that database and work externally to the python 

I guess one possibility will be:

import MySQLdb

import sys

try:

db = MySQLdb.connect(host = 'localhost',user ='root',passwd = ' ',db = 'my_dabasename')

except Exception as e:

sys.exit('we cant get into the db');

cursor = db.cursor()

cursor.execute('SELECT *FROM table')

results = cursor.fetchall()

print results

Once the table is fetched then use the insert_into_table option to create db object.

But I agree with Niphlod, import, export csv is probably the only way to go around, if it works:-). And also agree with richard. sqlbroser does take the storage object as input and can export csv table and then use that back to create the database. I was just hoping to directly connect to my local server (WAMP) where I have the mysql database defined. I was under the impression,  db = DAL('mysql://ro...@127.0.0.1:8000/my_database_name',migrate_enabled=False, pool_size=20) is the way to go about it.

[Ron Chatterjee]

This is what I was looking for if I have a legacy database in mysql. 

When I tried 

db = DAL (URI)

URI being mysql, usename, password,localhost...I wasn't able to connect. Then I looked at this script.

https://groups.google.com/forum/#!searchin/web2py/extract_mysql_models.py/web2py/XPoTlzPG7lQ/ngSsMbd6zHAJ

But homehow the code didn't work for me even when I followed the example like it says. So, I am creating a stand alone version that works with MySQLdb leveraging this script. I wish there are few examples.

[Richard Vézina]

Hello Ron,

The more this thread goes the less I understand what you are trying to acheive... Do you want to translate a SQLite DB into a web2py model? or MySQL DB? If you do have a MySQL server instance your connection string seems correct... But then you have to define the table you want to access in this table or you only have DB connection active which give you notting because the DAL don't know the DB structure...

Richard

On Wed, Apr 22, 2015 at 1:30 PM, Ron Chatterjee <achatte...@gmail.com> wrote:

This is what I was looking for. 

https://groups.google.com/forum/#!searchin/web2py/extract_mysql_models.py/web2py/XPoTlzPG7lQ/ngSsMbd6zHAJ

But homehow the code didn't work for me even when I followed the example like it says. So, I am creating a stand alone version that works with MySQLdb.

[Ron Chatterjee]

Sorry, wasn't clear. Basically want to create a DAL (db.py) file from an existing site that I locally hosted (wAMP) which is php front end mysql backend. I exported out of my WAMP (phpmysqladmin) the tables and fields in SQL language. Then I tried to make DAL out of it. As you explained, thats not possible, Now I will try to take a stab at working with extract_mysql_models.py but it will probably will require some updates. If nothing works, I can use the MYSQL workbench to connect to the server to draw me the schema and then write the DAL from that. Hope it clear things up now. My bad.

[Richard Vézina]

What do you exactly get out of extract_mysql_models.py applied over an SQL dump of the schema (only no data inserts)?

Richard

[Ron Chatterjee]

I used this version (the first one) and change to MySQLdb.

https://groups.google.com/forum/#!searchin/web2py/extract_mysql_models.py/web2py/XPoTlzPG7lQ/ngSsMbd6zHAJ

It gives me an error:

Basically, 

This worked

db = MySQLdb.connect(host = 'localhost',user ='root',passwd = '',db = 'name_of_my_database')

But this didn't.

extract_mysql_models.py --user 'root' --password '' --host '127.0.0.1' --database 'name_of_my_database' --dalname 'wikidb' --colcomments --singlemigrate > mywiki.py

Note, my password is empty.

The error I get is simply that it can't connect to the server. So, I am going through the code now. 

[Richard Vézina]

https://github.com/web2py/web2py/blob/master/scripts/extract_mysql_models.py

Ok, it is not working exactly how I thought it was... Do you have myslqldump install?

Do you use Linux or Windows...

what if you do

python extract_mysql_models.py username:password@data_basename

[Richard Vézina]

This regex : regex = re.compile('(.*?):(.*?)@(.*)')

Seems to parse the below command line call!!

Richard

[Richard Vézina]

The version in the thread seems not have been included in web2py...

Try the one in gluon/scripts/

Richard

[Ron Chatterjee]

Thanks for all the help Richard. I tried to work around the code extract_mysql_models.py (attached is the code). Basically, I installed XAMP/WAMP and in the phpmyadmin I have the sql database. I am running this code but it fails in line #74:

 p = subprocess.Popen(['mysqldump','--user=%s' % username,'--password=%s' % password,'--skip-add-drop-table','--no-data', database_name,table_name[0]], stdin=subprocess.PIPE, stdout=subprocess.PIPE, stderr=subprocess.PIPE)

sql_create_stmnt, stderr = p.communicate()

I don't know what a mysql dump is. Wondering if the above two lines can be written like,

cat_str = 'SELECT *From' + " " + table_name[0]

get_table = cursor.execute(cat_str)

sql_create_stmnt = cursor.fetchall()

And then go on with if 'CREATE' in sql_create_stmnt:

Anyway, I am attaching the script if it make it makes it any clear.

[Richard Vézina]

I don't know what you are doing... As far as I can see the only thing you have to do is to is that you call the script with as argument a valid web2py db connection string...

a database dump is what is say you dump your db into a file... But I don't think this script work like that, I thought it could work over a file, but it seems to work against a web2py live db connection string...

Richard

[Ron Chatterjee]

I very simple question. Have you tried? Does the script works for you? Because it didn't work for me. Idk what you don't understand that I am doing. Its all the same code. someone can just step through it and figure out that its failing on subprocess.Popen. Idk why its so hard to understand. Specially someone as good as you. And I mean it the good way:-).

[Richard Vézina]

I never try it... I may try it for you, but not now, I am working late tonight to push a new app version in production :(

From the script doc :

This plugin needs:
	mysql
	mysqldump
	installed and globally available.
	Under Windows you will probably need to add the mysql executable directory to the PATH variable,
	you will also need to modify mysql to mysql.exe and mysqldump to mysqldump.exe below.
	Just guessing here :)
	Access your tables with:
	legacy_db(legacy_db.mytable.id>0).select()
	If the script crashes this is might be due to that fact that the data_type_map dictionary below is incomplete.
	Please complete it, improve it and continue.

1) Do you have mysqldump installed??  https://dev.mysql.com/doc/refman/5.1/en/mysqldump.html

2) Is it globally available (You talk about some LAMP setup so I guess you are under windows in a virtual LAMP stack?? if so, this has to be check)

3) Under windows? ... (same thing goes here...)

4) Are you able to create a dummy app define a connection string and define one of your model (one table of your mysql db) and then do a simple db().select(db.yourTableName.ALL)??

5) If the answer to 4 is yes I guess your are neer to make it works if the precedent point are cover...

Richard

[Ron Chatterjee]

I got the code to run half way. I am saying, if we can bypass subprocess.Popen by:

cat_str = 'SELECT *From' + " " + table_name[0]

get_table = cursor.execute(cat_str)

sql_create_stmnt = cursor.fetchall()

I may not need to have mysqldump or anything. I just don't know what subprocess.Popen does that we can't do with cursor.

[Richard Vézina]

You know what... You may consider using an older web2py version... If possible the version which saw this contrib be added, that way you should raise your chance to make it works properly...

This guy : https://groups.google.com/d/msg/web2py/5GNG-g1M8T8/fq8UN1fqumMJ

Report it was working, back to jully 2014... So go to github select the proper tag version and download an older web2py version anterior to july 2014.

Richard