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/displa...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]

On Fri, 12 May 2006 14:01:51 +0000, John Salerno wrote:

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/displa...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/My...y-With-Python/
and the like.

Dan

[John Salerno]

CatDude wrote:

On Fri, 12 May 2006 14:01:51 +0000, John Salerno wrote:

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.

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. :)

[John Salerno]

John Salerno wrote:

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]

John Salerno wrote:

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]

BartlebyScrivener wrote:

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?

[Gerard Flanagan]

John Salerno wrote:

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/displa...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]

Dennis Lee Bieber wrote:

On Fri, 12 May 2006 21:00:49 -0400, John Salerno
<jo******@NOSPAMgmail.com> declaimed the following in comp.lang.python:

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...

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]

BartlebyScrivener wrote:

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

seems to work fine.

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

[John Salerno]

BartlebyScrivener wrote:

But with the firewall off, everything
seems to work fine.

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.

[John Salerno]

Dennis Lee Bieber wrote:

On Sat, 13 May 2006 14:49:06 -0400, John Salerno
<jo******@NOSPAMgmail.com> declaimed the following in comp.lang.python:

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.

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.

[NoNickName]

Gerard Flanagan wrote:

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

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

HTH

Gerard

There's also the TechBookReport SQL tutorial - which is geared to MySQL
(and, shudder, Access). Take a look at
http://www.techbookreport.com/sql-tut1.html

[Steve Holden]

NoNickName wrote:

Gerard Flanagan wrote:

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

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

HTH

Gerard

There's also the TechBookReport SQL tutorial - which is geared to MySQL
(and, shudder, Access). Take a look at
http://www.techbookreport.com/sql-tut1.html

I maintain a few useful relational database links for my students at

http://www.holdenweb.com/students/database.html

They may not help, but they may. Also I just discovered a shareware
application called galaxql that uses a database to describe a galaxy
that the program alos visualizes. This cam help with SQL manipulations,
as the galactic visualisation changes as you update the data in the SQL
database (sqlite, I believe).

regards
Steve
--
Steve Holden [phone] [phone]
Holden Web LLC/Ltd http://www.holdenweb.com
Love me, love my blog http://holdenweb.blogspot.com
Recent Ramblings http://del.icio.us/steve.holden