我想在sql中插入一些数据，但遇到了麻烦，因为有很多列，所以我必须在VALUE之后写很多参数。我有一个表，其中包含json文件中的所有属性和一个player_id，我将其添加到中
#Gamelogs for players and Teams
import requests
import json
import psycopg2
# Connect to your postgres DB
conn = psycopg2.connect("dbname=NBA user=postgres password=********")
# Open a cursor to perform database operations
cur = conn.cursor()
cur.execute('CREATE TABLE player_logs("player_id" int,"GameId" int,"Date" int,"Team" VARCHAR(10),"Opponent" VARCHAR(10),"Minutes" int,"Arc3Assists" int,"Arc3FGA" int,"Arc3Frequency" int,"AssistPoints" int,"Assists" int,"AtRimAssists" int,"AtRimFG3AFrequency" int,"Avg2ptShotDistance" int,"Avg3ptShotDistance" int,"BadPassOutOfBoundsTurnovers" int,"BadPassSteals" int,"BadPassTurnovers" int,"Corner3FGA" int,"Corner3Frequency" int,"DeadBallTurnovers" int,"DefArc3ReboundPct" int,"DefFGReboundPct" int,"DefPoss" int,"DefRebounds" int,"DefThreePtReboundPct" int,"DefThreePtRebounds" int,"EfgPct" int,"FG2A" int,"FG2M" int,"FG3A" int,"FG3APct" int,"FTA" int,"Fg2Pct" int,"FirstChancePoints" int,"Fouls" int,"FoulsDrawn" int,"FtPoints" int,"LiveBallTurnoverPct" int,"LiveBallTurnovers" int,"LongMidRangeAccuracy" int,"LongMidRangeAssists" int,"LongMidRangeFGA" int,"LongMidRangeFGM" int,"LongMidRangeFrequency" int,"Loose Ball Fouls" int,"LostBallTurnovers" int,"NonHeaveArc3FGA" int,"OffFGReboundPct" int,"OffPoss" int,"OffRebounds" int,"OffShortMidRangeReboundPct" int,"OffTwoPtReboundPct" int,"OffTwoPtRebounds" int,"OnDefRtg" int,"OnOffRtg" int,"PenaltyArc3FGA" int,"PenaltyArc3Frequency" int,"PenaltyDefPoss" int,"PenaltyEfgPct" int,"PenaltyFG2A" int,"PenaltyFG2M" int,"PenaltyFG3A" int,"PenaltyFg2Pct" int,"PenaltyOffPoss" int,"PenaltyOffPossExcludingTakeFouls" int,"PenaltyOffPossPct" int,"PenaltyPoints" int,"PenaltyPointsExcludingTakeFouls" int,"PenaltyPointsPct" int,"PenaltyShotQualityAvg" int,"PenaltyTsPct" int,"PenaltyTurnovers" int,"Period2Fouls2Minutes" int,"Period3Fouls3Minutes" int,"PlusMinus" int,"Points" int,"PtsUnassisted2s" int,"Rebounds" int,"SecondChanceOffPoss" int,"SelfOReb" int,"SelfORebPct" int,"ShootingFouls" int,"ShootingFoulsDrawnPct" int,"ShortMidRangeAccuracy" int,"ShortMidRangeAssists" int,"ShortMidRangeFGA" int,"ShortMidRangeFGM" int,"ShortMidRangeFrequency" int,"ShortMidRangeOffReboundedPct" int,"ShotQualityAvg" int,"Steals" int,"ThreePtAssists" int,"TotalPoss" int,"TsPct" int,"Turnovers" int,"TwoPtAssists" int,"TwoPtShootingFoulsDrawn" int,"TwoPtShootingFoulsDrawnPct" int,"UnblockedLongMidRangeAccuracy" int,"UnblockedShortMidRangeAccuracy" int,"Usage" int,"Arc3Accuracy" int,"Arc3FGM" int,"Arc3PctAssisted" int,"Assisted2sPct" int,"Assisted3sPct" int,"AtRimAccuracy" int,"AtRimFGA" int,"AtRimFGM" int,"AtRimFrequency" int,"AtRimOffReboundedPct" int,"AtRimPctBlocked" int,"Blocked2s" int,"BlockedShortMidRange" int,"Blocks" int,"BlocksRecoveredPct" int,"Corner3Assists" int,"DefAtRimReboundPct" int,"DefLongMidRangeReboundPct" int,"DefShortMidRangeReboundPct" int,"DefTwoPtReboundPct" int,"DefTwoPtRebounds" int,"FG2APctBlocked" int,"FG3M" int,"Fg2aBlocked" int,"Fg3Pct" int,"LongMidRangeOffReboundedPct" int,"LostBallSteals" int,"NonHeaveArc3Accuracy" int,"NonHeaveArc3FGM" int,"NonHeaveFg3Pct" int,"NonPutbacksAssisted2sPct" int,"NonShootingFoulsDrawn" int,"NonShootingPenaltyNonTakeFoulsDrawn" int,"OffLongMidRangeReboundPct" int,"Offensive Fouls Drawn" int,"PenaltyArc3Accuracy" int,"PenaltyArc3FGM" int,"PenaltyAtRimAccuracy" int,"PenaltyAtRimFGA" int,"PenaltyAtRimFGM" int,"PenaltyAtRimFrequency" int,"PenaltyFG3M" int,"PenaltyFg3Pct" int,"PenaltyFtPoints" int,"PtsAssisted2s" int,"PtsAssisted3s" int,"PtsPutbacks" int,"PtsUnassisted3s" int,"RecoveredBlocks" int,"SecondChanceArc3FGA" int,"SecondChanceArc3Frequency" int,"SecondChanceEfgPct" int,"SecondChanceFG2A" int,"SecondChanceFG2M" int,"SecondChanceFG3A" int,"SecondChanceFg2Pct" int,"SecondChancePoints" int,"SecondChancePointsPct" int,"SecondChanceShotQualityAvg" int,"SecondChanceTsPct" int,"ShortMidRangePctAssisted" int,"ShortMidRangePctBlocked" int,"ThreePtShootingFoulsDrawn" int,"ThreePtShootingFoulsDrawnPct" int,"UnblockedArc3Accuracy" int,"UnblockedAtRimAccuracy" int,"OffArc3ReboundPct" int,"OffThreePtReboundPct" int,"OffThreePtRebounds" int,"Offensive Fouls" int,"Corner3Accuracy" int,"Corner3FGM" int,"ThreePtOffReboundedPct" int,"UnblockedCorner3Accuracy" int,"DefFTReboundPct" int,"FTDefRebounds" int,"Technical Free Throw Trips" int,"BlockedAtRim" int,"LostBallOutOfBoundsTurnovers" int,"OffAtRimReboundPct" int,"BlockedLongMidRange" int,"Charge Fouls Drawn" int,"LongMidRangePctAssisted" int,"NonShootingPenaltyNonTakeFouls" int,"SecondChanceTurnovers" int,"Travels" int,"SecondChanceAtRimFGA" int,"SecondChanceAtRimFrequency" int,"Clear Path Fouls" int,"DefCorner3ReboundPct" int,"HeaveAttempts" int,"LongMidRangePctBlocked" int,"2pt And 1 Free Throw Trips" int,"AtRimPctAssisted" int,"Period3Fouls4Minutes" int,"Period4Fouls4Minutes" int,"Charge Fouls" int,"Loose Ball Fouls Drawn" int,"PeriodOTFouls4Minutes" int,"SecondChanceAtRimAccuracy" int,"SecondChanceAtRimFGM" int,"PenaltyCorner3FGA" int,"PenaltyCorner3Frequency" int,"Corner3PctAssisted" int,"SecondChanceFtPoints" int,"OffCorner3ReboundPct" int,"SecondChanceArc3Accuracy" int,"SecondChanceArc3FGM" int,"SecondChanceFG3M" int,"SecondChanceFg3Pct" int,"3pt And 1 Free Throw Trips" int,"Defensive 3 Seconds Violations" int,"Period4Fouls5Minutes" int,"StepOutOfBoundsTurnovers" int,"Period1Fouls2Minutes"int)')
x = 'https://api.pbpstats.com/get-all-players-for-league/nba'
headers = {'user-agent': 'Chrome/88.0.4324.190'}
jsonData1 = requests.get(x, headers=headers).json() # Player id and name
EntityId = json.loads(json.dumps(jsonData1)[12:-1])
SeasonType = {'R':'Regular+Season','P':'Playoff+Season','A':'All'}
EntityType = {'P':'Player','T':'Team'}
Season = {
'2008-09',
'2009-10',
'2010-11',
'2011-12',
'2012-13',
'2013-14',
'2014-15',
'2015-16',
'2016-17',
'2017-18',
'2018-19',
'2019-20',
'2020-21'
}
def log (S:Season,ST:SeasonType,EI:EntityId,ET:EntityType):
url = 'https://api.pbpstats.com/get-game-logs/nba'
payload = {
'Season': S,
'SeasonType': ST,
'EntityId': EI,
'EntityType': ET
}
r = requests.get(url, headers=headers, params=payload).json()
if r == {'error': 'no results'} :
return()
else :
for c in r['multi_row_table_data']:
j = {'Player_id':EI}
c.update(j)
cur.execute('INSERT INTO player_log (Player_id,GameId,Date,Team,Opponent,Minutes,Arc3Assists,Arc3FGA,Arc3Frequency,AssistPoints,Assists,AtRimAssists,AtRimFG3AFrequency,Avg2ptShotDistance,Avg3ptShotDistance,BadPassOutOfBoundsTurnovers,BadPassSteals,BadPassTurnovers,Corner3FGA,Corner3Frequency,DeadBallTurnovers,DefArc3ReboundPct,DefFGReboundPct,DefPoss,DefRebounds,DefThreePtReboundPct,DefThreePtRebounds,EfgPct,FG2A,FG2M,FG3A,FG3APct,FTA,Fg2Pct,FirstChancePoints,Fouls,FoulsDrawn,FtPoints,LiveBallTurnoverPct,LiveBallTurnovers,LongMidRangeAccuracy,LongMidRangeAssists,LongMidRangeFGA,LongMidRangeFGM,LongMidRangeFrequency,Loose_Ball_Fouls,LostBallTurnovers,NonHeaveArc3FGA,OffFGReboundPct,OffPoss,OffRebounds,OffShortMidRangeReboundPct,OffTwoPtReboundPct,OffTwoPtRebounds,OnDefRtg,OnOffRtg,PenaltyArc3FGA,PenaltyArc3Frequency,PenaltyDefPoss,PenaltyEfgPct,PenaltyFG2A,PenaltyFG2M,PenaltyFG3A,PenaltyFg2Pct,PenaltyOffPoss,PenaltyOffPossExcludingTakeFouls,PenaltyOffPossPct,PenaltyPoints,PenaltyPointsExcludingTakeFouls,PenaltyPointsPct,PenaltyShotQualityAvg,PenaltyTsPct,PenaltyTurnovers,Period2Fouls2Minutes,Period3Fouls3Minutes,PlusMinus,Points,PtsUnassisted2s,Rebounds,SecondChanceOffPoss,SelfOReb,SelfORebPct,ShootingFouls,ShootingFoulsDrawnPct,ShortMidRangeAccuracy,ShortMidRangeAssists,ShortMidRangeFGA,ShortMidRangeFGM,ShortMidRangeFrequency,ShortMidRangeOffReboundedPct,ShotQualityAvg,Steals,ThreePtAssists,TotalPoss,TsPct,Turnovers,TwoPtAssists,TwoPtShootingFoulsDrawn,TwoPtShootingFoulsDrawnPct,UnblockedLongMidRangeAccuracy,UnblockedShortMidRangeAccuracy,Usage,Arc3Accuracy,Arc3FGM,Arc3PctAssisted,Assisted2sPct,Assisted3sPct,AtRimAccuracy,AtRimFGA,AtRimFGM,AtRimFrequency,AtRimOffReboundedPct,AtRimPctBlocked,Blocked2s,BlockedShortMidRange,Blocks,BlocksRecoveredPct,Corner3Assists,DefAtRimReboundPct,DefLongMidRangeReboundPct,DefShortMidRangeReboundPct,DefTwoPtReboundPct,DefTwoPtRebounds,FG2APctBlocked,FG3M,Fg2aBlocked,Fg3Pct,LongMidRangeOffReboundedPct,LostBallSteals,NonHeaveArc3Accuracy,NonHeaveArc3FGM,NonHeaveFg3Pct,NonPutbacksAssisted2sPct,NonShootingFoulsDrawn,NonShootingPenaltyNonTakeFoulsDrawn,OffLongMidRangeReboundPct,Offensive_Fouls_Drawn,PenaltyArc3Accuracy,PenaltyArc3FGM,PenaltyAtRimAccuracy,PenaltyAtRimFGA,PenaltyAtRimFGM,PenaltyAtRimFrequency,PenaltyFG3M,PenaltyFg3Pct,PenaltyFtPoints,PtsAssisted2s,PtsAssisted3s,PtsPutbacks,PtsUnassisted3s,RecoveredBlocks,SecondChanceArc3FGA,SecondChanceArc3Frequency,SecondChanceEfgPct,SecondChanceFG2A,SecondChanceFG2M,SecondChanceFG3A,SecondChanceFg2Pct,SecondChancePoints,SecondChancePointsPct,SecondChanceShotQualityAvg,SecondChanceTsPct,ShortMidRangePctAssisted,ShortMidRangePctBlocked,ThreePtShootingFoulsDrawn,ThreePtShootingFoulsDrawnPct,UnblockedArc3Accuracy,UnblockedAtRimAccuracy,OffArc3ReboundPct,OffThreePtReboundPct,OffThreePtRebounds,Offensive_Fouls,Corner3Accuracy,Corner3FGM,ThreePtOffReboundedPct,UnblockedCorner3Accuracy,DefFTReboundPct,FTDefRebounds,Technical_Free_Throw_Trips,BlockedAtRim,LostBallOutOfBoundsTurnovers,OffAtRimReboundPct,BlockedLongMidRange,Charge_Fouls_Drawn,LongMidRangePctAssisted,NonShootingPenaltyNonTakeFouls,SecondChanceTurnovers,Travels,SecondChanceAtRimFGA,SecondChanceAtRimFrequency,Clear_Path_Fouls,DefCorner3ReboundPct,HeaveAttempts,LongMidRangePctBlocked,"2pt_And_1_Free_Throw_Trips",AtRimPctAssisted,Period3Fouls4Minutes,Period4Fouls4Minutes,Charge_Fouls,Loose_Ball_Fouls_Drawn,PeriodOTFouls4Minutes,SecondChanceAtRimAccuracy,SecondChanceAtRimFGM,PenaltyCorner3FGA,PenaltyCorner3Frequency,Corner3PctAssisted,SecondChanceFtPoints,OffCorner3ReboundPct,SecondChanceArc3Accuracy,SecondChanceArc3FGM,SecondChanceFG3M,SecondChanceFg3Pct,"3pt_And_1_Free_Throw_Trips",Defensive_3_Seconds_Violations,Period4Fouls5Minutes,StepOutOfBoundsTurnovers,Period1Fouls2Minutes) VALUES',
c)
return()
y=log('2020-21','Regular+Season','101108','Player')
conn.commit()
conn.close()
cur.close()
所以我想知道我是否可以插入数据，使其与键和列名匹配。因此，如果表和dict有区别的话，它的顺序也不一样。
通过采用两个辅助库：pandas和preql，这非常简单。
您可以使用panda将json加载到单个数据帧中，然后使用preql将其导入数据库。
以下是演示如何做到这一点的可运行代码：
import requests
import pandas as pd
from preql import Preql
headers = {'user-agent': 'Chrome/88.0.4324.190'}
def log(S,ST,EI,ET):
url = 'https://api.pbpstats.com/get-game-logs/nba'
payload = {
'Season': S,
'SeasonType': ST,
'EntityId': EI,
'EntityType': ET
}
r = requests.get(url, headers=headers, params=payload).json()
if r == {'error': 'no results'} :
return
else:
return [{'Player_id':EI, **d} for d in r['multi_row_table_data']]
rows=log('2020-21','Regular+Season','101108','Player')
df = pd.DataFrame.from_dict(rows)
print("Dataframe shape:", df.shape) # (50, 218)
p = Preql() # For postgres use: p = Preql("postgres://user:pass@server")
p.import_pandas(my_table=df)
print('SQL columns:', p('count(columns(my_table))')) # 219 - includes id
print('SQL rows:', p('count(my_table)')) # 50
注意，这个代码示例目前使用Python的内置Sqlite，但您可以通过为Preql提供postgres URL来轻松地使其与postgres一起工作，如注释所示。
用pip install pandas preql-lang安装
可能不是最好的解决方案，但我写了一个小函数，它只转换为SQL语句。
def insert_into_table_query(table, data):
col_names = list(data.keys())
task = tuple(data.values())
col_str = ', '.join(str(item) for item in col_names)
col_str = '(' + col_str + ')'
value_str = ', '.join('%s' for item in task)
value_str = '(' + value_str + ');'
sql = 'INSERT INTO {tn} '.format(tn=table) + col_str + ' VALUES ' + value_str
return (sql, task)