【Python】数据库

浏览: 1325

```python

import pymysql

```

```python

conn = pymysql.connect(

    host = 'localhost',   #127.0.0.1，主机

    user = 'root',

    password = '6129w6851',

    db = 'world',

    port = 3306,

    charset = 'utf8'

)            #连接数据库

```

```python

cur = conn.cursor()  #连接数据库之后要必须要设的参数，创建游标

```

```python

cur.execute('select * from country')

```

    239

```python

data = cur.fetchall()       #把数据放入data

```

```python

for d in data:

    print(d[0],d[1],d[4])

```

    ABW Aruba 193.0

    AFG Afghanistan 652090.0

    AGO Angola 1246700.0

    AIA Anguilla 96.0

    ALB Albania 28748.0

    AND Andorra 468.0

    ANT Netherlands Antilles 800.0

    ARE United Arab Emirates 83600.0

    ARG Argentina 2780400.0

    ARM Armenia 29800.0

    ASM American Samoa 199.0

    ATA Antarctica 13120000.0

    ATF French Southern territories 7780.0

    ATG Antigua and Barbuda 442.0

    AUS Australia 7741220.0

    AUT Austria 83859.0

    AZE Azerbaijan 86600.0

    BDI Burundi 27834.0

    BEL Belgium 30518.0

    BEN Benin 112622.0

    BFA Burkina Faso 274000.0

    BGD Bangladesh 143998.0

    BGR Bulgaria 110994.0

    BHR Bahrain 694.0

    BHS Bahamas 13878.0

    BIH Bosnia and Herzegovina 51197.0

    BLR Belarus 207600.0

    BLZ Belize 22696.0

    BMU Bermuda 53.0

    BOL Bolivia 1098581.0

    BRA Brazil 8547403.0

    BRB Barbados 430.0

    BRN Brunei 5765.0

    BTN Bhutan 47000.0

    BVT Bouvet Island 59.0

    BWA Botswana 581730.0

    CAF Central African Republic 622984.0

    CAN Canada 9970610.0

    CCK Cocos (Keeling) Islands 14.0

    CHE Switzerland 41284.0

    CHL Chile 756626.0

    CHN China 9572900.0

    CIV Côte dÂ’Ivoire 322463.0

    CMR Cameroon 475442.0

    COD Congo, The Democratic Republic of the 2344858.0

    COG Congo 342000.0

    COK Cook Islands 236.0

    COL Colombia 1138914.0

    COM Comoros 1862.0

    CPV Cape Verde 4033.0

    CRI Costa Rica 51100.0

    CUB Cuba 110861.0

    CXR Christmas Island 135.0

    CYM Cayman Islands 264.0

    CYP Cyprus 9251.0

    CZE Czech Republic 78866.0

    DEU Germany 357022.0

    DJI Djibouti 23200.0

    DMA Dominica 751.0

    DNK Denmark 43094.0

    DOM Dominican Republic 48511.0

    DZA Algeria 2381741.0

    ECU Ecuador 283561.0

    EGY Egypt 1001449.0

    ERI Eritrea 117600.0

    ESH Western Sahara 266000.0

    ESP Spain 505992.0

    EST Estonia 45227.0

    ETH Ethiopia 1104300.0

    FIN Finland 338145.0

    FJI Fiji Islands 18274.0

    FLK Falkland Islands 12173.0

    FRA France 551500.0

    FRO Faroe Islands 1399.0

    FSM Micronesia, Federated States of 702.0

    GAB Gabon 267668.0

    GBR United Kingdom 242900.0

    GEO Georgia 69700.0

    GHA Ghana 238533.0

    GIB Gibraltar 6.0

    GIN Guinea 245857.0

    GLP Guadeloupe 1705.0

    GMB Gambia 11295.0

    GNB Guinea-Bissau 36125.0

    GNQ Equatorial Guinea 28051.0

    GRC Greece 131626.0

    GRD Grenada 344.0

    GRL Greenland 2166090.0

    GTM Guatemala 108889.0

    GUF French Guiana 90000.0

    GUM Guam 549.0

    GUY Guyana 214969.0

    HKG Hong Kong 1075.0

    HMD Heard Island and McDonald Islands 359.0

    HND Honduras 112088.0

    HRV Croatia 56538.0

    HTI Haiti 27750.0

    HUN Hungary 93030.0

    IDN Indonesia 1904569.0

    IND India 3287263.0

    IOT British Indian Ocean Territory 78.0

    IRL Ireland 70273.0

    IRN Iran 1648195.0

    IRQ Iraq 438317.0

    ISL Iceland 103000.0

    ISR Israel 21056.0

    ITA Italy 301316.0

    JAM Jamaica 10990.0

    JOR Jordan 88946.0

    JPN Japan 377829.0

    KAZ Kazakstan 2724900.0

    KEN Kenya 580367.0

    KGZ Kyrgyzstan 199900.0

    KHM Cambodia 181035.0

    KIR Kiribati 726.0

    KNA Saint Kitts and Nevis 261.0

    KOR South Korea 99434.0

    KWT Kuwait 17818.0

    LAO Laos 236800.0

    LBN Lebanon 10400.0

    LBR Liberia 111369.0

    LBY Libyan Arab Jamahiriya 1759540.0

    LCA Saint Lucia 622.0

    LIE Liechtenstein 160.0

    LKA Sri Lanka 65610.0

    LSO Lesotho 30355.0

    LTU Lithuania 65301.0

    LUX Luxembourg 2586.0

    LVA Latvia 64589.0

    MAC Macao 18.0

    MAR Morocco 446550.0

    MCO Monaco 1.5

    MDA Moldova 33851.0

    MDG Madagascar 587041.0

    MDV Maldives 298.0

    MEX Mexico 1958201.0

    MHL Marshall Islands 181.0

    MKD Macedonia 25713.0

    MLI Mali 1240192.0

    MLT Malta 316.0

    MMR Myanmar 676578.0

    MNG Mongolia 1566500.0

    MNP Northern Mariana Islands 464.0

    MOZ Mozambique 801590.0

    MRT Mauritania 1025520.0

    MSR Montserrat 102.0

    MTQ Martinique 1102.0

    MUS Mauritius 2040.0

    MWI Malawi 118484.0

    MYS Malaysia 329758.0

    MYT Mayotte 373.0

    NAM Namibia 824292.0

    NCL New Caledonia 18575.0

    NER Niger 1267000.0

    NFK Norfolk Island 36.0

    NGA Nigeria 923768.0

    NIC Nicaragua 130000.0

    NIU Niue 260.0

    NLD Netherlands 41526.0

    NOR Norway 323877.0

    NPL Nepal 147181.0

    NRU Nauru 21.0

    NZL New Zealand 270534.0

    OMN Oman 309500.0

    PAK Pakistan 796095.0

    PAN Panama 75517.0

    PCN Pitcairn 49.0

    PER Peru 1285216.0

    PHL Philippines 300000.0

    PLW Palau 459.0

    PNG Papua New Guinea 462840.0

    POL Poland 323250.0

    PRI Puerto Rico 8875.0

    PRK North Korea 120538.0

    PRT Portugal 91982.0

    PRY Paraguay 406752.0

    PSE Palestine 6257.0

    PYF French Polynesia 4000.0

    QAT Qatar 11000.0

    REU Réunion 2510.0

    ROM Romania 238391.0

    RUS Russian Federation 17075400.0

    RWA Rwanda 26338.0

    SAU Saudi Arabia 2149690.0

    SDN Sudan 2505813.0

    SEN Senegal 196722.0

    SGP Singapore 618.0

    SGS South Georgia and the South Sandwich Islands 3903.0

    SHN Saint Helena 314.0

    SJM Svalbard and Jan Mayen 62422.0

    SLB Solomon Islands 28896.0

    SLE Sierra Leone 71740.0

    SLV El Salvador 21041.0

    SMR San Marino 61.0

    SOM Somalia 637657.0

    SPM Saint Pierre and Miquelon 242.0

    STP Sao Tome and Principe 964.0

    SUR Suriname 163265.0

    SVK Slovakia 49012.0

    SVN Slovenia 20256.0

    SWE Sweden 449964.0

    SWZ Swaziland 17364.0

    SYC Seychelles 455.0

    SYR Syria 185180.0

    TCA Turks and Caicos Islands 430.0

    TCD Chad 1284000.0

    TGO Togo 56785.0

    THA Thailand 513115.0

    TJK Tajikistan 143100.0

    TKL Tokelau 12.0

    TKM Turkmenistan 488100.0

    TMP East Timor 14874.0

    TON Tonga 650.0

    TTO Trinidad and Tobago 5130.0

    TUN Tunisia 163610.0

    TUR Turkey 774815.0

    TUV Tuvalu 26.0

    TWN Taiwan 36188.0

    TZA Tanzania 883749.0

    UGA Uganda 241038.0

    UKR Ukraine 603700.0

    UMI United States Minor Outlying Islands 16.0

    URY Uruguay 175016.0

    USA United States 9363520.0

    UZB Uzbekistan 447400.0

    VAT Holy See (Vatican City State) 0.4

    VCT Saint Vincent and the Grenadines 388.0

    VEN Venezuela 912050.0

    VGB Virgin Islands, British 151.0

    VIR Virgin Islands, U.S. 347.0

    VNM Vietnam 331689.0

    VUT Vanuatu 12189.0

    WLF Wallis and Futuna 200.0

    WSM Samoa 2831.0

    YEM Yemen 527968.0

    YUG Yugoslavia 102173.0

    ZAF South Africa 1221037.0

    ZMB Zambia 752618.0

    ZWE Zimbabwe 390757.0

    

```python

cur.execute("select * from country where Code = 'AIA' or Code = 'DOM' ") #在筛选的时候就加入限定条件

```

    2

```python

data_2 = cur.fetchall()

```

```python

for d in data_2:

    print(d[0],d[1],d[4])

```

    AIA Anguilla 96.0

    DOM Dominican Republic 48511.0

    

```python

conn.commit()   #将做出的修改上传保存

```

```python

cur.close()

conn.close()  #把数据库关闭，避免出错

```

```python

import pandas as pd

from sqlalchemy import create_engine   #OIM框架，帮助我们读写

```

```python

sql = "select * from country where population > 100000"

engine = create_engine('mysql+pymysql://root:6129w6851@localhost:3306/world?charset=utf8')

df = pd.read_sql(sql,engine)

```

    d:\ProgramData\Anaconda3\lib\site-packages\pymysql\cursors.py:166: Warning: (1366, "Incorrect string value: '\\xD6\\xD0\\xB9\\xFA\\xB1\\xEA...' for column 'VARIABLE_VALUE' at row 496")

      result = self._query(query)

    d:\ProgramData\Anaconda3\lib\site-packages\pymysql\cursors.py:166: Warning: (1287, "'@@tx_isolation' is deprecated and will be removed in a future release. Please use '@@transaction_isolation' instead")

      result = self._query(query)

    

```python

df

```

  

    

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

    

  

  

    

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

    

    

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

    

    

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

    

    

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

    

    

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

    

    

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

    

    

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

    

    

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

    

    

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

    

    

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

    

    

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

    

    

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

    

    

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

    

    

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

    

    

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

    

    

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

    

    

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

    

    

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

    

    

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

    

    

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

    

    

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

    

    

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

    

    

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

    

    

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

    

    

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

    

    

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

    

    

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

    

    

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

    

    

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

    

    

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

    

    

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

    

    

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

    

    

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

    

    

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

    

    

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

    

    

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

    

    

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

    

    

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

    

    

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

    

    

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

    

    

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

    

    

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

    

    

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

    

    

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

    

    

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

    

    

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

    

    

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

    

    

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

    

    

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

    

    

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

    

    

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

    

    

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

    

    

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

    

    

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

    

    

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

    

    

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

    

    

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

    

    

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

    

    

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

    

    

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

    

    

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

      

    

  

CodeNameContinentRegionSurfaceAreaIndepYearPopulationLifeExpectancyGNPGNPOldLocalNameGovernmentFormHeadOfStateCapitalCode2
0ABWArubaNorth AmericaCaribbean193.0NaN10300078.4828.0793.0ArubaNonmetropolitan Territory of The NetherlandsBeatrix129AW
1AFGAfghanistanAsiaSouthern and Central Asia652090.01919.02272000045.95976.0NaNAfganistan/AfqanestanIslamic EmirateMohammad Omar1AF
2AGOAngolaAfricaCentral Africa1246700.01975.01287800038.36648.07984.0AngolaRepublicJosé Eduardo dos Santos56AO
3ALBAlbaniaEuropeSouthern Europe28748.01912.0340120071.63205.02500.0ShqipëriaRepublicRexhep Mejdani34AL
4ANTNetherlands AntillesNorth AmericaCaribbean800.0NaN21700074.71941.0NaNNederlandse AntillenNonmetropolitan Territory of The NetherlandsBeatrix33AN
5AREUnited Arab EmiratesAsiaMiddle East83600.01971.0244100074.137966.036846.0Al-Imarat al-´Arabiya al-MuttahidaEmirate FederationZayid bin Sultan al-Nahayan65AE
6ARGArgentinaSouth AmericaSouth America2780400.01816.03703200075.1340238.0323310.0ArgentinaFederal RepublicFernando de la Rúa69AR
7ARMArmeniaAsiaMiddle East29800.01991.0352000066.41813.01627.0HajastanRepublicRobert Kotšarjan126AM
8AUSAustraliaOceaniaAustralia and New Zealand7741220.01901.01888600079.8351182.0392911.0AustraliaConstitutional Monarchy, FederationElisabeth II135AU
9AUTAustriaEuropeWestern Europe83859.01918.0809180077.7211860.0206025.0ÖsterreichFederal RepublicThomas Klestil1523AT
10AZEAzerbaijanAsiaMiddle East86600.01991.0773400062.94127.04100.0AzärbaycanFederal RepublicHeydär Äliyev144AZ
11BDIBurundiAfricaEastern Africa27834.01962.0669500046.2903.0982.0Burundi/UburundiRepublicPierre Buyoya552BI
12BELBelgiumEuropeWestern Europe30518.01830.01023900077.8249704.0243948.0België/BelgiqueConstitutional Monarchy, FederationAlbert II179BE
13BENBeninAfricaWestern Africa112622.01960.0609700050.22357.02141.0BéninRepublicMathieu Kérékou187BJ
14BFABurkina FasoAfricaWestern Africa274000.01960.01193700046.72425.02201.0Burkina FasoRepublicBlaise Compaoré549BF
15BGDBangladeshAsiaSouthern and Central Asia143998.01971.012915500060.232852.031966.0BangladeshRepublicShahabuddin Ahmad150BD
16BGRBulgariaEuropeEastern Europe110994.01908.0819090070.912178.010169.0BalgarijaRepublicPetar Stojanov539BG
17BHRBahrainAsiaMiddle East694.01971.061700073.06366.06097.0Al-BahraynMonarchy (Emirate)Hamad ibn Isa al-Khalifa149BH
18BHSBahamasNorth AmericaCaribbean13878.01973.030700071.13527.03347.0The BahamasConstitutional MonarchyElisabeth II148BS
19BIHBosnia and HerzegovinaEuropeSouthern Europe51197.01992.0397200071.52841.0NaNBosna i HercegovinaFederal RepublicAnte Jelavic201BA
20BLRBelarusEuropeEastern Europe207600.01991.01023600068.013714.0NaNBelarusRepublicAljaksandr Lukašenka3520BY
21BLZBelizeNorth AmericaCentral America22696.01981.024100070.9630.0616.0BelizeConstitutional MonarchyElisabeth II185BZ
22BOLBoliviaSouth AmericaSouth America1098581.01825.0832900063.78571.07967.0BoliviaRepublicHugo Bánzer Suárez194BO
23BRABrazilSouth AmericaSouth America8547403.01822.017011500062.9776739.0804108.0BrasilFederal RepublicFernando Henrique Cardoso211BR
24BRBBarbadosNorth AmericaCaribbean430.01966.027000073.02223.02186.0BarbadosConstitutional MonarchyElisabeth II174BB
25BRNBruneiAsiaSoutheast Asia5765.01984.032800073.611705.012460.0Brunei DarussalamMonarchy (Sultanate)Haji Hassan al-Bolkiah538BN
26BTNBhutanAsiaSouthern and Central Asia47000.01910.0212400052.4372.0383.0Druk-YulMonarchyJigme Singye Wangchuk192BT
27BWABotswanaAfricaSouthern Africa581730.01966.0162200039.34834.04935.0BotswanaRepublicFestus G. Mogae204BW
28CAFCentral African RepublicAfricaCentral Africa622984.01960.0361500044.01054.0993.0Centrafrique/Bê-AfrîkaRepublicAnge-Félix Patassé1889CF
29CANCanadaNorth AmericaNorth America9970610.01867.03114700079.4598862.0625626.0CanadaConstitutional Monarchy, FederationElisabeth II1822CA
................................................
162SVNSloveniaEuropeSouthern Europe20256.01991.0198780074.919756.018202.0SlovenijaRepublicMilan Kucan3212SI
163SWESwedenEuropeNordic Countries449964.0836.0886140079.6226492.0227757.0SverigeConstitutional MonarchyCarl XVI Gustaf3048SE
164SWZSwazilandAfricaSouthern Africa17364.01968.0100800040.41206.01312.0kaNgwaneMonarchyMswati III3244SZ
165SYRSyriaAsiaMiddle East185180.01941.01612500068.565984.064926.0SuriyaRepublicBashar al-Assad3250SY
166TCDChadAfricaCentral Africa1284000.01960.0765100050.51208.01102.0Tchad/TshadRepublicIdriss Déby3337TD
167TGOTogoAfricaWestern Africa56785.01960.0462900054.71449.01400.0TogoRepublicGnassingbé Eyadéma3332TG
168THAThailandAsiaSoutheast Asia513115.01350.06139900068.6116416.0153907.0Prathet ThaiConstitutional MonarchyBhumibol Adulyadej3320TH
169TJKTajikistanAsiaSouthern and Central Asia143100.01991.0618800064.11990.01056.0ToçikistonRepublicEmomali Rahmonov3261TJ
170TKMTurkmenistanAsiaSouthern and Central Asia488100.01991.0445900060.94397.02000.0TürkmenostanRepublicSaparmurad Nijazov3419TM
171TMPEast TimorAsiaSoutheast Asia14874.0NaN88500046.00.0NaNTimor TimurAdministrated by the UNJosé Alexandre Gusmão1522TP
172TTOTrinidad and TobagoNorth AmericaCaribbean5130.01962.0129500068.06232.05867.0Trinidad and TobagoRepublicArthur N. R. Robinson3336TT
173TUNTunisiaAfricaNorthern Africa163610.01956.0958600073.720026.018898.0Tunis/TunisieRepublicZine al-Abidine Ben Ali3349TN
174TURTurkeyAsiaMiddle East774815.01923.06659100071.0210721.0189122.0TürkiyeRepublicAhmet Necdet Sezer3358TR
175TWNTaiwanAsiaEastern Asia36188.01945.02225600076.4256254.0263451.0TÂ’ai-wanRepublicChen Shui-bian3263TW
176TZATanzaniaAfricaEastern Africa883749.01961.03351700052.38005.07388.0TanzaniaRepublicBenjamin William Mkapa3306TZ
177UGAUgandaAfricaEastern Africa241038.01962.02177800042.96313.06887.0UgandaRepublicYoweri Museveni3425UG
178UKRUkraineEuropeEastern Europe603700.01991.05045600066.042168.049677.0UkrajinaRepublicLeonid Kutšma3426UA
179URYUruguaySouth AmericaSouth America175016.01828.0333700075.220831.019967.0UruguayRepublicJorge Batlle Ibáñez3492UY
180USAUnited StatesNorth AmericaNorth America9363520.01776.027835700077.18510700.08110900.0United StatesFederal RepublicGeorge W. Bush3813US
181UZBUzbekistanAsiaSouthern and Central Asia447400.01991.02431800063.714194.021300.0UzbekistonRepublicIslam Karimov3503UZ
182VCTSaint Vincent and the GrenadinesNorth AmericaCaribbean388.01979.011400072.3285.0NaNSaint Vincent and the GrenadinesConstitutional MonarchyElisabeth II3066VC
183VENVenezuelaSouth AmericaSouth America912050.01811.02417000073.195023.088434.0VenezuelaFederal RepublicHugo Chávez Frías3539VE
184VNMVietnamAsiaSoutheast Asia331689.01945.07983200069.321929.022834.0Viêt NamSocialistic RepublicTrân Duc Luong3770VN
185VUTVanuatuOceaniaMelanesia12189.01980.019000060.6261.0246.0VanuatuRepublicJohn Bani3537VU
186WSMSamoaOceaniaPolynesia2831.01962.018000069.2141.0157.0SamoaParlementary MonarchyMalietoa Tanumafili II3169WS
187YEMYemenAsiaMiddle East527968.01918.01811200059.86041.05729.0Al-YamanRepublicAli Abdallah Salih1780YE
188YUGYugoslaviaEuropeSouthern Europe102173.01918.01064000072.417000.0NaNJugoslavijaFederal RepublicVojislav Koštunica1792YU
189ZAFSouth AfricaAfricaSouthern Africa1221037.01910.04037700051.1116729.0129092.0South AfricaRepublicThabo Mbeki716ZA
190ZMBZambiaAfricaEastern Africa752618.01964.0916900037.23377.03922.0ZambiaRepublicFrederick Chiluba3162ZM
191ZWEZimbabweAfricaEastern Africa390757.01980.01166900037.85951.08670.0ZimbabweRepublicRobert G. Mugabe4068ZW

192 rows × 15 columns

parse_dates 把某个字段转成时间类型 ，data_parser 具体的时间类型（pd.read，在读取时操作）

columns = ['列1','列2','列3','列4']

df = pd.read_table('文件.txt',names = columns,sep='\s+',parse_dates=,date_parser=)

```python

df['month']=20171209

```

```python

df.info()   #month是int格式，是数字

```

    

    RangeIndex: 192 entries, 0 to 191

    Data columns (total 16 columns):

    Code              192 non-null object

    Name              192 non-null object

    Continent         192 non-null object

    Region            192 non-null object

    SurfaceArea       192 non-null float64

    IndepYear         176 non-null float64

    Population        192 non-null int64

    LifeExpectancy    192 non-null float64

    GNP               192 non-null float64

    GNPOld            170 non-null float64

    LocalName         192 non-null object

    GovernmentForm    192 non-null object

    HeadOfState       192 non-null object

    Capital           192 non-null int64

    Code2             192 non-null object

    month             192 non-null int64

    dtypes: float64(5), int64(3), object(8)

    memory usage: 24.1+ KB

    

```python

df['month'] = pd.to_datetime(df.month,format="%Y%m%d")   #把数据类型解析转成时间,要转换的数据+format方法

df['date'] = df.month.values.astype('datetime64[M]')  #先加个values，变成数组形式，然后再用数组的功能转换数据。

```

```python

df.info()   #month变为时间格式数据，精度ns纳秒

```

    

    RangeIndex: 192 entries, 0 to 191

    Data columns (total 17 columns):

    Code              192 non-null object

    Name              192 non-null object

    Continent         192 non-null object

    Region            192 non-null object

    SurfaceArea       192 non-null float64

    IndepYear         176 non-null float64

    Population        192 non-null int64

    LifeExpectancy    192 non-null float64

    GNP               192 non-null float64

    GNPOld            170 non-null float64

    LocalName         192 non-null object

    GovernmentForm    192 non-null object

    HeadOfState       192 non-null object

    Capital           192 non-null int64

    Code2             192 non-null object

    month             192 non-null datetime64[ns]

    date              192 non-null datetime64[ns]

    dtypes: datetime64[ns](2), float64(5), int64(2), object(8)

    memory usage: 25.6+ KB

    

```python

result = df.groupby(['GovernmentForm']).count()['Code'].reset_index()  #数据透视表

```

```python

result

```

  

    

      

      

      

    

  

  

    

      

      

      

    

    

      

      

      

    

    

      

      

      

    

    

      

      

      

    

    

      

      

      

    

    

      

      

      

    

    

      

      

      

    

    

      

      

      

    

    

      

      

      

    

    

      

      

      

    

    

      

      

      

    

    

      

      

      

    

    

      

      

      

    

    

      

      

      

    

    

      

      

      

    

    

      

      

      

    

    

      

      

      

    

    

      

      

      

    

    

      

      

      

    

    

      

      

      

    

    

      

      

      

    

    

      

      

      

    

    

      

      

      

    

    

      

      

      

    

    

      

      

      

    

    

      

      

      

    

  

GovernmentFormCode
0Administrated by the UN1
1Autonomous Area1
2Commonwealth of the US1
3Constitutional Monarchy23
4Constitutional Monarchy (Emirate)1
5Constitutional Monarchy, Federation4
6Emirate Federation1
7Federal Republic15
8Federation1
9Islamic Emirate1
10Islamic Republic2
11Monarchy4
12Monarchy (Emirate)1
13Monarchy (Sultanate)2
14Nonmetropolitan Territory of France2
15Nonmetropolitan Territory of The Netherlands2
16Occupied by Marocco1
17Overseas Department of France4
18Parlementary Monarchy1
19People'sRepublic1
20Republic115
21Socialistic Republic3
22Socialistic State1
23Special Administrative Region of China2
24Territorial Collectivity of France1
25US Territory1

```python

result.to_sql(name = 'newtable',con= 'mysql+pymysql://root:6129w6851@localhost:3306/world?charset=utf8',

             if_exists = 'append', index = False)   #不去数据库，直接创建表（表会比较占内存，字段可能有问题）

```

    d:\ProgramData\Anaconda3\lib\site-packages\pymysql\cursors.py:166: Warning: (1366, "Incorrect string value: '\\xD6\\xD0\\xB9\\xFA\\xB1\\xEA...' for column 'VARIABLE_VALUE' at row 496")

      result = self._query(query)

    d:\ProgramData\Anaconda3\lib\site-packages\pymysql\cursors.py:166: Warning: (1287, "'@@tx_isolation' is deprecated and will be removed in a future release. Please use '@@transaction_isolation' instead")

      result = self._query(query)

    

```python

result.to_sql(name = 'newtable2',con= 'mysql+pymysql://root:6129w6851@localhost:3306/world?charset=utf8',

             if_exists = 'append', index = False)   #在数据库建好表的属性，再插入数据（建议方式）

```

    d:\ProgramData\Anaconda3\lib\site-packages\pymysql\cursors.py:166: Warning: (1366, "Incorrect string value: '\\xD6\\xD0\\xB9\\xFA\\xB1\\xEA...' for column 'VARIABLE_VALUE' at row 496")

      result = self._query(query)

    d:\ProgramData\Anaconda3\lib\site-packages\pymysql\cursors.py:166: Warning: (1287, "'@@tx_isolation' is deprecated and will be removed in a future release. Please use '@@transaction_isolation' instead")

      result = self._query(query)

    

** 创建表的时候字段有三个，但写入时，只写入两个。会出现什么情况？**

少写会空值，多写会报错。

推荐 0
本文由 呃呃呃呃的猫 创作，采用 知识共享署名-相同方式共享 3.0 中国大陆许可协议 进行许可。
转载、引用前需联系作者，并署名作者且注明文章出处。
本站文章版权归原作者及原出处所有 。内容为作者个人观点， 并不代表本站赞同其观点和对其真实性负责。本站是一个个人学习交流的平台，并不用于任何商业目的，如果有任何问题，请及时联系我们，我们将根据著作权人的要求，立即更正或者删除有关内容。本站拥有对此声明的最终解释权。

0 个评论

要回复文章请先登录或注册