HomeData ScienceArchive by Category "SQL"

sql list all table and schema names

SELECT *
FROM INFORMATION_SCHEMA.TABLES
order by TABLE_TYPE, TABLE_SCHEMA

python reads data from SQL directly; python executes sql query


import pandas as pd
import pyodbc

sqlServerInstance = ".\JINGSQL"
dbname = 'db'
schema = 'FTP'
connectionStr = "DRIVER={SQL Server};SERVER=" + sqlServerInstance + ";DATABASE=" + dbname + ";Trusted Connection = Yes" + ", autocommit=True"
conn = pyodbc.connect(connectionStr)
cursor = conn.cursor()

sql_q = """ select top 10 * from [DB].[SCHEMA].[TableName]"
df_concordance = pd. read_sql_query(sql_q,conn)

Now records are saved in the dataframe df_concordance.

SQL Server “Saving changes is not permitted”

I tried to edit the column types, and save it. But received the error message:

Saving changes is not permitted. The change you have made requires the following table to be dropped and re-created. You have either made changes to a table that can't be recreated or enabled the option prevent saving changes that require the table to be re-created.

To solve the problem, you can go to Tools–>options–>designers–>uncheck “Prevent Saving changes that require table re-creation”.

SQL unpivot example

SELECT [county] [CountyName],[Year],[FIPS], MillionGallon, Category
FROM [USGS].[APP].[CountyWater]
unpivot
(MillionGallon for Category in
([Aquaculture GroundWater] , [Aquaculture Surface Water],[Aquaculture Total],
[Domestic GroundWater],[Domestic Surface Water],[Domestic Total],
[Irrigation GroundWater] , [Irrigation Surface Water],[Irrigation Total],
[Industrial GroundWater],[Industrial Surface Water],[Industrial Total],
[Livestock GroundWater],[Livestock Surface Water],[Livestock Total],
[Mining GroundWater],[Mining Surface Water],[Mining Total],
[Thermoelectric GroundWater],[Thermoelectric Surface Water],[Thermoelectric Total],
[PublicSupply GroundWater],[PublicSupply Surface Water],[PublicSupply Total]
)
)as TableUnpivot

SQL delete duplicated rows


WITH CTE AS(
SELECT DOMAINCAT_DESC, SHORT_DESC, DOMAIN_DESC, AGG_LEVEL_DESC,
RN = ROW_NUMBER()OVER(PARTITION BY AGG_LEVEL_DESC ORDER BY AGG_LEVEL_DESC)
FROM [NASSQUICKSTATS].[NASSQSFTP].[NASS_QuickStats_Economicscheck]
)
DELETE FROM CTE WHERE RN > 1

Python: Load Data to SQL

sqlInsert = " bulk insert " + "[" + dbname + "].[" + schema + "].[" + sqltablename + "] from " + "'" + workingDir + NassNames + '.txt' "'" + """ WITH(FIELDTERMINATOR='|',ROWTERMINATOR='\r\n')"""
# print sqlInsert
cursor.execute(sqlInsert)
cursor.commit()

bulk insert [.].[.].[.] from 'C:\Users\.\economics.txt' WITH(FIELDTERMINATOR='|' ,lastrow=1)

sql load data

copy data from one table to the other:
insert into [NASSQUICKSTATS].[NASSQSFTP].[NASS_QuickStats_Census2002_2012]
select * from [NASSQUICKSTATS].[NASSQSFTP].[NASS_QuickStats_Census2002]

use bulk insert if you load data from csv to sql tables