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.
On Wednesday, April 22, 2015 at 12:44:08 PM UTC-7, Richard wrote:
>
> The version in the thread seems not have been included in web2py...
>
> Try the one in gluon/scripts/
>
> Richard
>
> On Wed, Apr 22, 2015 at 3:41 PM, Richard Vézina <[email protected]
> <javascript:>> wrote:
>
>> This regex : regex = re.compile('(.*?):(.*?)@(.*)')
>>
>> Seems to parse the below command line call!!
>>
>> Richard
>>
>> On Wed, Apr 22, 2015 at 3:39 PM, Richard Vézina <[email protected]
>> <javascript:>> wrote:
>>
>>>
>>> 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
>>>
>>> On Wed, Apr 22, 2015 at 3:28 PM, Ron Chatterjee <[email protected]
>>> <javascript:>> wrote:
>>>
>>>> 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.
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> On Wednesday, April 22, 2015 at 2:49:41 PM UTC-4, Richard wrote:
>>>>>
>>>>> What do you exactly get out of extract_mysql_models.py applied over an
>>>>> SQL dump of the schema (only no data inserts)?
>>>>>
>>>>> Richard
>>>>>
>>>>> On Wed, Apr 22, 2015 at 2:29 PM, Ron Chatterjee <[email protected]>
>>>>> wrote:
>>>>>
>>>>>> 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.
>>>>>>
>>>>>>
>>>>>>
>>>>>> On Wednesday, April 22, 2015 at 2:04:49 PM UTC-4, Richard wrote:
>>>>>>>
>>>>>>> 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 <
>>>>>>> [email protected]> 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.
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>> On Tuesday, April 21, 2015 at 4:58:52 PM UTC-4, Ron Chatterjee
>>>>>>>> wrote:
>>>>>>>>>
>>>>>>>>> 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://
>>>>>>>>> [email protected]:8000/my_database_name',migrate_enabled=False,
>>>>>>>>> pool_size=20) is the way to go about it.
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> On Tuesday, April 21, 2015 at 4:07:02 PM UTC-4, Richard wrote:
>>>>>>>>>>
>>>>>>>>>> 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
>>>>>>>>>>
>>>>>>>>>> On Tue, Apr 21, 2015 at 3:55 PM, Niphlod <[email protected]>
>>>>>>>>>> wrote:
>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>> On Tuesday, April 21, 2015 at 9:39:14 PM UTC+2, Ron Chatterjee
>>>>>>>>>>> wrote:
>>>>>>>>>>>>
>>>>>>>>>>>> I tried this:
>>>>>>>>>>>>
>>>>>>>>>>>> db =
>>>>>>>>>>>> DAL('mysql://[email protected]: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).
>>>>>>>>>>>
>>>>>>>>>>> --
>>>>>>>>>>> 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 [email protected].
>>>>>>>>>>> For more options, visit https://groups.google.com/d/optout.
>>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> --
>>>>>>>> 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 [email protected].
>>>>>>>> For more options, visit https://groups.google.com/d/optout.
>>>>>>>>
>>>>>>>
>>>>>>> --
>>>>>> 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 [email protected].
>>>>>> For more options, visit https://groups.google.com/d/optout.
>>>>>>
>>>>>
>>>>> --
>>>> 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 [email protected] <javascript:>.
>>>> For more options, visit https://groups.google.com/d/optout.
>>>>
>>>
>>>
>>
>
--
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 [email protected].
For more options, visit https://groups.google.com/d/optout.
'''
Create the web2py code needed to access your mysql legacy db.
To make this work all the legacy tables you want to access need to have an "id" field.
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.
Created by Falko Krause, minor modifications by Massimo Di Pierro and Ron McOuat
'''
import subprocess
import MySQLdb
import re
import sys
data_type_map = dict(
varchar='string',
int='integer',
integer='integer',
tinyint='integer',
smallint='integer',
mediumint='integer',
bigint='integer',
float='double',
double='double',
char='string',
decimal='integer',
date='date',
#year = 'date',
time='time',
timestamp='datetime',
datetime='datetime',
binary='blob',
blob='blob',
tinyblob='blob',
mediumblob='blob',
longblob='blob',
text='text',
tinytext='text',
mediumtext='text',
longtext='text',
)
username = 'root'
password = 'yes'
database_name = 'mysql'
try:
db = MySQLdb.connect(host = 'localhost',user =username,passwd = password,db =database_name)
except Exception as e:
sys.exit('we cant get into the db');
cursor = db.cursor()
#cursor.execute('INSERT INTO tabella(id) VALUES("Tom","Peggy was here")')
#cursor.execute('SELECT *FROM db')
cursor.execute("SHOW TABLES")
tables = cursor.fetchall()
connection_string = "legacy_db = DAL('mysql://%s:%s@localhost/%s')" % (username, password, database_name)
legacy_db_table_web2py_code = []
#cat_str = 'SELECT *From' + " " + table_name[0]
#get_table = cursor.execute(cat_str)
#sql_create_stmnt = cursor.fetchall()
for table_name in tables:
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()
if 'CREATE' in sql_create_stmnt:
sql_lines = sql_create_stmnt.split('\n')
sql_lines = filter(lambda x: not(x in ('','\r') or x[:2] in ('--','/*')),sql_lines)
web2py_table_code = ''
table_name = re.search('CREATE TABLE .(\S+). \(', sql_lines[0]).group(1)
fields = []
for line in sql_lines[1:-1]:
if re.search('KEY', line) or re.search('PRIMARY', line) or re.search(' ID', line) or line.startswith(')'):
continue
hit = re.search('(\S+)\s+(\S+)(,| )( .*)?', line)
if hit is not None:
name, d_type = hit.group(1), hit.group(2)
d_type = re.sub(r'(\w+)\(.*', r'\1', d_type)
name = re.sub('`', '', name)
web2py_table_code += "\n Field('%s','%s')," % (name, data_type_map[d_type])
web2py_table_code = "legacy_db.define_table('%s',%s\n migrate=False)" % (table_name, web2py_table_code)
legacy_db_table_web2py_code.append(web2py_table_code)
legacy_db_web2py_code = connection_string + "\n\n"
legacy_db_web2py_code += "\n\n#--------\n".join(legacy_db_table_web2py_code)
regex = re.compile('(.*?):(.*?)@(.*)')
if len(sys.argv) < 2 or not regex.match(sys.argv[1]):
print 'USAGE:\n\n extract_mysql_models.py username:password@data_basename\n\n'
else:
m = regex.match(sys.argv[1])
print mysql(m.group(3), m.group(1), m.group(2))
