[Tutor] How can i dump the DB data into a text file in CSV format
Aztech Guy
aztech1200@yahoo.com
Tue Dec 31 08:07:03 2002
--0-861122756-1041340017=:22292
Content-Type: text/plain; charset=us-ascii
Hi,
Here's an overview ...
[ Disclaimer: I haven't tried Python+DBI yet; but I've read about it; and it's similar to Perl+DBI+DBD, Java+JDBC, and C+ODBC in concept, the last two of which I've used a lot. And I've read enough of Perl+DBI+DBD docs, though, to realize that its similar in concept as I said above. My guess is that Python+DBI(+DBD?) should be roughly on the same lines, though of course with a Pythonic flavour. Don't take what I say as absolutely accurate, though - you'll have to do some research on the Net to find the exact equivalents in Python.]
- Get (if not already present on your system) the DBI+DBD (not sure of the exact names in Python) modules from the Net. (Try the Python site www.python.org, the Vaults of Parnassus, Google, etc. to find out where to get it from).
- DBI (in Perl, at least) stands for Data Base Interface, and DBD stands for Data Base Driver.
The DBI is a database-indepent layer. Your Perl/Python program talks to it.
This is similar to the Driver Manager in ODBC.
The DBI in turn talks to the DBD layer, which is specific to the database you are using. There should be different DBD's for different databases. So you'll have to get one for Oracle - may need to check if it works with your particular Oracle version.
The DBD is similar to the database-specific database driver in ODBC - you need a separate ODBC driver for each database that you want to connect to.
Once you have got all the above software, your program should be written something on these lines :
- Open a connection to the database from Python program.
You'll have to specify the database as a "URL" (like a JDBC URL in concept, though not maybe in syntax), which will include the database name, user name and password, maybe the machine on which it is (the host name). The connect call will return a 'handle' of some sort which you will use for all further operations on that database, such as executing statements, getting results, etc.
- Write your SQL statement to get the data you want from the table you want.
(You'll need to know SQL here - if you don't, I can't help you with that - it's too long a topic to explain right now - try Googling for SQL tutorials and Database tutorials to understand the basics of both - it's not very difficult - a super-short explanation is that database hold many tables, tables are like spreadsheets (contain rows and columns, and SQL is a way of reading rows and / or columns, and any combination of them, and also of updating (adding/modifying/deleting) the same. About.com is also a good site for learning about this stuff)
- Once you've written your SQL statement, you will have to pass it to the DBD using the appropriate syntax. After this, if the SQL statement executed successfully, you can use other Py/DBD statements to read the data returned by the statement, row by row, with each row containing the columns you've specified. You may also need to use statements to access each column of each row). All this will have to be done in (probably) two nested loops - the outer loop iterates over rows, and the inner one over the columns of the current row.
- Next, inside the inner loop, you now have the values of each row+column, i.e. a "cell" as a Python variable. Write it out to a text file in CSV format. (You should have opened a text file - to write to - before the outer loop). CSV stands for Comma Separated Values - its a simple format - there is a comma between any two consecutive values, and (optionally - if the string contains white space), double quotes around strings - its probably to always use double quotes.
- So, in the inner loop, for each numeric variable, write it out as is; for each string variable, you may have to prefix and suffix it with double quotes and then write it out.
- At the end of each inner loop, print a newline.
After the outer loop, close the text file.
If all went well, you should have the output you need.
Sample pseudocode:
db_handle = connect( <Py-DBD-URL> )
stmt = exec_stmt(db_handle, "<your SQL stmt>")
results = stmt.get_resultSet
while results.hasMoreRows()
row = results.getNextRow()
while row.hasMoreColumns()
col = row.getNextColumn()
txtfile.write( col ) # formatted as necessary
end while col
print a newline
end while row
close textfile
close stmt
close db_handle
Hope this helps
Az.
.
shobhan <schalla@vasoftware.com> wrote:Hi Pythoners,
Can anyone tell me how to connect to Oracle DB using Python and dump the data from a table called "logs" into a text file in CSV format..?
The text file thus created should be of the format (2002-12-30-logs.txt).
It would be fine if anyone can show a sample script.
Thanks in advance
Schalla
---------------------------------
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now
--0-861122756-1041340017=:22292
Content-Type: text/html; charset=us-ascii
<P>Hi,
<P>Here's an overview ...
<P>[ Disclaimer: I haven't tried Python+DBI yet; but I've read about it; and it's similar to Perl+DBI+DBD, Java+JDBC, and C+ODBC in concept, the last two of which I've used a lot. And I've read enough of Perl+DBI+DBD docs, though, to realize that its similar in concept as I said above. My guess is that Python+DBI(+DBD?) should be roughly on the same lines, though of course with a Pythonic flavour. Don't take what I say as absolutely accurate, though - you'll have to do some research on the Net to find the exact equivalents in Python.]
<P>- Get (if not already present on your system) the DBI+DBD (not sure of the exact names in Python) modules from the Net. (Try the Python site <A href="http://www.python.org">www.python.org</A>, the Vaults of Parnassus, Google, etc. to find out where to get it from).
<P>- DBI (in Perl, at least) stands for Data Base Interface, and DBD stands for Data Base Driver.
<P>The DBI is a database-indepent layer. Your Perl/Python program talks to it.
<P>This is similar to the Driver Manager in ODBC.
<P>The DBI in turn talks to the DBD layer, which is specific to the database you are using. There should be different DBD's for different databases. So you'll have to get one for Oracle - may need to check if it works with your particular Oracle version.
<P>The DBD is similar to the database-specific database driver in ODBC - you need a separate ODBC driver for each database that you want to connect to.
<P>Once you have got all the above software, your program should be written something on these lines :
<P>- Open a connection to the database from Python program.
<P>You'll have to specify the database as a "URL" (like a JDBC URL in concept, though not maybe in syntax), which will include the database name, user name and password, maybe the machine on which it is (the host name). The connect call will return a 'handle' of some sort which you will use for all further operations on that database, such as executing statements, getting results, etc.
<P>- Write your SQL statement to get the data you want from the table you want.
<P>(You'll need to know SQL here - if you don't, I can't help you with that - it's too long a topic to explain right now - try Googling for SQL tutorials and Database tutorials to understand the basics of both - it's not very difficult - a super-short explanation is that database hold many tables, tables are like spreadsheets (contain rows and columns, and SQL is a way of reading rows and / or columns, and any combination of them, and also of updating (adding/modifying/deleting) the same. About.com is also a good site for learning about this stuff)
<P>- Once you've written your SQL statement, you will have to pass it to the DBD using the appropriate syntax. After this, if the SQL statement executed successfully, you can use other Py/DBD statements to read the data returned by the statement, row by row, with each row containing the columns you've specified. You may also need to use statements to access each column of each row). All this will have to be done in (probably) two nested loops - the outer loop iterates over rows, and the inner one over the columns of the current row.
<P>- Next, inside the inner loop, you now have the values of each row+column, i.e. a "cell" as a Python variable. Write it out to a text file in CSV format. (You should have opened a text file - to write to - before the outer loop). CSV stands for Comma Separated Values - its a simple format - there is a comma between any two consecutive values, and (optionally - if the string contains white space), double quotes around strings - its probably to always use double quotes.
<P>- So, in the inner loop, for each numeric variable, write it out as is; for each string variable, you may have to prefix and suffix it with double quotes and then write it out.
<P>- At the end of each inner loop, print a newline.
<P>After the outer loop, close the text file.
<P>If all went well, you should have the output you need.
<P>Sample pseudocode:
<P>db_handle = connect( <Py-DBD-URL> )
<P>stmt = exec_stmt(db_handle, "<your SQL stmt>")
<P>results = stmt.get_resultSet
<P>while results.hasMoreRows()
<P> row = results.getNextRow()
<P> while row.hasMoreColumns()
<P> col = row.getNextColumn()
<P> txtfile.write( col ) # formatted as necessary
<P> end while col
<P> print a newline
<P> end while row
<P>close textfile
<P>close stmt
<P>close db_handle
<P>Hope this helps
<P>Az.
<P>
<P>
<P>.
<P> <B><I>shobhan <schalla@vasoftware.com></I></B> wrote:
<BLOCKQUOTE style="PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #1010ff 2px solid"><FONT face="Helvetica, foo, sans-serif" size=-1>Hi Pythoners,<BR><BR>Can anyone tell me how to connect to Oracle DB using Python and dump the data from a table called "logs" into a text file in CSV format..?<BR>The text file thus created should be of the format (2002-12-30-logs.txt).<BR>It would be fine if anyone can show a sample script.<BR><BR>Thanks in advance<BR>Schalla</FONT><BR></BLOCKQUOTE><p><br><hr size=1>Do you Yahoo!?<br>
<a href="http://rd.yahoo.com/mail/mailsig/*http://mailplus.yahoo.com">Yahoo! Mail Plus</a> - Powerful. Affordable. <a href="http://rd.yahoo.com/mail/mailsig/*http://mailplus.yahoo.com">Sign up now</a>
--0-861122756-1041340017=:22292--