where do you run database scripts/where are DBs 'located'?

[John Salerno]

Ok, I've been browsing through the MySQLdb docs, and I *think* I know
the kind of code I need to write (connect, cursor, manipulate data,
commmit, etc. -- although I probably need to get more familiar with
actual SQL commands too), but here's my problem: I don't know where
these scripts are supposed to be executed, or how they are supposed to
'find' the database.

Really, I have the same question for two different scenarios: accessing
and working with a database that is stored locally on my PC, and the
same with a DB that is on my web server space. I figure I already know
the host, username, password and database name (in fact, all these
things are spelled out as far as my web DB is concerned; I assume my
host name for the local DB is my computer's name).

But my question is, can these DB scripts be executed anywhere, and they
will find the DB based on the connect() parameters? Or do I need to do
something special with them? It seems like a similar problem to when you
don't have your PYTHONPATH variable set up properly. I've tried some DB
stuff, but it doesn't seem to work.

If more information is needed, I will try my local DB when I get home
later. As for the web DB, I figure there's probably more issues involved
that I'm not aware of, since web programming is still new to me.

So without actually giving you some code and tracebacks, is there any
general advice about how to set up these scripts? Also, is there any
better documentation than this:
http://sourceforge.net/docman/display_doc.php?docid=32071&group_id=22307

It doesn't seem too thorough, and it also doesn't cover actual SQL
queries that you'd have to pass to the query method. Maybe I will just
have to find that information in a MySQL tutorial.

Thanks.

 

[CatDude]

Ok, I've been browsing through the MySQLdb docs, and I *think* I know
the kind of code I need to write (connect, cursor, manipulate data,
commmit, etc. -- although I probably need to get more familiar with
actual SQL commands too), but here's my problem: I don't know where
these scripts are supposed to be executed, or how they are supposed to
'find' the database.

I'm kind of a noob myself, but I'll see if I can't offer some useful info
anyhow.

When you issue your MySQLdb.connect, that determines where the database
actions occur. If you specify "host='localhost'", then you are trying to
connect to your local machine. If you specify "host='db.smurgle.net'",
you're trying to connect to my home db server. You can specify the
hostname in any format that your local machine understands; for example,
if you are on a Linux box, and your /etc/hosts file contains a line like:
10.0.0.10 myDBserver
then you can use "host='myDBserver'".

This isn't so much a web programming issue as it is using MySQL's network
interface. MySQL by default uses port 3306 (I think) to handle database
connections over the network.

But my question is, can these DB scripts be executed anywhere, and they
will find the DB based on the connect() parameters? Or do I need to do
something special with them? It seems like a similar problem to when you
don't have your PYTHONPATH variable set up properly. I've tried some DB
stuff, but it doesn't seem to work.

Your scripts that make connections can be executed from anywhere that:
1) knows how to communicate with the specified database server, and
2) has some sort of MySQL client available.

So without actually giving you some code and tracebacks, is there any
general advice about how to set up these scripts? Also, is there any
better documentation than this:
http://sourceforge.net/docman/display_doc.php?docid=32071&group_id=22307

If you are having problems, by all means post some code.

It doesn't seem too thorough, and it also doesn't cover actual SQL
queries that you'd have to pass to the query method. Maybe I will just
have to find that information in a MySQL tutorial.

A couple of good MySQL tutorials would most likely be a big help. The
standard docs (the ones you pointed out) are not of a great deal of help
if you're totally new to SQL. You might want to do a Google search. This
will lead you to pages like:
http://www.kitebird.com/articles/pydbapi.html
http://www.devshed.com/c/a/Python/MySQL-Connectivity-With-Python/
and the like.

Dan

 

[John Salerno]

I'm kind of a noob myself, but I'll see if I can't offer some useful info
anyhow.

Thanks very much for the info! I'll give the 'localhost' a try again and
see if I was creating problems elsewhere, perhaps with the commands
themselves. (All I know for sure is that I created a MySQL database and
filled it with baseball statistics, so I know *that* exists, at least!
But it was created from the MySQL prompt itself, not using Python.)

I'll also read over those links, they seem like the kind of thing I need
to really get going with it.

 

[Dennis Lee Bieber]

these scripts are supposed to be executed, or how they are supposed to
'find' the database.

The RDBM server is responsible for "finding" the database data
files.

Really, I have the same question for two different scenarios: accessing
and working with a database that is stored locally on my PC, and the
same with a DB that is on my web server space. I figure I already know
the host, username, password and database name (in fact, all these
things are spelled out as far as my web DB is concerned; I assume my
host name for the local DB is my computer's name).

Bottom up... If the server is running on the same machine as the
client program, the host name typically is "localhost". That may also
work for your web server if the RDBM is on the same machine (the
username/password may be sufficient for them to set access privileges so
"your" database can only be modified by "you" -- and for security, they
may actually lock it down so that ONLY "localhost" is able to access;
otherwise one could run the clients directly on their workstations and
do cross network calls to the RDBM for query execution).

But my question is, can these DB scripts be executed anywhere, and they
will find the DB based on the connect() parameters? Or do I need to do

As mentioned, they don't care /where/ the database is. The connect
opens a bidirectional transport with the RDBM server. The server is told
the name of the default database, and it finds the tables in whatever
its local storage format uses [MyISAM tables are file triples
{definition, data, index} stored in a subdirectory of "database name";
InnoDB tables are all stored in specialized files where all the tables
are mixed into the file}.

something special with them? It seems like a similar problem to when you
don't have your PYTHONPATH variable set up properly. I've tried some DB
stuff, but it doesn't seem to work.

What "stuff". If you mean you tried running a script on your
development machine, specifying the web-server parameters, it may be
that 1) the RDBM is configured to only permit "localhost" connections
(for security -- only scripts ON the web-server machine can connect to
the RDBM on the same machine) or 2) a firewall is blocking connections
to/from the RDBM (this could have the same effect as a "localhost"-only
configuration, but is done external to the RDBM server -- RDBM
configuration is to list some combination of "database, user, password,
source machine" that restricts users to specified databases, from
specified machines).

So without actually giving you some code and tracebacks, is there any
general advice about how to set up these scripts? Also, is there any
better documentation than this:
http://sourceforge.net/docman/display_doc.php?docid=32071&group_id=22307

It doesn't seem too thorough, and it also doesn't cover actual SQL
queries that you'd have to pass to the query method. Maybe I will just
have to find that information in a MySQL tutorial.

Correct -- MySQLdb is just an "adapter" between Python and MySQL
(and it follows the DB-API PEP defining the minimal feature set that all
Python DB adapters should implement so that they can be swapped out with
minimal code changes).

SQL is a whole separate "language", there are many books available
for it -- some generic, most specific to some flavor.
--
Wulfraed Dennis Lee Bieber KD6MOG
(e-mail address removed) (e-mail address removed)
HTTP://wlfraed.home.netcom.com/
(Bestiaria Support Staff: (e-mail address removed))
HTTP://www.bestiaria.com/

 

[John Salerno]

If more information is needed, I will try my local DB when I get home
later.

Ok, here we go:

import MySQLdb

db = MySQLdb.connect(host='localhost',
user='johnjsal',
passwd='seinfeld',
db='bbdatabank')

cursor = db.cursor()
cursor.execute('SELECT * FROM Master')
data = cursor.fetchall()
cursor.close()
db.close()
print data

And the output:

Traceback (most recent call last):
File "C:\Python24\myscripts\db_test.py", line 6, in -toplevel-
db='bbdatabank')
File "C:\Python24\lib\site-packages\MySQLdb\__init__.py", line 66, in
Connect
return Connection(*args, **kwargs)
File "C:\Python24\lib\site-packages\MySQLdb\connections.py", line
134, in __init__
super(Connection, self).__init__(*args, **kwargs2)
OperationalError: (2003, "Can't connect to MySQL server on 'localhost'
(10061)")
The situation is this: I installed MySQL 5.0, created a database called
bbdatabank using the MySQL prompt. Within the database there is a table
called Master, which I'm testing on. Obviously something is happening
with the connect method. Perhaps I'm doing something wrong. Is there a
way to get more info about what's happening?

Thanks.

 

[John Salerno]

Traceback (most recent call last):
File "C:\Python24\myscripts\db_test.py", line 6, in -toplevel-
db='bbdatabank')
File "C:\Python24\lib\site-packages\MySQLdb\__init__.py", line 66, in
Connect
return Connection(*args, **kwargs)
File "C:\Python24\lib\site-packages\MySQLdb\connections.py", line
134, in __init__
super(Connection, self).__init__(*args, **kwargs2)
OperationalError: (2003, "Can't connect to MySQL server on 'localhost'
(10061)")

Latest development: I turned off my firewall and it worked.

 

[BartlebyScrivener]

John,

I had nothing but trouble connecting to my Access and MySql dbs until I
started using mxODBC. Search on it in this group, and you'll find the
links that were given to me and that I've shared with others. It works
like a charm.

If you come up short, I'll send you the links. I can't dig them up
right now. Back in an hour or so.

rick

 

[BartlebyScrivener]

Add the mysql db to your datasources using Administrative
Tools/Datasources(ODBC). Once that's done it's simple to access it,
using mxODBC.

I'm assuing you are on Windows XP?

Here's mxODBC

http://www.egenix.com/files/python/mxODBC.html

and you'll need the MySql connector

http://dev.mysql.com/downloads/connector/odbc/3.51.html

rick

 

[John Salerno]

Add the mysql db to your datasources using Administrative
Tools/Datasources(ODBC). Once that's done it's simple to access it,
using mxODBC.

I'm assuing you are on Windows XP?

Here's mxODBC

http://www.egenix.com/files/python/mxODBC.html

and you'll need the MySql connector

http://dev.mysql.com/downloads/connector/odbc/3.51.html

rick

Thanks. But is this like using a different module? I wouldn't be using
MySQLdb anymore?

 

[Dennis Lee Bieber]

Latest development: I turned off my firewall and it worked.

Next step -- figure out what rule you need to define to the firewall
to permit it to work...
--
Wulfraed Dennis Lee Bieber KD6MOG
(e-mail address removed) (e-mail address removed)
HTTP://wlfraed.home.netcom.com/
(Bestiaria Support Staff: (e-mail address removed))
HTTP://www.bestiaria.com/

 

[Gerard Flanagan]

Ok, I've been browsing through the MySQLdb docs, and I *think* I know
the kind of code I need to write (connect, cursor, manipulate data,
commmit, etc. -- although I probably need to get more familiar with
actual SQL commands too), but here's my problem: I don't know where [...]

So without actually giving you some code and tracebacks, is there any
general advice about how to set up these scripts? Also, is there any
better documentation than this:
http://sourceforge.net/docman/display_doc.php?docid=32071&group_id=22307

It doesn't seem too thorough, and it also doesn't cover actual SQL
queries that you'd have to pass to the query method. Maybe I will just
have to find that information in a MySQL tutorial.

Thanks.

Interactive SQL tutorial: http://www.sqlcourse.com/ ;
http://sqlcourse2.com/

Indirectly helpful maybe:
http://initd.org/tracker/pysqlite/wiki/basicintro

HTH

Gerard

 

[BartlebyScrivener]

John,

Yep, different module. I'll watch the thread. Perhaps once you get
connected, we should make a mini-HOWTO for XP users while it's fresh in
your mind, because this question seems to come up a lot, and beginners
would probably appreciate a short howto that would perhaps detail how
to set up either mysqldb, mxodbc, or both on XP and get Python talking
to MySQL.

I think no matter which you choose, you will benefit from downloading
the mysql connector and adding your mysql db to datasources on XP. Then
you can access the db just by using the name you gave it in the
datasources panel, e.g. driv="bbdatabank"

rick

 

[BartlebyScrivener]

driv="bbdatabank"

Oops, sorry, I meant,

driv='DSN=bbdatabank'

rick

 

[John Salerno]

Next step -- figure out what rule you need to define to the firewall
to permit it to work...

Yeah, that's actually what I'm trying to do now. I'm not sure how to
define it, but I assume it has something to do with 3306, since that's
the default.

I remember when I first installed MySQL on my computer, it would never
get past the configuration wizard, also because of a port/firewall
issue. So I uninstalled it, and then reinstalled it with the firewall
off and it worked fine. And this works fine too with the firewall off, I
just need to figure out what the criteria are for the new rule I need.

 

[John Salerno]

John,

Yep, different module. I'll watch the thread. Perhaps once you get
connected, we should make a mini-HOWTO for XP users while it's fresh in
your mind, because this question seems to come up a lot, and beginners
would probably appreciate a short howto that would perhaps detail how
to set up either mysqldb, mxodbc, or both on XP and get Python talking
to MySQL.

I think no matter which you choose, you will benefit from downloading
the mysql connector and adding your mysql db to datasources on XP. Then
you can access the db just by using the name you gave it in the
datasources panel, e.g. driv="bbdatabank"

rick

Well, I'm not too sure I need to do this now. The problem wasn't
anything to do with mysqldb after all, it was just that my firewall
wasn't allowing the connection. But with the firewall off, everything
seems to work fine.

 

[BartlebyScrivener]

But with the firewall off, everything
Whatever works. But I'm having trouble imagining how a firewall would
interfere with you accessing your own db on localhost.

 

[John Salerno]

Whatever works. But I'm having trouble imagining how a firewall would
interfere with you accessing your own db on localhost.

I don't know either. Something to do with the ports it's trying to use I
guess.

 

[Dennis Lee Bieber]

Yeah, that's actually what I'm trying to do now. I'm not sure how to
define it, but I assume it has something to do with 3306, since that's
the default.

What firewall application?

Zone Alarm, that I use, pops up a warning that "xyz" is trying to
act as a server -- I just had to configure it to allow server for local
and block internet.
--
Wulfraed Dennis Lee Bieber KD6MOG
(e-mail address removed) (e-mail address removed)
HTTP://wlfraed.home.netcom.com/
(Bestiaria Support Staff: (e-mail address removed))
HTTP://www.bestiaria.com/

 

[John Salerno]

What firewall application?

Zone Alarm, that I use, pops up a warning that "xyz" is trying to
act as a server -- I just had to configure it to allow server for local
and block internet.

Norton Internet Security. Usually it will pop up a warning and I can
accept or decline it, but it doesn't do it in this case. It just won't
connect to the database unless it's disabled.

 

[Dennis Lee Bieber]

Norton Internet Security. Usually it will pop up a warning and I can
accept or decline it, but it doesn't do it in this case. It just won't

connect to the database unless it's disabled.

I'd suggest that might be the server (started at boot time) that is
being blocked, but if the MySQL command line client can connect, it
isn't that. (By the way, have you installed the MySQL Query Browser and
Administrator programs? Much nicer than command line for experimenting)

http://lists.mysql.com/win32/18250 isn't any help -- no replies yet <G>

So... I'd suspect it is the firewall configuration for the Python
executable that is being blocked. And you likely already set an override
for Python so the pop-up is not being seen (because your python script
goes through the Python interpreter, it is the interpreter that is the
application, not the script).

Open the firewall configuration for programs, find the Python
entries, and see if there are options to allow outgoing ports for local
network (you may also have to see what IP numbers are considered "local"
network).
--
Wulfraed Dennis Lee Bieber KD6MOG
(e-mail address removed) (e-mail address removed)
HTTP://wlfraed.home.netcom.com/
(Bestiaria Support Staff: (e-mail address removed))
HTTP://www.bestiaria.com/