import pypyodbc
connection = pypyodbc.connect(r'Driver={SQL Server};Server=<Server_name>;Database=<Database_name>;Trusted_Connection=yes;')
cursor = connection.cursor()
SQLCommand = ("SELECT TABLE_SCHEMA ,TABLE_NAME FROM INFORMATION_SCHEMA.TABLES")
cursor.execute(SQLCommand)
results = cursor.fetchone()
while results:
print ("Table Schema " + str(results[0]) + " Table name is " + results[1])
results = cursor.fetchone()
connection.close()
Thursday, August 18, 2016
SQL server blogs I love...
There are lots of good blogs regarding Microsoft SQL server database engine and BI technologies...
Here are some i visit on regular basis
Brent Ozar
SQLBlog
Microsoft Data Platform Blogs
Paul randall
Pinal Dave (SQL authority)
SQL Server Customer Advisory Team (SQLCAT)
Jonathan Kehayias
Glen Berry
I like power BI, here is the list of power BI blogs\resources
Microsoft Power BI Blog
SQLBI (Marco Russo and Alberto Ferrari)
Chris Webb
Jen Underwood
Kasper de Jonge
Melissa Coates (SQL Chick)
Here are some i visit on regular basis
Brent Ozar
SQLBlog
Microsoft Data Platform Blogs
Paul randall
Pinal Dave (SQL authority)
SQL Server Customer Advisory Team (SQLCAT)
Jonathan Kehayias
Glen Berry
I like power BI, here is the list of power BI blogs\resources
Microsoft Power BI Blog
SQLBI (Marco Russo and Alberto Ferrari)
Chris Webb
Jen Underwood
Kasper de Jonge
Melissa Coates (SQL Chick)
Examples of SQL Server Full Text Index Queries
Examples of using SQL Server Full
text query
1) CONTAINS - Is a
predicate used in a WHERE clause to search columns containing character-based
data types for precise or fuzzy (less precise) matches to single words and
phrases, the proximity of words within a certain distance of one another, or
weighted matches.
--The following
example finds all records with word "hypersonic" in <column_name>
column
SELECT <column_name>
FROM dbo.<table_name>
WHERE CONTAINS(<column_name>, 'hypersonic');
GO
--The following
example finds all records with word "hypersonic" or
"liquid" in <column_name> column
SELECT <column_name>
FROM dbo.<table_name>
WHERE CONTAINS(<column_name>, ' "hypersonic" OR "liquid" ')
GO
--The following
example finds all records with word "hypersonic" AND
"liquid" in <column_name> column
SELECT id, <column_name>
FROM dbo.<table_name>
WHERE CONTAINS(<column_name>, ' "hypersonic" AND "liquid" ')
GO
--The following
example finds all records with at least one word starting with the prefix chain
word "hyper"
SELECT id, <column_name>
FROM dbo.<table_name>
WHERE CONTAINS(<column_name>, ' "hyper*" ')
GO
--The following
example finds all records with word starting with hyper OR word starting with
heal
SELECT id, <column_name>
FROM dbo.<table_name>
WHERE CONTAINS(<column_name>, ' "hyper*" OR "heal*" ')
GO
--The following
example finds all records with word starting with hyper and word starting with
heal
SELECT id, <column_name>
FROM dbo.<table_name>
WHERE CONTAINS(<column_name>, ' "hyper*" AND "heal*" ')
GO
--The following
example returns all records that have the word australian near the word health.
SELECT id, <column_name>
FROM dbo.<table_name>
WHERE CONTAINS(<column_name>, ' australian NEAR health ')
GO
--The following
example searches for all records with words of the form ride: riding, ridden, and
so on.
SELECT id, <column_name>
FROM dbo.<table_name>
WHERE CONTAINS(<column_name>, ' FORMSOF (INFLECTIONAL, ride) ');
GO
--The following
example searches for records containing the words performance, comfortable, or
smooth, and different weightings are given to each word.
SELECT id, <column_name>
FROM dbo.<table_name>
WHERE CONTAINS(<column_name>, 'ISABOUT (performance weight (.8),
comfortable
weight (.4), smooth weight (.2) )' );
GO
2) FREETEXT - Is a
predicate used in a WHERE clause to search columns containing character-based
data types for values that match the meaning and not just the exact wording of
the words in the search condition.
-- The
following example searches for all records in column <column_name>
containing the words related to "australian", "health", and
"safety".
SELECT id, <column_name>
FROM dbo.<table_name>
WHERE FREETEXT(<column_name>, 'australian health safety' );
GO
3) CONTAINSTABLE -
Returns a table of zero, one, or more rows for those columns containing
character-based data types for precise or fuzzy (less precise) matches to
single words and phrases, the proximity of words within a certain distance of
one another, or weighted matches. CONTAINSTABLE can only be referenced in the
FROM clause of a SELECT statement as if it were a regular table name. Queries
using CONTAINSTABLE specify contains-type full-text queries that return a
relevance ranking value (RANK) and full-text key (KEY) for each row. The
CONTAINSTABLE function uses the same search conditions as the CONTAINS
predicate.
-- The
following example searches for all records in column <column_name>
containing the words nitride and also gives rank
SELECT FT_TBL.id
,FT_TBL.<column_name>
,KEY_TBL.RANK as score
FROM dbo.<table_name> AS FT_TBL
INNER JOIN CONTAINSTABLE(dbo.<table_name>, <column_name>,
'nitride') AS KEY_TBL
ON FT_TBL.id = KEY_TBL.[KEY]
ORDER BY RANK desc
SELECT FT_TBL.id
,FT_TBL.<column_name>
,KEY_TBL.RANK as score
FROM dbo.<table_name> AS FT_TBL
INNER JOIN CONTAINSTABLE(dbo.<table_name>, <column_name>,
'ISABOUT
(performance weight (.8),
comfortable
weight (.4), smooth weight (.2) )') AS KEY_TBL
ON FT_TBL.id = KEY_TBL.[KEY]
ORDER BY RANK desc
4) FREETEXTTABLE -
Returns a table of zero, one, or more rows for those columns containing
character-based data types for values that match the meaning, but not the exact
wording, of the text in the specified freetext_string.
FREETEXTTABLE can only be referenced in the FROM clause of a SELECT statement
like a regular table name. Queries using FREETEXTTABLE specify freetext-type
full-text queries that return a relevance ranking value (RANK) and full-text
key (KEY) for each row.
-- The
following example searches for all records in column <column_name>
containing the words nitride and also gives rank
SELECT FT_TBL.id
,FT_TBL.<column_name>
,KEY_TBL.RANK as score
FROM dbo.<table_name> AS FT_TBL
INNER JOIN FREETEXTTABLE(dbo.<table_name>, <column_name>,
'nitrides of
boron') AS
KEY_TBL
ON FT_TBL.id = KEY_TBL.[KEY]
ORDER BY RANK desc
Python example to shred xml from a webservice and saving output to csv using Worldcat issn APi
import requests
import csv
from xml.dom.minidom import parse
import xml.dom.minidom
resultFile = open("output_jl.csv",'wb')
wr = csv.writer(resultFyle,delimiter=',')
wr.writerow(["issn","tag", "ind1", "ind2" ,"code" ,"value"])
#Replace XXX.. with your own key
url ="http://www.worldcat.org/webservices/catalog/content/issn/0000-0019?wskey=XXXXXXXXXXXXXXXXXX"
response = requests.get(url)
print response.content
DOMTree = xml.dom.minidom.parseString(response.content)
collection = DOMTree.documentElement
datafields = collection.getElementsByTagName("datafield")
for datafield in datafields:
subfields = datafield.getElementsByTagName("subfield")
for subfield in subfields:
issn = "0000-0019"
tag = datafield.getAttribute("tag").encode('utf-8')
ind1 = datafield.getAttribute("ind1").encode('utf-8')
ind2 = datafield.getAttribute("ind2").encode('utf-8')
code = subfield.getAttribute("code").encode('utf-8')
value = subfield.childNodes[0].data.encode('utf-8')
wr.writerow([issn,tag, ind1, ind2 ,code ,value])
#-------------------------------------------------------------------------------------------------------------#
XML:
<?xml version="1.0" encoding="UTF-8"?>
-<record xmlns="http://www.loc.gov/MARC21/slim">
<leader>00000cas a2200000 a 4500</leader>
<controlfield tag="001">2396688</controlfield>
<controlfield tag="008">760826d19492013dcuwr p 0 a0eng c</controlfield>
-<datafield tag="010" ind2=" " ind1=" ">
<subfield code="a"> 80643369 </subfield>
<subfield code="z"> 83641459 </subfield>
<subfield code="z"> 85644546 </subfield>
<subfield code="z"> 83641460 </subfield>
<subfield code="z"> 84644357 </subfield>
</datafield>
-<datafield tag="022" ind2=" " ind1="0">
<subfield code="a">0148-0227</subfield>
<subfield code="l">0148-0227</subfield>
<subfield code="z">0022-1406</subfield>
<subfield code="z">0196-6928</subfield>
<subfield code="z">0196-6936</subfield>
<subfield code="z">0885-3401</subfield>
<subfield code="z">8755-8556</subfield>
<subfield code="z">0196-2256</subfield>
<subfield code="z">0747-7309</subfield>
<subfield code="z">1934-8592</subfield>
<subfield code="z">1934-8843</subfield>
<subfield code="2">1</subfield>
</datafield>
-<datafield tag="222" ind2="0" ind1=" ">
<subfield code="a">Journal of geophysical research</subfield>
</datafield>
-<datafield tag="245" ind2="0" ind1="0">
<subfield code="a">Journal of geophysical research.</subfield>
</datafield>
-<datafield tag="246" ind2=" " ind1="1">
<subfield code="a">Journal of geophysical research.</subfield>
<subfield code="p">Space physics</subfield>
</datafield>
-<datafield tag="246" ind2=" " ind1="1">
<subfield code="a">Journal of geophysical research.</subfield>
<subfield code="p">Oceans and atmospheres</subfield>
</datafield>
-<datafield tag="246" ind2=" " ind1="1">
<subfield code="a">Journal of geophysical research.</subfield>
<subfield code="p">Solid earth and planets</subfield>
</datafield>
-<datafield tag="246" ind2=" " ind1="1">
<subfield code="a">Journal of geophysical research.</subfield>
<subfield code="p">Solid earth</subfield>
</datafield>
-<datafield tag="246" ind2=" " ind1="1">
<subfield code="a">Journal of geophysical research.</subfield>
<subfield code="p">Oceans</subfield>
</datafield>
-<datafield tag="246" ind2=" " ind1="1">
<subfield code="a">Journal of geophysical research.</subfield>
<subfield code="p">Atmospheres</subfield>
</datafield>
-<datafield tag="246" ind2=" " ind1="1">
<subfield code="a">Journal of geophysical research.</subfield>
<subfield code="p">Earth surface</subfield>
</datafield>
-<datafield tag="246" ind2=" " ind1="1">
<subfield code="a">Journal of geophysical research.</subfield>
<subfield code="p">Biogeosciences</subfield>
</datafield>
-<datafield tag="246" ind2=" " ind1="1">
<subfield code="i">Issues for 1974- also have title:</subfield>
<subfield code="a">JGR</subfield>
</datafield>
-<datafield tag="260" ind2=" " ind1=" ">
<subfield code="a">Richmond, Va. :</subfield>
<subfield code="b">William Byrd Press for John Hopkins Press,</subfield>
<subfield code="c">1949-2013</subfield>
</datafield>
-<datafield tag="260" ind2=" " ind1="3">
<subfield code="3">1959- :</subfield>
<subfield code="a">Washington, D.C. :</subfield>
<subfield code="b">American Geophysical Union</subfield>
</datafield>
-<datafield tag="300" ind2=" " ind1=" ">
<subfield code="a">v. :</subfield>
<subfield code="b">ill. ;</subfield>
<subfield code="c">30 cm.</subfield>
</datafield>
-<datafield tag="500" ind2=" " ind1=" ">
<subfield code="a">Title from cover.</subfield>
</datafield>
-<datafield tag="500" ind2=" " ind1=" ">
<subfield code="a">Each pt. is also available separately from publisher.</subfield>
</datafield>
-<datafield tag="650" ind2="0" ind1=" ">
<subfield code="a">Geomagnetism</subfield>
<subfield code="v">Periodicals.</subfield>
</datafield>
-<datafield tag="650" ind2="0" ind1=" ">
<subfield code="a">Geophysics</subfield>
<subfield code="v">Periodicals.</subfield>
</datafield>
-<datafield tag="650" ind2="0" ind1=" ">
<subfield code="a">Astrophysics</subfield>
<subfield code="v">Periodicals.</subfield>
</datafield>
-<datafield tag="650" ind2="7" ind1="1">
<subfield code="a">Geofysica.</subfield>
<subfield code="2">gtt</subfield>
</datafield>
-<datafield tag="650" ind2="7" ind1=" ">
<subfield code="a">Astrophysics.</subfield>
<subfield code="2">fast</subfield>
<subfield code="0">(OCoLC)fst00819797</subfield>
</datafield>
-<datafield tag="650" ind2="7" ind1=" ">
<subfield code="a">Geomagnetism.</subfield>
<subfield code="2">fast</subfield>
<subfield code="0">(OCoLC)fst00940796</subfield>
</datafield>
-<datafield tag="650" ind2="7" ind1=" ">
<subfield code="a">Geophysics.</subfield>
<subfield code="2">fast</subfield>
<subfield code="0">(OCoLC)fst00941002</subfield>
</datafield>
-<datafield tag="655" ind2="7" ind1=" ">
<subfield code="a">Periodicals.</subfield>
<subfield code="2">fast</subfield>
<subfield code="0">(OCoLC)fst01411641</subfield>
</datafield>
-<datafield tag="710" ind2=" " ind1="2">
<subfield code="a">American Geophysical Union.</subfield>
</datafield>
-<datafield tag="856" ind2="1" ind1="4">
<subfield code="3">Space physics section</subfield>
<subfield code="u">http://www.agu.org/journals/ja/</subfield>
</datafield>
-<datafield tag="856" ind2="1" ind1="4">
<subfield code="3">Solid earth section</subfield>
<subfield code="u">http://www.agu.org/journals/jb/</subfield>
</datafield>
-<datafield tag="856" ind2="1" ind1="4">
<subfield code="3">Oceans section</subfield>
<subfield code="u">http://www.agu.org/journals/jc/</subfield>
</datafield>
-<datafield tag="856" ind2="1" ind1="4">
<subfield code="3">Atmospheres section</subfield>
<subfield code="u">http://www.agu.org/journals/jd/</subfield>
</datafield>
-<datafield tag="856" ind2="1" ind1="4">
<subfield code="3">Planets section</subfield>
<subfield code="u">http://www.agu.org/journals/je/</subfield>
</datafield>
-<datafield tag="856" ind2="1" ind1="4">
<subfield code="3">Earth surface section</subfield>
<subfield code="u">http://www.agu.org/journals/jf/</subfield>
</datafield>
-<datafield tag="856" ind2="1" ind1="4">
<subfield code="3">Biogeosciences section</subfield>
<subfield code="u">http://www.agu.org/journals/jg/</subfield>
</datafield>
-<datafield tag="856" ind2="1" ind1="4">
<subfield code="u">http://www.agu.org/journals/jgr/1950.shtml</subfield>
</datafield>
-<datafield tag="856" ind2="1" ind1="4">
<subfield code="3">1949-1977</subfield>
<subfield code="u">http://wileyonlinelibrary.com/journal/JGR</subfield>
<subfield code="z">Issues of earlier title also at website</subfield>
</datafield>
</record>
import csv
from xml.dom.minidom import parse
import xml.dom.minidom
resultFile = open("output_jl.csv",'wb')
wr = csv.writer(resultFyle,delimiter=',')
wr.writerow(["issn","tag", "ind1", "ind2" ,"code" ,"value"])
#Replace XXX.. with your own key
url ="http://www.worldcat.org/webservices/catalog/content/issn/0000-0019?wskey=XXXXXXXXXXXXXXXXXX"
response = requests.get(url)
print response.content
DOMTree = xml.dom.minidom.parseString(response.content)
collection = DOMTree.documentElement
datafields = collection.getElementsByTagName("datafield")
for datafield in datafields:
subfields = datafield.getElementsByTagName("subfield")
for subfield in subfields:
issn = "0000-0019"
tag = datafield.getAttribute("tag").encode('utf-8')
ind1 = datafield.getAttribute("ind1").encode('utf-8')
ind2 = datafield.getAttribute("ind2").encode('utf-8')
code = subfield.getAttribute("code").encode('utf-8')
value = subfield.childNodes[0].data.encode('utf-8')
wr.writerow([issn,tag, ind1, ind2 ,code ,value])
#-------------------------------------------------------------------------------------------------------------#
XML:
<?xml version="1.0" encoding="UTF-8"?>
-<record xmlns="http://www.loc.gov/MARC21/slim">
<leader>00000cas a2200000 a 4500</leader>
<controlfield tag="001">2396688</controlfield>
<controlfield tag="008">760826d19492013dcuwr p 0 a0eng c</controlfield>
-<datafield tag="010" ind2=" " ind1=" ">
<subfield code="a"> 80643369 </subfield>
<subfield code="z"> 83641459 </subfield>
<subfield code="z"> 85644546 </subfield>
<subfield code="z"> 83641460 </subfield>
<subfield code="z"> 84644357 </subfield>
</datafield>
-<datafield tag="022" ind2=" " ind1="0">
<subfield code="a">0148-0227</subfield>
<subfield code="l">0148-0227</subfield>
<subfield code="z">0022-1406</subfield>
<subfield code="z">0196-6928</subfield>
<subfield code="z">0196-6936</subfield>
<subfield code="z">0885-3401</subfield>
<subfield code="z">8755-8556</subfield>
<subfield code="z">0196-2256</subfield>
<subfield code="z">0747-7309</subfield>
<subfield code="z">1934-8592</subfield>
<subfield code="z">1934-8843</subfield>
<subfield code="2">1</subfield>
</datafield>
-<datafield tag="222" ind2="0" ind1=" ">
<subfield code="a">Journal of geophysical research</subfield>
</datafield>
-<datafield tag="245" ind2="0" ind1="0">
<subfield code="a">Journal of geophysical research.</subfield>
</datafield>
-<datafield tag="246" ind2=" " ind1="1">
<subfield code="a">Journal of geophysical research.</subfield>
<subfield code="p">Space physics</subfield>
</datafield>
-<datafield tag="246" ind2=" " ind1="1">
<subfield code="a">Journal of geophysical research.</subfield>
<subfield code="p">Oceans and atmospheres</subfield>
</datafield>
-<datafield tag="246" ind2=" " ind1="1">
<subfield code="a">Journal of geophysical research.</subfield>
<subfield code="p">Solid earth and planets</subfield>
</datafield>
-<datafield tag="246" ind2=" " ind1="1">
<subfield code="a">Journal of geophysical research.</subfield>
<subfield code="p">Solid earth</subfield>
</datafield>
-<datafield tag="246" ind2=" " ind1="1">
<subfield code="a">Journal of geophysical research.</subfield>
<subfield code="p">Oceans</subfield>
</datafield>
-<datafield tag="246" ind2=" " ind1="1">
<subfield code="a">Journal of geophysical research.</subfield>
<subfield code="p">Atmospheres</subfield>
</datafield>
-<datafield tag="246" ind2=" " ind1="1">
<subfield code="a">Journal of geophysical research.</subfield>
<subfield code="p">Earth surface</subfield>
</datafield>
-<datafield tag="246" ind2=" " ind1="1">
<subfield code="a">Journal of geophysical research.</subfield>
<subfield code="p">Biogeosciences</subfield>
</datafield>
-<datafield tag="246" ind2=" " ind1="1">
<subfield code="i">Issues for 1974- also have title:</subfield>
<subfield code="a">JGR</subfield>
</datafield>
-<datafield tag="260" ind2=" " ind1=" ">
<subfield code="a">Richmond, Va. :</subfield>
<subfield code="b">William Byrd Press for John Hopkins Press,</subfield>
<subfield code="c">1949-2013</subfield>
</datafield>
-<datafield tag="260" ind2=" " ind1="3">
<subfield code="3">1959- :</subfield>
<subfield code="a">Washington, D.C. :</subfield>
<subfield code="b">American Geophysical Union</subfield>
</datafield>
-<datafield tag="300" ind2=" " ind1=" ">
<subfield code="a">v. :</subfield>
<subfield code="b">ill. ;</subfield>
<subfield code="c">30 cm.</subfield>
</datafield>
-<datafield tag="500" ind2=" " ind1=" ">
<subfield code="a">Title from cover.</subfield>
</datafield>
-<datafield tag="500" ind2=" " ind1=" ">
<subfield code="a">Each pt. is also available separately from publisher.</subfield>
</datafield>
-<datafield tag="650" ind2="0" ind1=" ">
<subfield code="a">Geomagnetism</subfield>
<subfield code="v">Periodicals.</subfield>
</datafield>
-<datafield tag="650" ind2="0" ind1=" ">
<subfield code="a">Geophysics</subfield>
<subfield code="v">Periodicals.</subfield>
</datafield>
-<datafield tag="650" ind2="0" ind1=" ">
<subfield code="a">Astrophysics</subfield>
<subfield code="v">Periodicals.</subfield>
</datafield>
-<datafield tag="650" ind2="7" ind1="1">
<subfield code="a">Geofysica.</subfield>
<subfield code="2">gtt</subfield>
</datafield>
-<datafield tag="650" ind2="7" ind1=" ">
<subfield code="a">Astrophysics.</subfield>
<subfield code="2">fast</subfield>
<subfield code="0">(OCoLC)fst00819797</subfield>
</datafield>
-<datafield tag="650" ind2="7" ind1=" ">
<subfield code="a">Geomagnetism.</subfield>
<subfield code="2">fast</subfield>
<subfield code="0">(OCoLC)fst00940796</subfield>
</datafield>
-<datafield tag="650" ind2="7" ind1=" ">
<subfield code="a">Geophysics.</subfield>
<subfield code="2">fast</subfield>
<subfield code="0">(OCoLC)fst00941002</subfield>
</datafield>
-<datafield tag="655" ind2="7" ind1=" ">
<subfield code="a">Periodicals.</subfield>
<subfield code="2">fast</subfield>
<subfield code="0">(OCoLC)fst01411641</subfield>
</datafield>
-<datafield tag="710" ind2=" " ind1="2">
<subfield code="a">American Geophysical Union.</subfield>
</datafield>
-<datafield tag="856" ind2="1" ind1="4">
<subfield code="3">Space physics section</subfield>
<subfield code="u">http://www.agu.org/journals/ja/</subfield>
</datafield>
-<datafield tag="856" ind2="1" ind1="4">
<subfield code="3">Solid earth section</subfield>
<subfield code="u">http://www.agu.org/journals/jb/</subfield>
</datafield>
-<datafield tag="856" ind2="1" ind1="4">
<subfield code="3">Oceans section</subfield>
<subfield code="u">http://www.agu.org/journals/jc/</subfield>
</datafield>
-<datafield tag="856" ind2="1" ind1="4">
<subfield code="3">Atmospheres section</subfield>
<subfield code="u">http://www.agu.org/journals/jd/</subfield>
</datafield>
-<datafield tag="856" ind2="1" ind1="4">
<subfield code="3">Planets section</subfield>
<subfield code="u">http://www.agu.org/journals/je/</subfield>
</datafield>
-<datafield tag="856" ind2="1" ind1="4">
<subfield code="3">Earth surface section</subfield>
<subfield code="u">http://www.agu.org/journals/jf/</subfield>
</datafield>
-<datafield tag="856" ind2="1" ind1="4">
<subfield code="3">Biogeosciences section</subfield>
<subfield code="u">http://www.agu.org/journals/jg/</subfield>
</datafield>
-<datafield tag="856" ind2="1" ind1="4">
<subfield code="u">http://www.agu.org/journals/jgr/1950.shtml</subfield>
</datafield>
-<datafield tag="856" ind2="1" ind1="4">
<subfield code="3">1949-1977</subfield>
<subfield code="u">http://wileyonlinelibrary.com/journal/JGR</subfield>
<subfield code="z">Issues of earlier title also at website</subfield>
</datafield>
</record>
Subscribe to:
Comments (Atom)