0

When I use visual studio I can connect to an SQL server and explore the databases that the server holds.

Is there a way I can do this with python?

I have created a script that allows me to connect to the server

import adodbapi
conn = adodbapi.connect("PROVIDER=SQLOLEDB;Data Source=<location>;Database=<databaseName>; \
       trusted_connection=yes;")
cursor = conn.cursor()

<missing code here>

cursor.close()
conn.close()

This script runs fine, so i assume that the connection is generated fine.

I am hoping to create something like this

for table in sqlserver:
    for row in table:
        print row["name"]

or is it possible to explore the tables as a dictionary?

I am not asking anyone to write this code for me, but any help to allow me to do this would be appreciated, cheers

Thank you for the responses - I have found a solution to the question I asked.

To get a list of tables found replacing <missing code here> with the following code worked well.

tables = conn.get_table_names()
#prints all table names
for table in tables:
    print table

Once I pick a table (in this case called "Parts") I can then view the data in each column. I have used the .fetchone() function just pull one row.

sql = r'SELECT * FROM Parts'
cursor.execute(sql)
rows = cursor.fetchone()
rownames = cursor.columnNames

for rowname in rownames: # iterate through all rows
        print str(rowname) + "     " + str(rows[rowname])
Jens
  • 67,715
  • 15
  • 98
  • 113
CodeCupboard
  • 1,507
  • 3
  • 17
  • 26
  • 1
    Why the mysql tag if you refer to SQL-server – Jens Jun 02 '17 at 10:45
  • Possible duplicate of [Connecting to Microsoft SQL server using Python](https://stackoverflow.com/questions/33725862/connecting-to-microsoft-sql-server-using-python) – Mihai-Daniel Virna Jun 02 '17 at 10:47
  • 1
    Also, this is really a question about ADO itself, rather than Python or adodbapi. Once you've connected to the server via ADO, you can run any queries you like, including those to return the list of databases as explained here: https://blogs.technet.microsoft.com/heyscriptingguy/2006/09/14/how-can-i-get-a-list-of-all-the-databases-on-a-sql-server-computer/ – Daniel Roseman Jun 02 '17 at 10:50
  • @Jens Cheers I have removed this tag – CodeCupboard Jun 02 '17 at 15:16
  • @Milhai-Daniel Vima I did see that question before posting but it didnt contain enough detail to allow me explore the server to find the tables / columns i want to interact with which is the point of my question, i have no issue connecting. – CodeCupboard Jun 02 '17 at 15:19

1 Answers1

1

It sounds like you want to do something like this:

sql = 'SELECT * FROM table'
crsr.execute(sql)
rows = crsr.fetchone()
for row in rows: # iterate through all rows
    name = row[0] # replace 0 with row containing names

See the documentation here for more info: http://adodbapi.sourceforge.net/quick_reference.pdf

Sadie LaBounty
  • 379
  • 1
  • 5
  • 23
  • thank you for the answer, I get an error when I run that script though, do you happen to know what I doing wrong? Cheers – CodeCupboard Jun 02 '17 at 11:49
  • Just to make sure, I presume your table isn't actually called table? If not, you've obviously got to substitute the name of your table. The error says `Incorrect syntax near the keyword 'table'`, so maybe you forgot to change it? – Sadie LaBounty Jun 02 '17 at 11:53
  • I was using "table" and my table is not called table. Thank you – CodeCupboard Jun 02 '17 at 11:59
  • No worries. If that's solved your problem, you might mark this question as answered? – Sadie LaBounty Jun 02 '17 at 12:06